SQL timeouts in Sitecore instance due to High Index Fragmentation

Sitecore server performance depends a lot on underlying SQL server performance. If SQL server performance decreases, Sitecore server performance will decrease too. That's why its important to have SQL server maintenance plans. 

Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies. You can learn more about maintenance plans here.

We observed that a poor maintenance plan can lead to SQL timeout errors in application and the high fragmentation level in the SQL indexes can have major role to play in it.

Sitecore recommends keeping index fragmentation below 10%

If you see error in your logs which read something like below, its time for you to check your index fragmentation levels and revisit your SQL server maintenance plans - 

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How to know if index fragmentation is too high?

Below is a SQL query that Sitecore support team shared with us to know the level of index fragmentation in our databases - 

SELECT dbschemas.[name] as 'Schema',

dbtables.[name] as 'Table',

dbindexes.[name] as 'Index',

indexstats.avg_fragmentation_in_percent,

indexstats.page_count

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]

INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]

INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]

AND indexstats.index_id = dbindexes.index_id

WHERE indexstats.database_id = DB_ID()

ORDER BY indexstats.avg_fragmentation_in_percent desc

On running this query in Master DB, you will get results like in below snapshot- 

You can see that the master database has rather high index of fragmentation for the majority of tables.

Since Sitecore recommends keeping index fragmentation below 10%, we need to perform steps to bring the fragmentation level down.

How to perform defragmentation?

Sitecore support suggested us to perform the defragmentation using the following article - 

https://doc.sitecore.com/en/SdnArchive/upload/sitecore6/65/cms_tuning_guide_sc60-66-a4.pdf (2.1.6 How to Solve section)

This article was written long back and not everyone may find this article useful in their case based on Sitecore versions.

If your results only show couple of indexes with high fragmentation level, you can fix them manually as in snapshot below - 

You can also create scripts like below to do the same - 

ALTER INDEX Index_Name ON Table_Name REBUILD

And the entire logic can also be scripted to run the job in automated fashion. I hope you find this information useful!! Thanks :)