Database Cache Memory in Performance Monitor drops down significantly after DBCC CheckDB

Question

We have been monitoring some SQLServer: Memory Manager's metrics, and noticed that after DBCC CheckDB job, metric

Database Cache Memory (KB)

drops down significantly. If to be exact, it dropped from 140 GB cached DB memory to 60 GB. And after that, slowly ramp up again during the week. (Amount of "Free Memory KB", went from 20 to 100 GB right after CheckDB)

DBCC CheckDB is run every Sunday, so Database Cache Memory has to ramp up back again each week

What is the behavior of this ? Why CheckDB pushes database pages out of memory ?

Second question is why "buffer cache hit ratio" did not change after DBCC CheckDB completes ?

It was 99.99% on average and after DBCC CheckDB job it drops to ~98.00%, and returns back to 99% pretty fast while I expected "buffer cache hit ratio" to drop significantly because database data has to be read from storage to RAM again ?

Regarding BCHR, the reason why it didn't drop further can be because CHECKDB code could be done using read-ahead (RA), and I/O done by RA isn't included when calculating BCHR. Which in turn makes BCHR a pretty useless perf counter. — Tibor Karaszi, Aug 01 '19 at 19:13

Randi Vertongen · Accepted Answer · 2019-08-01T11:39:03.797

We have been monitoring some SQLServer: Memory Manager's metrics, and noticed that after DBCC CheckDB job, metric

Database Cache Memory (KB) drops down significantly. If to be exact, it dropped from 140 GB cached DB memory to 60 GB

This is correct, you can clearly see this behaviour when this example DBCC CHECKDB command completes at 21h45

Why

This behaviour is due to the database snapshot created by the DBCC command being dropped, removing all of its objects in memory.

You can replicate the behaviour by creating a snapshot of a database, loading some data in memory, and then dropping that snapshot

  CREATE DATABASE MY_DATABASE
     GO
     USE MY_DATABASE 
     GO
     CREATE TABLE dbo.bla(id int identity(1,1) PRIMARY KEY NOT NULL,
                          val int,
                          val2 char(100));



    INSERT INTO dbo.bla(val,val2)
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),'bla'
    FROM master..spt_values spt
    CROSS APPLY master..spt_values spt2;
    GO

    CREATE DATABASE MY_DATABASE_SNAPSHOT
     ON  
     (  
     NAME ='MY_DATABASE',  
     FILENAME ='D:\DATA\MY_DATABASE.ss'  
     ) 
     AS SNAPSHOT OF MY_DATABASE;

     GO


    USE MY_DATABASE_SNAPSHOT
    GO
    SELECT * FROM dbo.bla;

     SELECT
      COUNT(file_id) * 8/1024.0 AS BufferSizeInMB
    FROM sys.dm_os_buffer_descriptors;

BufferSize before dropping the snapshot

BufferSizeInMB
1061.70312  --before

Dropping the snapshot

USE master
GO
DROP DATABASE MY_DATABASE_SNAPSHOT ;

BufferSize after dropping the snapshot

BufferSizeInMB
824.179687 --after

Second question is why "buffer cache hit ratio" did not change after DBCC CheckDB completes ?

This depends on how fast the data is loaded back in your buffer cache.

If your buffer pool fills up over a longer time, it should amount to this ratio staying higher on average.

This corresponds with this part of your question:

... It (Buffer pool datasize) dropped from 140 GB cached DB memory to 60 GB. and after that, slowly ramp up again during the week...

thank you Randi! when we talk about Internal Database Snapshot made by DBCC CheckDB, is it creating it in memory ? or at disk ? or both — Aleksey Vitsko, Aug 02 '19 at 07:29

Database Cache Memory in Performance Monitor drops down significantly after DBCC CheckDB

1 Answers1