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

From: Technet Script Center

A Dynamic CMS using SQL Server most always has fragmentation. When files or data are in the wrong place then SQL Server has to spend time looking for the missing data, slowing the whole process down and increasing load times for your site


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.


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

Step 1. Open Microsoft SQL Server 2008 R2

Step 2. Open Microsoft SQL Server Management Studio Start- All programs- Microsoft SQL Server 2008 R2- Microsoft SQL Server Management Studio

SQL Management

Step 3. Drag the script file to SQL Server Management Studio

Step 4. 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 specify the percent.

Step 5. Run the script

Script Results

This script selects AdventureWorks2008.

After the script executing, we will get this result:

Web Matrix is what I use to check Fragmentation

Open Web Matrix select databases and logon to your database, Select new query and paste IndexDefrag query in workspace the execute.

Web Matrix

Here is my result on a database that is less than a month old. Media Library is 87% FRAGMENTED.

Web Matrix Script Results

Additional Resources

See My post on how to fix this at: Defrag SQl

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

Download this script .zip file

Check Index Defragmentation

Related Posts