"Almost" everything SQL Server does happens through the buffer pool. Here, the buffer "pool" is a combinations of memory regions used by SQL Server. The largest and main region is the "buffer cache," which is why most discussions about SQL Server's inner workings, including performance issues and solutions, involve the buffer cache.
For example, if a query needs to read a page, it will be served from the buffer cache... if the requested page is not in there then it will be fetched from the disk into the buffer cache first, then served to the query. And if a query modifies a page, that too gets modified in the buffer cache first... then some other internal SQL Server processes will copy the modified pages from buffer cache to the disk.
Additionally, the buffer cache also contains data that doesn't yet exist on the disk (in SQL Server data files). This includes new data when it's added. Although SQL Server immediately writes this data to the transaction log file (unless Delayed Durability is enabled), it's not immediately written to the data files. Instead, SQL Server waits until the next CHECKPOINT process, which then writes these "dirty" pages to the data files. The LAZY WRITER process also writes dirty pages to the disk, but it does so continuously in a less aggressive manner.
There are exceptions though where SQL Server may bypass the buffer cache entirely and performs the direct disk I/O, for example for bulk load and bulk inserts.
Why size of dirty pages matters:
Causes:
Some reasons I can think of top of my head are:
- A simple and likely reason is that you have relatively high number of writes i.e. high number of INSERTS, UPDATES, and/or DELETES. Or that you have very large, long running transactions in progress, generating significant amount of dirty pages.
- There is a memory pressure or more accurately the buffer pool is under pressure and can't write dirty pages to disk fast enough. This could be an indication that not enough memory is allocated to SQL Server or a more likely explanation is that your database needs a tuning.
- The disk I/O is slow, i.e., the I/O subsystem is the bottleneck, causing a high count of dirty pages. Bluntly speaking, if you have slower disks, it can cause longer I/O queue length and a backlog of dirty pages waiting to be written to disk. Of course, the impact of slow I/O would be felt and manifested in several ways.
- Heavy use of temporary tables can also cause high number of dirty pages
- If the interval between checkpoints is too long then dirty pages can build up in the buffer pool.
Calculating size of dirty pages:
To calculate the size of the dirty pages in buffer cache, SQL Server exposes some meta data regarding the pages in buffer cache through sys.dm_os_buffer_descriptors DMV. Though I am not a fan, this still is the primary way to ascertain and monitor the size of dirty pages.
If you have VIEW SERVER STATE permission or, the SYSDBA role membership, you can query sys.dm_os_buffer_descriptors, mostly to get some summary information as it only contains the meta data. The below query is an example of such, which returns the ratio/percentage of dirty pages against the overall/total size of the buffer cache.
;WITH cte
AS (SELECT Cast(Count(*) AS FLOAT) Total_Pages,
Count(CASE WHEN is_modified = 0 THEN 1 END) Clean_Pages,
Count(CASE WHEN is_modified = 1 THEN 1 END) Dirty_Pages
FROM sys.dm_os_buffer_descriptors
)
SELECT Total_Pages,
Clean_Pages,
Dirty_Pages,
Dirty_Pages_Percentage = Cast(( Dirty_Pages / Total_Pages ) * 100
AS DECIMAL(4, 2))
FROM cte;
To get this information for each database:
;WITH cte
AS (SELECT
database_id, Case When database_id = 32767 then '(ResourceDB)'
Else Coalesce(db_name(database_id),'***** TOTAL *****') End [Database],
Cast(Count(*) AS FLOAT) Total_Pages,
Count(CASE WHEN is_modified = 0 THEN 1 END) Clean_Pages,
Count(CASE WHEN is_modified = 1 THEN 1 END) Dirty_Pages
FROM sys.dm_os_buffer_descriptors
GROUP BY ROLLUP (database_id))
SELECT [Database],
Total_Pages,
Clean_Pages,
Dirty_Pages,
Dirty_Pages_Percentage = Cast(( Dirty_Pages / Total_Pages ) * 100
AS DECIMAL(4, 2))
FROM cte
ORDER BY Total_Pages desc;
A word of caution: if you have a very large buffer cache (like 750GB or even 75GB), querying this DMV can be very very slow. The larger the cache, the more data the DMV has to process. For a 75GB cache, it might need to process about ~10 million rows; for a 750GB cache, that number could jump to ~100 million rows!
Please also note that when using the sys.dm_os_buffer_descriptors tool to view the buffer cache contents, keep in mind that it also includes pages used by SQL Server's internal Resource database. Often, these pages are left out when people are summing up or analyzing the buffer cache data. However, in this particular example, I've chosen to include them in the count and analysis.
Alternative Method:
If you have huge buffer cache like 2TB or more, using sys.dm_os_buffer_descriptors can be impractical. While not as detailed, you can utilize the DBCC MEMORYSTATUS to get the size of dirty pages:
IF OBJECT_ID('tempdb..#MemoryStatus') IS NOT NULL DROP TABLE #MemoryStatus; CREATE TABLE #MemoryStatus ( ID INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(100), VALUE BIGINT ); INSERT INTO #MemoryStatus (NAME, VALUE) EXEC ('DBCC MEMORYSTATUS WITH TABLERESULTS'); SELECT CASE WHEN NAME='Database' THEN 'Buffer Pages' WHEN NAME='Dirty' THEN 'Dirty Pages' ELSE NAME END NAME, VALUE / 128 SIZE_MB FROM #MemoryStatus WHERE NAME in ('Database','Dirty');
I would really prefer using windows performance counters for this but alas... there are no windows performance counters currently available that would measure size of the dirty pages. There are some performance counters (e.g. Checkpoint pages/sec) can be used to get some idea about the size of dirty pages, I have though found them to be not adequate or accurate enough.
Resources:
sys.dm_os_buffer_descriptors