Index Structure

Published 04 March 11 02:48 PM | MartinBell

At the Manchester and Leeds Usergroup meetings David Betteridge presented a great talk about Index Structures. The slides for the talk can be found here and the scripts are here.

David wrote a great application to display page information and the structure of the indexes. This was a wonderful way to show the differences in the structure of indexes of different types.

The application uses some of the undocumented functions found in SQL 2008, for more information on these see Paul Randal's blog. The application will connect to the local default instance and IndexDemo database. This database is created at the start of the “Demo 1 - Max Row Size.sql” script.



The first screen you will see allow you to view the pages in a table if you choose the show table button, you can view the data pages and the data they contain. For a heap the pages aren’t linked so you will see the data as follows:



For an table with a clustered index the pages are doubly-linked shown by the arrows:



The show index option will display index pages for clustered and non-clustered indexes, such as the following clustered index with 1 index page and 3 data pages the output from DBCC IND shows page 94 as the index page and pages 90, 109 and 110 as data pages. 

 

The application shows this graphically 

 



This is a great application and if you follow the scripts David provided for the presentation you will see exactly how indexes are structured.

Filed under:

Comments

# Show Indexes | AllGraphicsOnline.com said on March 16, 2011 12:55 AM:

Pingback from  Show Indexes | AllGraphicsOnline.com

This Blog

SQL Blogs

Syndication