--記憶體次數統計
SELECT
(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified]
ORDER BY 3 DESC
--記憶體耗用統計
SELECT
[DatabaseName],
ISNULL([Dirty],'0') AS [Dirty],
ISNULL([Clean],'0') AS [Clean],
ISNULL([Total],'0') AS [Total]
FROM(
SELECT
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database'
ELSE ISNULL(DB_NAME (database_id),'Total')
END) AS 'DatabaseName',
(CASE WHEN ([is_modified] = 1) THEN 'Dirty'
WHEN ([is_modified] = 0) THEN 'Clean'
ELSE 'Total'
END) AS 'State',
COUNT (*)/128 AS 'SizeInMB'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified] WITH CUBE
) AS SourceTable
PIVOT(SUM([SizeInMB]) FOR [State] IN (Clean, Dirty, Total)) AS PivotTable
ORDER BY 4
