How to get exact dynamic memory usage of SQL server 2005?


I have a SQL server 2005 SP2 and is installed on a dedicated windows box.

I am referring to the below URL: http://msdn.microsoft.com/en-us/library/ms190924.aspx

I am trying to monitor the amount of memory used by SQL server at any point of time.

I am trying to understand the below 2 parameters: Target Server Memory (KB) --> Total amount of dynamic memory the server can consume.

Total Server Memory (KB) --> The committed memory from the buffer pool (in kilobytes). Note: This is not the total memory used by SQL Server.

As per my understanding, Total Server Memory (KB) is max amt of memory SQL server can use BUT is NOT the amt of memory SQL server is using right now.

Options that I found: 1) select * from sys.dm_os_performance_counters where counter_name like '%server memory%' 2) Check SQLSERVER.EXE from TASK MANAGER.

So HOW can I find out amt of memory that SQL server is using? I need to find the exact memory usage which may keep on changing at any point on time.


more ▼

asked Mar 26, 2010 at 02:38 AM in Default

avatar image

Mandar Alawani
376 40 43 48

does anyone have any inputs on this??

Apr 01, 2010 at 04:46 AM Mandar Alawani
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Hi Mander,

Checkout this article about MEMORYSTATUS and you might also find below scripts useful**.

            sum(virtual_memory_reserved_kb) as [VM Reserved],
            sum(virtual_memory_committed_kb) as [VM Committed],
            sum(awe_allocated_kb) as [AWE Allocated],
            sum(shared_memory_reserved_kb) as [SM Reserved], 
            sum(shared_memory_committed_kb) as [SM Committed],
            sum(multi_pages_kb) as [MultiPage Allocator],
            sum(single_pages_kb) as [SinlgePage Allocator]
            group by type
            order by 8 desc

Top 10 consumers of memory from Buffer Pool:

SELECT TOP (10) type, sum(single_pages_kb) AS [SPA Mem, Kb]
FROM sys.dm_os_memory_clerks
ORDER BY SUM(single_pages_kb) DESC

How much Physical RAM does the machine have?

SELECT physical_memory_in_bytes/1024 AS physical_memory_in_kb
FROM sys.dm_os_sys_info

Get PLE from sys.dm_os_performance_counters:

SELECT cntr_value AS 'Page Life Expectancy'
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND counter_name = 'Page life expectancy'

Find out what has the most entries in the buffer cache

 SELECT TOP (20)[Name], COUNT(*) AS 'Count'
    FROM sys.dm_os_memory_cache_entries
    GROUP BY [Name]

Also checkout sys.dm_exec_query_resource_semaphores on books online

Good Luck!

**I am not the author of these scripts. I found them somewhere on the net a while back and I was using them since and I figured somebody might benefit from them here . I dont remember where I found them so I couldnt give credit properly. Let me know if this is not appropriate and I will remove them.

more ▼

answered Apr 03, 2010 at 09:31 PM

avatar image

4.9k 33 39 43

(comments are locked)
10|1200 characters needed characters left

You might find a good starting point here - using DBCC MEMORYSTATUS.

The following code would give you the working set in KB:

CREATE TABLE #memusage (ID [int] IDENTITY(1,1), counterName [varchar] (MAX), value [bigint]);


SELECT value / 1024 FROM #memusage WHERE [counterName] = 'Working Set';

DROP TABLE #memusage;

more ▼

answered Apr 03, 2010 at 05:46 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Mar 26, 2010 at 02:38 AM

Seen: 8839 times

Last Updated: Mar 26, 2010 at 02:38 AM

Copyright 2016 Redgate Software. Privacy Policy