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
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:
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: http://donboulton.com/blogs/sqldefrag
Download this script at Check Index Defragmentation.