How SQL Index Fragmentation will kill Sitecore’s Performance

Thought I wrote a blog post about this years ago, but apparently I didn’t.

Problem

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%.

Solution

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.