Where is my data?

As you might already know, SQL Server stores the data in the databases in *.mdf and *.ndf files. And internally, these files are divided up into 8Kb structures called Pages. And sometimes you might want to know exactly in which file and which page a certain row of data is stored.

Why would you ever need to know that, apart from pure curiosity? One example could be to verify how many rows you can fit on one page with you current database design, and maybe how designchanges affects this. And it can also be a good way to visually see the fragmentation of your clustered indexes. Or even to view the effects of converting a heap to a table with a clustered index.

Let me show you how this can be done, using MSDB as an example database:

Select  jh.*, pl.* from msdb.dbo.sysjobhistory jh

Cross apply fn_physloccracker(%%physloc%%) pl

This gives us all rows and all columns in the sysjobhistory table, and 3 additional columns to the right of the normal columns:

File_ID is the id number of the mdf/ndf datafile.

Page_ID is the id of the 8Kb page that the specific row is stored on

Slot_ID is the ordinal place in the page that this specific row is stored on, as a page can generally contain several rows.