Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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:
1 | SELECT column1, sys.fn_physlocformatter(%%physloc%%) FROM dbo.myTable WHERE xxx_No = 8071030185 |
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.
1 | SELECT * FROM sys.dm_os_buffer_descriptors WHERE page_id = 304 AND database_id = DB_ID() |
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.
