Identify Database Fragmentation in SQL 2000 vs SQL Server 2005

By: Jeremy Kadlec | Read Comments | Print Jeremy is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com and SQL Server MVP since 2009. Related Tips: More

Problem
Database fragmentation is a hindrance to SQL Server performance because with excessive fragmentation more data pages need to be traversed to fulfill a query request.  Luckily, fragmentation is a manageable problem that is resolved by re-building the indexes to reduce the fragmentation at the index or table level.  Determining the tables that have high levels of fragmentation was a potentially time consuming process in the SQL Server 2000 environment because DBCC SHOWCONTIG had to be issued against the table or index.  Then this data needed to be analyzed to determine which indexes required rebuilding based on your personal tolerance for fragmentation.  Unfortunately, there was and still remains little agreement on the threshold when indexes should be rebuilt.  Some DBAs use a threshold in the 90% range while other DBAs were closer to the 70% range, but it seems that DBAs agree beyond the 70% fragmentation range that the indexes should be rebuilt. 

Solution
SQL Server 2000 - DBCC SHOWCONTIG

In SQL Server 2005 DBCC SHOWCONTIG remains a viable short term option to determine the database fragmentation, but will be removed in a future version of SQL Server.  Below outlines the DBCC SHOWCONTIG code to determine table level fragmentation for a sample table and database:

This output indicates that the table is free of fragmentation based on the logical scan fragmentation (0.00%), extent scan fragmentation (0.00%) and scan density statistics (100%).  This table should be considered healthy as it pertains to fragmentation because the indexes have been recently rebuilt.

SQL Server 2005 - sys.dm_db_index_physical_stats

Dynamic Management Views (DMVs) and Functions (DMF) are a new feature in SQL Server 2005 to help gather statistical information on particular portions of SQL Server from the core database engine to new features such as the CLR or Service Broker.  The sys.dm_db_index_physical_stats DMF addresses database fragmentation and can be queried directly for specific index fragmentation.  The key column from the query to determine the fragmentation is avg_fragementation_in_percent.  This column indicates the percentage of fragmentation for the table or index.  To fine tune the output, the query accepts 5 parameters which are database_id, object_id, index_id, partition_number and the mode as well as WHERE clause parameters as an example.

Below outlines a sample query to determine the fragmentation for the dbo.ProfilerResults tables in the ProfilerTest database.  The output shows three entries for the table with the overall table being free of corruption, but 1 of the non clustered indexes having 16% fragmentation based on the value from the avg_fragementation_in_percent column.  The ideal goal would be to have the avg_fragementation_in_percent value of 0 or reasonably between 0 and 10 percent.

Download the sample code here from the image above.

Next Steps

• Determine the last time database fragmentation was reviewed and indexes were rebuilt to resolve the fragmentation levels.
• If database maintenance is not performed regularly, work with your organization to determine a viable maintenance window to rebuild indexes and perform must have database maintenance.
• Based on your application determine the correct fragmentation threshold for rebuilding indexes.
• With the fragmentation threshold resolved, determine if the fill factor used to build or rebuild the indexes needs to be adjusted.
• Since DBCC SHOWCONTIG will removed in a future SQL Server version begin to migrate code to use sys.dm_db_index_physical_stats.

Share:

Share

Tweet

Free SQL Server Learning Waiting on Waits Let's connect on facebook! Tweet...tweet...tweet... Follow us on twitter

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