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(
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
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 )
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.