Identify and resolve SQL Server problems BEFORE they happen with SQL diagnostic manager

SQL Server Fragmentation Custom Detection Procedure, Page Linkage Part 7

By: Chad Boyd | Read Comments | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: More

In post #6 of the series, I mentioned that I often get asked for alternative methods of looking at fragmentation, a way to view the page chain or linkage, and ways to get insight into which pages are out of order in a given structure. There is nothing super graceful to be honest, but you can make use of a DBCC statement (DBCC IND()) to get page linkage and order data, then using some relatively complex querying against the data captured you can see some of this type of information.

This post has an attachment (customFragInfo.sql) that contains the following procedures:

• zcpReinitDbccInd - This procedure takes a single parameter (@tableName) which defines which table to get page-level information for. The procedure will then create a single table called 'ztblDbccInd' (if it exists it will drop it) which is used to store the results from DBCC IND() into. This will produce a table that contains a single record for every allocated page for all indexes in the given table. We then strip out all pages except data pages, add a few columns to the table, and update them to include indicators for the appropriate logical and physical positions of each page (based on the page linkage information present on the pages).  This is all done with a fairly complex recursive CTE and update statement. We then build some indexes and call it good. This procedure has to be called first on the table you want to analyze with the following procedures.
• zcpShowPageLinkage - This will show a record for each page at the leaf-level of each index (or all data pages in a heap) sorted by the logical ordinal position of each page - the logical ordinal position meaning the order the page should fall in if you were to scan the data from front-to-back using an ordered-index scan of the data (doesn't apply for heaps, since they don't hold any logical order). If you see a value other than '0' in the physicalPageDiff column, this tells you that this page is that # of pages away from the prior page in the logical order, which indicates that there are other pages of data between the 2 pages - if the value is negative, then that page is earlier in the file; if positive, that page is later in the file (which comes into play when performing a scan of data).
• zcpShowFragSummary - This will show a summary of the fragmentation for the given table's index(es) and optionally an additional 2 result sets if you set the single parameter (@showFragPages) to 1. The 1st is a summary of the count of logically fragmented pages, file-level fragmented pages, non-contiguous pages, non-leaf pages, and the total # of pages for each index/heap in a given table. The 2ndshows each page that is logically fragmented, and earlier physically within the file than the prior page, as well as the previous and next page logically ordered for comparison purposes (each logically unordered page will show a 'position' value of '00', whereas the previous page will have a position value of '-1', and the next page will show a position value of '+1'). The 3rd result set is the same as the2nd, except it shows logically unordered pages that are later physically in the file than the prior page.

That's all of them, we'll be using these in the final post in the series where we get a full hands-on script, enjoy!

Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.

Related Tips: More | Become a paid author

Last Update: 2/22/2008

Share:

Share

Tweet

Free SQL Server Learning Building Blocks for Your SQL Server Career Database development best practices What Are You Waiting For?

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"SQL doctor ROCKS! As soon as I ran it, problems that have been giving me headaches were identified and cured." Optimize your SQL Server storage: compress live databases by up to 90%. Download a free trial. SQL Server Consultants - What you don't know could be your biggest asset - Guaranteed Results Join the over million SQL Server Professionals who get their issues resolved daily. The SQL Server Security THREAT - It’s Closer Than You Think