Home > PASS, PASS Summit 2013, SQL Server, T-SQL > Strange Results from sys.fn_physlocformatter

Strange Results from sys.fn_physlocformatter

I’m writing from Charlotte, North Carolina where I’m attending my fifth PASS Summit. I hope to write a series of posts recapping my experiences, but I wanted to write about something I learned in Paul White’s (b|t) pre-conference session, Understanding the Optimizer and Interpreting Execution Plans. The session was amazing and I think I’ll spend the next year going through his scripts.

If you’ve read my blog you know I love internals. Paul introduced me to a function called sys.fn_physlocformatter. In SQL Server 2008 and beyond, %%physloc%% is a virtual column that returns the file, page and slot of each row of a result set in byte-reversed, hex format. That means it is hard to read. If you don’t believe me, here is an example of the query and its result:

USE MYTEST
GO

SELECT %%physloc%% AS [physloc],*
FROM MYTestTable

 

physloc results

 

fn_physlocformatter takes that hex value and formats it into a colon-delimited value that is much more readable. Here’s the query above, replacing %%physloc%% with fn_physlocformatter:

SELECT sys.fn_PhysLocFormatter(%%physloc%%),*
FROM MYTestTable

 

fn_physlocformatter results

 

After Paul showed us the function, I did a search to get more information and came across this post. Using the example provided, I wrote the following query to return a count of the number of rows written to each page:

SELECT SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%),4,(CHARINDEX(':',sys.fn_PhysLocFormatter(%%physloc%%),4) - 4)) [Page ID], COUNT(*) AS [Row Count]
FROM MYTestTable
GROUP BY SUBSTRING(sys.fn_PhysLocFormatter(%%physloc%%),4,(CHARINDEX(':',sys.fn_PhysLocFormatter(%%physloc%%),4) - 4))
ORDER BY [Row Count]

The sample code from the link above contains code to insert nine rows to MYTestTable2 at a time. I’ve listed the table definition and insert code below.

USE MYTEST
GO

CREATE TABLE MYTestTable2
(MYID INT IDENTITY, 
NAME CHAR(500), 
LNAME CHAR(500))
GO

INSERT INTO MYTestTable2 VALUES ('NAME-1','LNAME-1');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-2','LNAME-2');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-3','LNAME-3');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-4','LNAME-4');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-5','LNAME-5');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-6','LNAME-6');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-7','LNAME-7');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-8','LNAME-8');
GO
INSERT INTO MYTestTable2 VALUES ('NAME-9','LNAME-9');
GO

Because the rows are 1008 bytes long, seven rows will fit on one 8kb data page. After the initial insert of nine rows, I expected to see a page containing seven rows followed by a page with two rows. Here are the results after the initial insert:

 

Initial Insert Results

 

I continued running the nine inserts, to confirm that my code was working as expected. And for the next five runs, it did. The strange results didn’t show up until the seventh run, where I saw this:

 

strange results

 

After the seventh insert, something was causing seven pages to be allocated each with a single row inserted. This didn’t make any sense to me, so I continued investigating. I’ll show you the results of those investigations in my next post, sys.fn_physlocformatter – The Mystery Deepens!

  1. Ken
    October 23, 2013 at 11:43

    Recall that SQL Server organizes pages (8K each) into extents that hold 8 pages (64K total). In addition, extents can be “mixed” where up to 8 different tables can have a page or “uniform” where all the pages are used by a single object. I suspect that your table has grown to the point where SQL Server has switched from using a mixed extent to a uniform extent.

    • October 24, 2013 at 10:10

      Ken, you are correct. The 1 row per page behavior happens on the first uniform extent. It still doesn’t explain why that happens only for that first uniform extent. I have a follow up post to write this weekend. Stay tuned.

  1. No trackbacks yet.

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

%d bloggers like this: