THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kevin Kline

Read Index Structure

I recently listened in on a conversation between SQL Server MVPs Cristian Lefter and our very own Itzik Ben-Gan.

Cristian asked, "Hi, I want to display an index structure from an AdventureWorks index an indexing chapter of a book.  I mean by that the content of the leaf level pages and the page numbers and some data for the leaf level pages.  How would you do that - DBCC IND and DBCC PAGE?"

I thought Itzik's response was quite a neat tip, so (with his permission) I'll share it here:

You can use the following code to generate a string with the page addresses in the leaf level in linked list order:

CREATE TABLE #DBCCIND(
    PageFID INT,
    PagePID INT,
    IAMFID INT,
    IAMPID INT,
    ObjectID INT,
    IndexID INT,
    PartitionNumber INT,
    PartitionID BIGINT,
    iam_chain_type VARCHAR(100),
    PageType INT,
    IndexLevel INT,
    NextPageFID INT,
    NextPagePID INT,
    PrevPageFID INT,
    PrevPagePID INT);

INSERT INTO #DBCCIND
EXEC ('DBCC IND(''AdventureWorks'', ''Sales.SalesOrderHeader'',-1)');

WITH LinkedList AS
(
SELECT 1 AS RowNum, PageFID, PagePID FROM #DBCCIND WHERE IndexID 
    = 1 -- specify index id here
    AND IndexLevel = 0
    AND PrevPageFID = 0
    AND PrevPagePID = 0

UNION ALL

SELECT PrevLevel.RowNum + 1,
    CurLevel.PageFID, CurLevel.PagePID FROM LinkedList AS PrevLevel
    JOIN #DBCCIND AS CurLevel
    ON CurLevel.PrevPageFID = PrevLevel.PageFID
    AND CurLevel.PrevPagePID = PrevLevel.PagePID )

SELECT
CAST(PageFID AS VARCHAR(MAX)) + ':'
+ CAST(PagePID AS VARCHAR(MAX)) + ' ' AS [text()] FROM LinkedList 
ORDER BY RowNum FOR XML PATH('') OPTION (MAXRECURSION 0);

DROP TABLE #DBCCIND;

For example, here I requested the layout of the linked list in the clustered index of AdventureWorks.Sales.SalesOrderHeader, and got the following output for the clustered index (IndexID = 1):

    1:5280 1:5281 1:5282 ... 1:6024 1:6025 1:6026

And this is what I get for IndexID = 2:

    1:8184 1:8185 1:8186 ... 1:8335 1:8336 1:8337

Both indexes are non-fragmented and were created when the data existed so it seems that the page numbers keep increasing sequentially, but with indexes that have fragmentation the picture would naturally be different.

Anyway, you can then use DBCC PAGE to investigate the contents of particular pages.

Published Thursday, January 25, 2007 5:52 PM by KKline
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About KKline

Kevin Kline is a well-known database industry expert, author, and speaker. Kevin is a long-time Microsoft MVP and was one of the founders of PASS, www.sqlpass.org.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement