Thought I wrote a blog post about this years ago, but apparently I didn’t.
Poor index maintenance is a major cause of decreased SQL Server performance, which in turn will impact your Sitecore’s performance. The Sitecore databases contains tables with numerous entries, that get updated frequently, therefore high index fragmentation will occur.
Detecting SQL Server index fragmentation
The following script displays the average fragmentation, and as a help generates the SQL query to fix it.
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent, 'ALTER INDEX ' + QUOTENAME(ind.name) + ' ON ' +QUOTENAME(object_name(ind.object_id)) + CASE WHEN indexstats.avg_fragmentation_in_percent>30 THEN ' REBUILD ' WHEN indexstats.avg_fragmentation_in_percent>=5 THEN 'REORGANIZE' ELSE NULL END as [SQLQuery] -- if <5 not required, so no query needed FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE --indexstats.avg_fragmentation_in_percent , e.g. >10, you can specify any number in percent ind.Name is not null ORDER BY indexstats.avg_fragmentation_in_percent DESC
Below you can see the typical result of running the script above. I was shocked as the majority of indexes on my local SQL server where over 99%.
The script above generates the SQL statements needed to defragment the affected indexes, so you can automate the defragmentation process, using SQL Server Maintenance Plans.
Anyway I hope this helps keeping you sitecore solution running at its best, Alan
Does SiteCore, by any chance, use Random GUIDs for keys on the indexes? If so, I know how to make it so that they hardly fragment at all. For example, I have one demonstration where I insert 100,000 rows per day for 58 days resulting in almost no page splits and <1% logical fragmentation.