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
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
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.
Here is my result on a database that is less than a month old. Media Library is 87% FRAGMENTED.
See My post on how to fix this at: Defrag SQl