I have SQL server 2008 installed in the production site and the max memory allocated for it is 28 GB of the total 32 GB ram. But sometimes, I see more than 29GB memory being used by the sql server which is not suppose to. Is there any corrective action I should be taking or do some clean up?

migrated from stackoverflow.com Jul 26 '11 at 0:03

This question came from our site for professional and enthusiast programmers.


The max server memory setting only controls the size of the buffer pool. SQL Server may and will allocate more memory than the 28GB setting, for its own use of consumers unrelated to the buffer pool. Components (DLLs) loaded inside the process will also consume memory. How much more depends on the usage pattern, it can be few MBs more or it can be GBs more (eg. throw in a few XPs or sp_OA calls and the memory will go all haywire).

Your corrective actions should be to investigate the extra memory consumption. sys.dm_os_memory_clerks is your friend here, see also How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server. Certain memory consumers cannot be investigated from SQL Server DMVs and counters (eg. memory allocated by loaded DLLs, like COM components).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy