This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to check dirty pages or clean pages in SQL Server Memory area. Most of the DBAs know about DBCC DROPCLEANBUFFERS which clear the data or pages from the memory area.
We need to execute DBCC DROPCLEANBUFFERS while testing the performance of queries.
First time when you execute your query, it requires physical reads but for the second execution it stores the information in memory, and it will perform fast compare to previous execution.
But few people said that DBCC DROPCLEANBUFFERS is not working all the times. I don’t know why but found below script to check dirty pages and clean pages in the Memory area.
Dirty pages mean has not written to disk and Clean pages say the page is brought in to memory by the buffer manager.
SQL Server: DBCC to Clean Cache and Clean Buffer for Stored Procedure
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SELECT DatabaseName ,DirtyPageCount ,CleanPageCount ,[DirtyPageCount] * 8 / 1024 AS DirtyPageInMB ,[CleanPageCount] * 8 / 1024 AS CleanPageInMB FROM (SELECT (CASE WHEN ([database_id] = 32767) THEN N'Resource Database' ELSE DB_NAME ([database_id]) END) AS [DatabaseName], SUM (CASE WHEN ([is_modified] = 1) THEN 1 ELSE 0 END) AS [DirtyPageCount], SUM (CASE WHEN ([is_modified] = 1) THEN 0 ELSE 1 END) AS [CleanPageCount] FROM sys.dm_os_buffer_descriptors GROUP BY [database_id]) AS [buffers] ORDER BY [DatabaseName] GO |
Reference is taken from sqlskills.com:
Leave a Reply