x

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!!!!

more ▼

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

Mandar Alawani gravatar image

Mandar Alawani
376 33 38 44

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

         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.

more ▼

answered Apr 03, 2010 at 09:31 PM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

(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, value [bigint]);

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

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

DROP TABLE #memusage;
more ▼

answered Apr 03, 2010 at 05:46 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1951
x734
x59

asked: Mar 26, 2010 at 02:38 AM

Seen: 7809 times

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