Monday, April 10, 2017

Where'd all this #SQLServer Free Memory come from??!?

My blog post on memory accounting with perfmon has been pretty popular :-) You can look there to see validation of the idea that "database cache + free + stolen = total server memory" for SQL Server.

SQL Server Memory Accounting with Perfmon

Today I was looking at perfmon from a system and I saw a huge increase in "SQLServer:Memory Manager\Free Memory (KB)" appear in the middle of a key workload.  How'd that happen?

Unfortunately, the perfmon data I was reviewing was missing one of the counters I collect routinely now: "\SQLServer:Databases(_Total)\DBCC Logical Scan Bytes/sec"

So... right now I'm not sure where all the free memory came from on that system.  But I've got a hunch, so I asked to get that counter included in the next perfmon collection I get from them.

Below is a test I ran with a full checkdb on my 3.35TB database.  The "(logical scan bytes +1)/sec Logarithmic" measure that is graphed really is "\SQLServer:Databases(_Total)\DBCC Logical Scan Bytes/sec" + 1, with an Excel logarithmic scale :-)  Its required to be positive nonzero to preclude errors.  And I just wanted it there as a clear indicator that checkdb was done.  So I started the SQL Server service for the instance and started checkdb.  An internal snapshot was created, and "Total server memory" kept growing to reach "max server memory".  All of the database cache was for the internal snapshot (other than checkdb only some monitors were running in the instance).

When checkdb was complete - blammo!!  The internal snapshot db was dropped, and all of the database cache associated with the dropped db was invalidated - resulting in an humongous increase in free memory within SQL Server "total server memory".

Maybe that's what happened in that production system.  I should be able to rule that in/out watching free memory for a similar increase, and checking "\SQLServer:Databases(_Total)\DBCC Logical Scan Bytes/sec".

No comments:

Post a Comment