Sunday, October 16, 2016

Physical Location of Records in SQL Server


The unit of data storage in SQL Server is the page.The disk space allocated to  mdf file in a database is logically divided into pages numbered contiguously from 0 to n and all disk I/O operations are performed at the page level.

The page size is 8 KB (8192 bytes). So SQL Server databases have 128 pages/MB.

Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.

Say one of the pages in your table is corrupt and while repairing the corrupt pages, you may eventually end up loosing some data. You may want to find out which records are on the page.


To do so, use the following undocumented T-SQL %%physloc%% virtual column:



Query:

SELECT *, %%physloc%% AS physloc
FROM Person.Address
ORDER BY physloc


As you can see, the last column represents the record location. However the hexadecimal value is not in a human readable format. To read the physical record of each row in a human readable format, use the following query:


SELECT * 
FROM Person.AddressType 
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)

this query will provide the hexadecimal address in form of page_id,slot_id

However this system tabular function fn_PhysLocCracker is available only in 2008 and above.


Original Article: https://blogs.msdn.microsoft.com/sql_pfe_blog/2016/09/15/where-is-a-record-really-located/