How to assess overall database index fragmentation level

book

Article ID: 181082

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Attached to this KB article is an SQL query that assesses the level of index fragmentation within an SQL Server 2005 database (and later).

This query shows the size of the index (in 8KB pages), an estimated amount of fragmentation (as a percentage), and the product of these two values. If the product is greater than 5000 the index is displayed. Generally, indexes with values greater than about 10,000 are considered too fragmented.

This query lists larger tables with moderate or high fragmentation percentages, and only lists smaller table with high or very high fragmentation percentages.

If indexes are too fragmented, then rebuilding indexes more often may significantly improve performance. See for example:

   Creating a maintenance plan in SQL Server 2005 or 2008 to optimize database performance
   http://www.symantec.com/docs/HOWTO8589

Note, this query is based on estimated fragmentation level, and highlights indexes based on a simple heuristic. As such, while the results may help identify cases of extreme fragmentation, it may be less helpful in case of moderate to low fragmentation.

Attachments

show-index-fragmentation-3c.sql get_app