If you experience decreasing performance in your Sitecore solution, it’s not always your fault. And it’s not always Sitecore’s fault either.
The database is usually the last place I would look for performance issues. Most performance issues comes from poor XSLT design (which is why I quit using XSLT’s). Other issues revolve around selecting too many items at once (remember to use the Lucene index). And a few issues revovle around the security settings (Is Siteore security slowing you down?).
However, in certain situations, the SQL server indexes may decrease in performance. This is usually due to index fragmentation which is why you need to rebuild them.
This script will rebuild all indexes with a fill factor of 80 (Fillfactor determines the percentage of the space on each leaf-level page are filled with data):
DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@sql) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor GO
You can find the original script here:
- Rebuilding indexes manually
- Setting Database Properties to Improve Performance by Dan Brown
- Index Fragmentation also by Dan Brown
- How to use the SQL Server Performance Monitor to measure performance
Thanks to Michael Sundstrøm for the tip.