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

Question

question

Mandar Alawani asked Mar 26, '10

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

Hi,

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.

NEED HELP ASAP!!!!

sql-server-2005 sql memory

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mandar Alawani commented · Apr 01, 2010 at 04:46 AM

does anyone have any inputs on this??

0 ·

Answer 1 · 2010-04-03T21:31:39Z

DaniSQL answered Apr 3, '10

Hi Mander,

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

         select 
            type,
            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]
         from 
            sys.dm_os_memory_clerks 
            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
GROUP BY type
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]
    ORDER BY COUNT(*) DESC;

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-04-03T17:46:50Z

Matt Whitfield answered Apr 3, '10

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]);

INSERT INTO #memusage
EXEC ('DBCC MEMORYSTATUS');

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

DROP TABLE #memusage;

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question