SQL Fragmentation

SQL Fragmentation

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


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

From: http://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-a-a5758043#content

Orchard CMS is no different that any other Web Site using SQL Server where fragmentation does exist. 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.

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
SQL Management

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

Step5. Run the script

The below script selects AdventureWorks2008. 

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

Script Results

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.

Index Fragmentation

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

SQL Management

Additional Resources

See my post on how to fix this at: http://donboulton.com/blogs/sqldefrag

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

Download this script at Check Index Defragmentation.

No Comments

Comments are Moderated, from, "(Spam-bots)", before they are posted.