Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a dedicated SQL Server 2008 R2 machine that is experiencing some strange memory issues.. The machine itself has plenty of resources including two quad-core processors, 16gb of RAM and 64bit Windows Server 2008 R2 Enterprise (it is a Dell PowerEdge 2950).

The strange problem is that the system is reporting 82% of memory in use but sqlservr.exe is only reporting 155mb in use. The reason that I suspect SQL Server is the issue is because if I restart the sqlservr.exe process the memory consumption returns to normal for a period of time.

Does anyone have any ideas on how I can start to track this issue down?

Thanks, Jason

share|improve this question
1  
Are you using the Lock Pages in Memory user right? If so, the locked memory won't be reported by task manager. See blogs.technet.com/b/askperf/archive/2008/03/25/… for more info. –  Mark S. Rasmussen Oct 21 '11 at 13:37
    
We have the Lock Pages in Memory user right set to "None". We also have the "Maximum server memory (in MB)" setting on it's default int.MaxValue--do you think that could cause an issue? –  typefragger Oct 21 '11 at 13:54
4  
The only time I'd be concerned is when my sql server is using LESS than 82%! –  SqlACID Oct 21 '11 at 14:57
add comment

3 Answers

up vote 14 down vote accepted

You won't get a true picture of memory usage from Task Manager if the account the service is running under has the lock pages in memory privilege (edit: as per Mark Rasmussen's comment/link). To determine how much memory is being used you can look at:

  • SQLServer:Memory Manager\Total Server Memory perfmon counter
  • DMVs

I can't recall if there is a DMV or combination of that will give you the total memory allocation but the following will show the bulk of it.

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second is the most interesting usually, buffer pool allocations by database. This is where the lions share will be used and it can be useful to understand which of your databases are the biggest consumers.

share|improve this answer
    
Wow thank you so much! This (particularly the second one) made it completely clear for me! –  typefragger Oct 21 '11 at 15:38
add comment

There's a recent article from our own Brent Ozar that treats this case, when Task Manager doesn't show correctly the memory eaten by SQLServer and its additional services. You can find it here: A Sysadmin’s Guide to Microsoft SQL Server Memory.

Quote: "Why Isn’t SQLServer.exe Using Much Memory?

When you remote desktop into a server and look at Task Manager, sqlservr.exe’s Mem Usage always seems wacky. That’s not SQL Server’s fault. Task Manager is a dirty, filthy liar. (I know, it sounds like the SQL guy is shifting the blame, but bear with me for a second.) On 64-bit boxes, this number is somewhat more accurate, but on 32-bit boxes, it’s just completely off-base. To truly get an accurate picture of how much memory SQL Server is using, you need a tool like Process Explorer, and you need to identify all of SQL Server’s processes. In the server I’m showing at right, there’s two SQL Server instances (shown by sqlservr.exe), plus SQL Agent, SQL Browser, and SQL Server backup tools. It’s not unusual to also see SQL Server Analysis Services, Integration Services, and Reporting Services also running on the same server – all of which consume memory.

So how much memory is SQL using? I’ll make this easy for you. SQL Server is using all of the memory. Period."

So I'd advise you to try Mark's query and use a better tool for memory report. Or just trust Perfmon to report memory, not Task Manager.

share|improve this answer
add comment

The amount of memory used by SQL, as shown in the task manager, will mostly be the max-memory setting. This is how the min/max setting works:

When SQL server starts up, it starts taking memory up to the min-memory setting. As your SQL needs increase, SQL will start using more memory up to the max-memory setting. The memory then stays at this (max) point even when the SQL usage goes down. This gives the impression of SQL performing huge tasks and using up that much memory. In reality, this memory is reserved by SQL.

When there is non-SQL memory pressure on the server, SQL will release memory down to the min-memory setting point. This is how the memory settings are used. You can use Mark's scripts to see how SQL is using this memory.

share|improve this answer
1  
Min/max governs buffer pool allocations, nothing more. This is the first line in the description of Server Memory Options in BOL. The settings have absolutely no relation to to that displayed in task manager. Brent's description of taskmgr as "a dirty, filthy liar" sums up the situation as well as any I've read. –  Mark Storey-Smith Oct 21 '11 at 23:22
    
@MarkStorey-Smith please read the content further down in the link in your own comment, it just further explains my point. The task manager shows the usage of system resources. Buffer pool is not a system resource. I am explaining on what the memory usage by SQL shown in the task manager means. Your are stating the obvious by mentioning the buffer pool, but that still does not prove me wrong. –  StanleyJohns Oct 22 '11 at 12:59
    
Not sure how better to put this... "The amount of memory used by SQL, as shown in the task manager, will mostly be the max-memory setting. This is how the min/max setting work".. no it isn't. –  Mark Storey-Smith Oct 22 '11 at 13:04
    
'This is how the min/max settings work:' There is a colon at the end meaning the explanation follows, not that it asserts the previous sentence. :) –  StanleyJohns Oct 22 '11 at 13:10
1  
I'm with Mark. Read Slava Oks blog: he was part of the MS team that wrote the memory manager. Go to the heading "buffer pool". I quote "Remember SQL Server has two memory settings that you can control using sp_conifigure. They are max and min server memory. I am not sure if you know but these two setting really control the size of the buffer pool. They do not control overall amount of physical memory consumed by SQL Server" –  gbn Oct 22 '11 at 13:41
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.