Monday , December 4 2023

How do you know that a record is in Data Cache On SQL Server


How do you find out if a specific record in a particular table is in the Buffer pool / data cache or in the disk?

First, you need to find the page ID of the record you’re interested in.

For this, you can use a command like the following:

The result of this command will be as follows:

The data returned by sys.fn_physlocformatter (%% physloc %%) shows us where the corresponding record is physically stored.

The description is as follows:

1 – The ID of the file containing the Page.
2 – Page ID
3 – Slot number


Now we’ve got all the physical information we need. Using this information, you can get more information about the corresponding Page with the DBCC PAGE command as I mentioned in a previous article named “Find the logical and physical database source that is experiencing IO problem“.

With the DMV below, you can also check whether the relevant Page is in Data Cache.

When you run the above command, the result will return as follows:

If a result is returned, the corresponding record’s Page is in Cache. So when you want to do something about this registry, SQL Server will not have to go to disk and do physical IO, it will go to RAM where it can work much faster and it will perform there.

Note: You cannot find official Microsoft documents for the command sys.fn_physlocformatter (%% physloc %%), because it is a non-documented command by Microsoft.


About Ekrem Önsoy

The original article was written in Turkish by Ekrem Önsoy and translated to English by dbtut with the consent of the author. The copyright of the article belongs to the author. The author shall not be liable in any way for any defect caused by translation.

Leave a Reply

Your email address will not be published. Required fields are marked *