SQL Fragmentation


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


 Comment Form

Notify me of replies by email, Subscribe

Item Reviewed
Title: Jekyll Image Gallery
Adding comments and reviews per page for google positioning. Someone need to try it out
Item Reviewed
Title: Home
Hi, My name is Randy and I was looking at a few different sites online and came across your site donboulton.com. I must say - ...
Item Reviewed
Title: No Spam
Posted this with drag and drop submit button defeating spambots. No more honeypots or captchas - recaptcha.
Item Reviewed
Title: Defrag SQL
This is an old post but still relevant to SQL 2016. Some updates will be forthcoming in the near future for SQL Server 2018.

Add A Page Review

Add A Rating: