Issue 1: max server memory <> max server memory
Okay so you have run sp_configure and checked SQL and it has stolen more memory than you told it to. Aghh!
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'max server memory', 4096; GO RECONFIGURE; GO
Why do you fight me SQL Server? Use a m-a-x-i-m-u-m of 4GB!
But wait… The max server memory setting only sets the buffer pool, its not the max server memory at all…lies. So we need to take in to account that the buffer will be at most “4GB” and then added on to that the memory allocated to our other components. So if we want it to use a maximum of 4GB we should set it below this. (The actual value will vary depending on your exact setup)
Note that the max server memory option only limits the size of the SQL Server buffer pool. The max server memory option does not limit a remaining unreserved memory area that SQL Server leaves for allocations of other components such as extended stored procedures, COM objects, non-shared DLLs, EXEs, and MAPI components. Because of the preceding allocations, it is normal for the SQL Server private bytes to exceed the max server memory configuration.
Issue 2: Task Manager Lies 64 bit
SQL Server is using x amount of memory but the maths does not seem to add up when I do the sum in my head. Something must be using more memory! It is you SQL I know it is!
OMG memory leak! Well no task manager is a big liar in fact, because the memory has been allocated via the AWE api and it doesn’t show this. The AWE api has been used because the Lock Pages in memory privilege has been granted. So the memory is not reported in task manager… lies upon lies…
Lock pages in memory
So what is the easiest way to check how much memory SQL Server is using without running any pesky SQL scripts? Easy. The best applications available to quickly see memory is VMMap. Available here: http://technet.microsoft.com/en-us/sysinternals/dd535533 I often wonder why this tool can not be incorporated into Windows now that Microsoft own it.