Find Physical Location of Records in SQL Server

Indexes

There is often a disconnect between what you want/need to know in SQL Server, and what is actually happening at the application or system level.  You should know that SQL Server database files are organized in 8KB (8192 bytes) chunks, called data pages. As we add a row of data in a table, SQL Server allocates at least one 8KB page to store that data. If we store more than 8KB of data, a second page file is added, and so on until we get to a 2 TB database and too many data pages to count. The lowest level of container, at the system level, is that 8KB page file that you might have to find one day.

During a corruption event, for example, you may lose data at that 8KB page file level, so you probably want to find out which records are on the target data page. Luckily there is an undocumented Transact-SQL way to get that location from SQL Server, by using the %%physloc%% virtual column.

If you add this column onto your query, you will get some results that help track down the page file locations:

SELECT TOP 10 [Name]
              ,[MenuType]
              ,[ALC]
              ,[WATER]
              ,[NoBev]
              ,%%physloc%% as PhysicalLocation
FROM [Restaurant].[dbo].[BeverageMaster];

You’ll get the results back like this example:

Name       MenuType ALC WATER NoBev PhysicalLocation
MR PIBB         1     0   0     0    0x5000000024000000
MM ORANGE       1     0   0     0    0x5000000024000100
ROOT BEER       1     0   0     0    0x5000000024000200
PINK LEMONADE   1     0   0     0    0x5000000024000300
HiC FRUIT PUNCH 1     0   0     0    0x5000000024000400
CHERRY COKE     1     0   0     0    0x5000000024000500
TO GO SODA      1     0   0     0    0x5000000024000600
TO GO COFFEE    1     0   0     0    0x5000000024000700
TO GO TEA       1     0   0     0    0x5000000024000800
WATER           1     0   1     0    0x5000000024000900

This isn’t very useful. As you can see from the results, the last column represents the actual record location. This value is not in a readable format, at least by most humans I know. To help you read the location of each row in a physical record you can use this simple query:

SELECTT TOP 10 [Name]
              ,[MenuType]
              ,[ALC]
              ,[WATER]
              ,[NoBev]
              ,physloc.*
FROM [Restaurant].[dbo].[BeverageMaster]
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) as physloc;

The sys.fn_PhysLocCracker function (available in SQL Server 2008 and newer)  takes the %%physloc%% and represents a human readable format fileid, pageid, and slotid.

Name       MenuType ALC WATER NoBev file_id  page_id  slot_id
MR PIBB         1     0   0     0    36       80       0
MM ORANGE       1     0   0     0    36       80       1
ROOT BEER       1     0   0     0    36       80       2
PINK LEMONADE   1     0   0     0    36       80       3
HiC FRUIT PUNCH 1     0   0     0    36       80       4
CHERRY COKE     1     0   0     0    36       80       5
TO GO SODA      1     0   0     0    36       80       6
TO GO COFFEE    1     0   0     0    36       80       7
TO GO TEA       1     0   0     0    36       80       8
WATER           1     0   1     0    36       80       9

Remember this is just the tip of the iceberg, so please investigate this more if you are interested. You will find it very difficult to find “official” information, but you can perform a quick internet search to find related articles.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s