Check SQL Server a specified database index fragmentation percentage (SQL)

Introduction

This T-SQL sample script illustrates how to check index fragmentation of a specified database in SQL Server.

Scenarios

This script can be used for checking index fragmentation percentage of tables in SQL Server user databases. You can specify the database name and fragmentation percent, after you run the script, you will get the result.

Script

The following steps use SQL Server 2008 R2 and AdventureWorks2008 sample database as example.

Step1. Open Microsoft SQL Server 2008 R2

Step2. Open Microsoft SQL Server Management Studio
Start-> All programs-> Microsoft SQL Server 2008 R2-> Microsoft SQL Server Management Studio

Step3. Drag the script file to SQL Server Management Studio

Step4. Replace the following information with yours in the script

indexstats.avg_fragmentation_in_percent: In the script the  indexstats.avg_fragmentation_in_percent is greater than 30. Please sepecify the percent.

Step5. Run the script

The script selects AdventureWorks2008. 

After the script executing, we will get a result as the following figure shows:

 

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

SQL
Edit|Remove
SELECTOBJECT_NAME(ind.OBJECT_IDASTableNameind.nameASIndexNameindexstats.index_type_descASIndexTypeindexstats.avg_fragmentation_in_percentFROMsys.dm_db_index_physical_stats(DB_ID(), NULLNULLNULLNULLindexstatsINNERJOINsys.indexesindONind.object_id = indexstats.object_idANDind.index_id = indexstats.index_idWHEREindexstats.avg_fragmentation_in_percent > 30ORDERBYindexstats.avg_fragmentation_in_percentDESC

 

Additional Resources

SQL query performance might decrease when the SQL Server Database instance has high index fragmentation