5

If I execute the following Powershell command:

Invoke-Sqlcmd `
  -ServerInstance '(local)' -Database 'TestDatabase' `
  -Query "select top 1000000 * from dbo.SAMPLE_CUSTOMER" | Out-Null

I see my memory usage go through the roof. It uses 1GB of memory.

If I start the command again, memory increases to 1.8GB of memory, then it gets reduced to 800MB (garbage collect?) and starts growing again.

I tried to reduce memory footprint for the PowerShell shell and plugins to 100MB by following the steps in the article http://blogs.technet.com/b/heyscriptingguy/archive/2013/07/30/learn-how-to-configure-powershell-memory.aspx, but I still see memory increase far above the configured 100MB.

I have some questions:

  1. Why does PowerShell not respect the memory limitations given by the setting MaxMemoryPerShellMB?
  2. Why does Invoke-Sqlcmd eat memory and doesn't it "forget" the records processed in the pipeline
  3. Why does the PowerShell process not reclaim memory automatically when finished processing?
  4. How can I process many SQL records without a large memory footprint?
Improve this question
5
  • 3
    If you're actually fetching 1 million rows, you're going to be unable to use Invoke-SqlCmd without severely pressuring memory. That cmdlet will instantiate every record in memory before it passes it to Out-Null. It has to put that data somewhere, so it will use memory to do it. Objects won't disappear until garbage collection gets them, so you're going to see a lot of memory usage. If you really need to do this, then I suggest you use System.Data.SqlClient.SqlDataReader instead. Commented Nov 30, 2015 at 16:56
  • 1
    Here is an example I posted in a different question for using SqlDataReader in PowerShell. SqlDataReader is designed for processing large quantities of data without using lots of memory. In exchange for that, it has the connection open longer and never has access to more than one record at a time. Commented Nov 30, 2015 at 17:04
  • 1
    As @BaconBits already explained, Invoke-SqlCmd just works this way (it packs all retrieved rows into a single DataSet object, which is why they don't get "discarded on the fly"). Regarding MaxMemoryPerShellMB: The behavior is unsupported if the MaxMemoryPerShellMB is set to a value that is less than the default. Commented Nov 30, 2015 at 17:45
  • @BaconBits: thx for the example code, will implement that! Commented Dec 1, 2015 at 0:11
  • @BaconBits Got it working, works like a breeze! No extra memory usage. Commented Dec 1, 2015 at 21:56
Related questions
2
Powershell Memory Usage
1
Massive memory leak in Powershell script
5
Powershell memory usage - expensive?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.