mssql sqlserver查看占用内存的方法分享


摘要:
下文讲述sqlserver占用内存的方法分享,如下所示:
实验环境:sqlserver 2008 R2

 
---查看整个数据库占用系统内存的方法
SELECT 
    ISNULL(DB_NAME(DATABASE_ID),'RESOURCEDB') AS [数据库名称],
    CAST(COUNT(ROW_COUNT) * 8.0 /(1024.0) AS DECIMAL(28,2)) AS '内存占用(MB)'
FROM SYS.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DATABASENAME
 

---sqlserver服务占用总内存查看方法 
SELECT SUM(a)+SUM(b) FROM
(
    SELECT 
        SUM(single_pages_kb) AS a
        ,SUM(MULTI_PAGES_KB) AS b        
    FROM SYS.dm_os_memory_clerks
    GROUP BY [TYPE]   
) AS A 
UNION ALL 
SELECT COUNT(ROW_COUNT) * 8.0 FROM SYS.dm_os_buffer_descriptors