title: MSSQL Batch INSERT operations increase memory usage
body: <p>I have the following batch insert query running on MSSQL 2012:</p>
  
  <pre><code>WHILE 1&lt;2 --busy loop until user stops the query
  BEGIN
  
  DECLARE @batch int = 200000
  
  BEGIN TRANSACTION
  
  WHILE @batch &gt; 0
  BEGIN
  
  DECLARE @hourRand int = CONVERT(int,60*RAND() )
  DECLARE @minRand int = CONVERT(int,60*RAND() )
  --...more DECLAREs... --
  
  INSERT INTO dbo.details (COLUMN1,COLUMN2) VALUES (@hourRand, @minRand, ...)
  
  set @batch = @batch - 1
  END
  
  COMMIT
  
  END
  </code></pre>
  
  <p>When I leave this query on, SQL's memory usage continuously grows. Why would this loop cause memory growth? Are the inserted entries being stored in some kind of cache or buffer that's taking up memory? If so, how can I free the memory that's being used?</p>
  
  <p>I'm aware that SQL grows its memory pool as needed, but my queries begin to hang when the server's memory usage approaches 98%, so I do not think it's simply the memory pool being large. SQL appears to be actually "using" most of the memory it's holding onto.</p>
  
  <p>Restarting the server frees the memory as expected, but I can't have the server run out of memory often.</p>
  
  <p>Thank you for the help!</p>