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

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.

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;