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?
Serge van den Oever
  • 4,340
  • 8
  • 45
  • 66
  • 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. – Bacon Bits Nov 30 '15 at 16:56
  • 1
    [Here](http://stackoverflow.com/questions/33572538/powershell-sqldatareader-foreach-how-do-i-execute-a-select-query-against-a-sql/33573606#33573606) 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. – Bacon Bits Nov 30 '15 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.*](https://msdn.microsoft.com/en-us/library/windows/desktop/ee309367(v=vs.85).aspx) – Mathias R. Jessen Nov 30 '15 at 17:45
  • @BaconBits: thx for the example code, will implement that! – Serge van den Oever Dec 01 '15 at 00:11
  • @BaconBits Got it working, works like a breeze! No extra memory usage. – Serge van den Oever Dec 01 '15 at 21:56

0 Answers0