Home / server / Questions / 115957
Accepted
Django Reinhardt
Asked: 2010-02-24 12:07:09 +0800 CST

Viewing SQL's cache RAM usage?

  • 772

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.

windows windows-server-2008 sql-server memory memory-usage

2 Answers

  1. Best Answer

    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).

    • 4

  2. 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.

    • 1