Sitecore poor database performance

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:

http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

Other resources:

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