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.
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30 ORDER BY indexstats.avg_fragmentation_in_percent DESC
SELECTOBJECT_NAME(ind.OBJECT_ID) ASTableName, ind.nameASIndexName, indexstats.index_type_descASIndexType, indexstats.avg_fragmentation_in_percentFROMsys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstatsINNERJOINsys.indexesindONind.object_id = indexstats.object_idANDind.index_id = indexstats.index_idWHEREindexstats.avg_fragmentation_in_percent > 30ORDERBYindexstats.avg_fragmentation_in_percentDESC