October 30, 2013 at 5:02 am
Hi - I am currently monitoring the perf counter SQL Server Total Memory which as I understand it is the memory currently in use by SQL server. This is currently hovering around the 40GB mark in one of our SQL servers however the process in windows only shows a committed value of 2GB
I would have thought these 2 would be the same? What am I missing?
Thanks
October 30, 2013 at 6:25 am
Total Memory is the amount that SQL Server has allocated. Your average server will allocate as much memory as it can and then keep it. So this counter will generally be flat on most systems. It makes it less than useful. Only on a system that has other services that will take memory away from SQL Server, and if SQL Server is set to allow for fluctuation of the memory (the default) will you see any changes. It's generally considered a good practice to set a memory allocation for the server and leave it there (allowing for memory for the OS and any other services in addition to SQL Server on the system).
The other memory measurements are memory within the allocated memory. They'll only add up to the total of memory allocated if you don't have enough memory on the system (and even then, there's going to be some wiggle room). I wouldn't try to correlate the two at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 30, 2013 at 7:37 am
Hi Grant - Thanks for the reply.
We generally set the maximum memory and also lock pages in memory (I assume this stops the fluctuations you talked about).
If I understand you correctly then the total of the other memory measurements which include the windows process in task manager are the amount of memory actually being used by SQL at any one time.
If this is the case then what other memory measurements are you talking about? I have read about DBCC MEMORYSTATUS but I find it difficult to interpret.
Many thanks
October 30, 2013 at 7:46 am
I meant internal memory measurements within SQL Server such as ConnectionMemory, DatabaseCacheMemory or FreeMemory. In theory you should be able to look at all the these and see what the total use is, but it doesn't quite work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 30, 2013 at 7:58 am
Task manager is limited, it doesn't show memory allocated with the AWE API (which is used when you have locked pages enabled)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply