Steps to assess overall database index fragmentation levels

Products

• IT Management Suite

Issue/Introduction

How to assess the overall database index fragmentation levels?

Environment

ITMS 8.x

Resolution

Attached to this KB article is a SQL query that assesses the level of index fragmentation within a 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 tables with high or very high fragmentation percentages.

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

   158633 Creating a maintenance plan in SQL to optimize database performance

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.