Tuesday, December 22, 2009

DBCC MEMORYSTATUS

How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005:
http://support.microsoft.com/kb/907877/en-us


Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage:
http://support.microsoft.com/kb/271624



select * from sys.dm_os_performance_counters
SELECT
SUM(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
FROM sys.dm_os_memory_clerks

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

SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
FROM
sys.dm_os_sys_info

3 comments:

  1. This is useful but we need more detailed information about memory usage by SQL Server. For example, buffer cache, procedure cache etc.

    See my blog for cloud computing system here.

    ReplyDelete
  2. I agree. DBCC memorystatus is very useful to go deep inside sql engine.

    my blog is about iPad 2 keyboard reviews.

    ReplyDelete
  3. the error("Exception of type 'System.OutOfMemoryException' was thrown) will appear.
    what should exact solution and how trace it ????????

    ReplyDelete