2

I hope this has a really simple solution, but I have a feeling it won't. I've not been able to find any information on this anywhere else, which isn't a good start!

Basically we're running SQL Server 2008 on a Windows Server 2008 R2 machine with 16GB RAM.

The problem is that our RAM usage keeps creeping up to 15.6GB and causing us problems, and I was wondering if it could be SQL's cache.

We've now configured SQL to use a min/max of 13GB, but I'm still worried that it might be something else eating the RAM, so before I reset the server and flush out whatever is using it, I thought I'd try and see if it was SQL behind it.

How can I see the size of SQL's cache RAM usage? Is it just the figure in the task manager?

Thanks for any help.

Dave Cheney
  • 18,307
  • 7
  • 48
  • 56
Django Reinhardt
  • 2,256
  • 3
  • 38
  • 55

2 Answers2

4

Perfmon.exe

First look at the Process object type. This has an instance for every single process on the system, and contains metrics like Virtual Bytes, Virtual Bytes Peak, Working Set and Working Set Peak. The SQL Server's instance will be named after the process name, 'sqlservr'. Looking at all instances you can quickly see which process causes the most memory consumption.

Next look at SQL Server's own counters. In the SQL Server:Buffer Manager object you'll find SQL Server own counters. You need to look at the Total Pages counter that counts all memory tracked by SQL Server internally. The counter is in pages, so you need to multiply by 8192 to get bytes.

There may be big a discrepancy between the Process Virtual Bytes counter and SQL own's Total Pages. This can happen when SQL uses AWE to map memory, and SQL may use AWE on x64 platforms too.

You can also track SQL Server moemory consumption from isnide, look at the sys.dm_os_memory_clerks or run DBCC MEMORYSTATUS.

If you find that SQL Server uses the memory: close your session, lay down your hands from the keyboard, and walk away. This is the normal, intended and desired behavior. If you need memory for any other process, move that process away from the same host as SQL. Never run anything else on the same host you run SQL Server (no IIS, no ASP, no exhange, no DC, no DNS/Winds, nothing).

Community
  • 1
Remus Rusanu
  • 8,253
  • 1
  • 19
  • 22
  • Thanks for your help. We have one instance of sqlserver. Total Pages sits constantly at 1,664,000 = 13GB. The amount I just set it to. We also ran DBCC MEMORYSTATUS. It seems like SQL Server is NOT the culprit. Thanks again for your help! – Django Reinhardt Feb 23 '10 at 20:48
  • 1
    Consider though that the Total Pages only track memory allocated by SQL Server buffer pool. Inside the SQL process there are *other* memory allocations too. Some are visible in DBCC MEMORYSTATUS, but some aren't (specially memory allocated by loaded libraries, like in proc COM servers or some xp_ extended procs), so you can't know for sure. The Process perf counter *will* track those, so better double check. – Remus Rusanu Feb 23 '10 at 22:29
  • Ok, it seems I wasn't looking at precisely the right figures. In Perfmon I needed to be looking at **SQL Server: Memory Manager -> Total Server Memory**. And in SQL I can view the locked pages by typing: select * from sys.dm_os_process_memory. This blog explained it all: http://bit.ly/SQLRAMUsage – Django Reinhardt Feb 24 '10 at 11:51
1

Just a little comment: if you can freely "reset the server and flush out whatever is using it", then you can also simply stop the SQL Server service.

If you do that, you'll know for sure if it's actually SQL Server which is using up all your memory.

Massimo
  • 68,714
  • 56
  • 196
  • 319