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 @fillfactor INT
SET @fillfactor = 80
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
CLOSE TableCursor

You can find the original script here:

Other resources:

Thanks to Michael Sundstrøm for the tip.

About briancaos

Developer at Pentia A/S since 2003. Have developed Web Applications using Sitecore Since Sitecore 4.1.
This entry was posted in Sitecore 6 and tagged , , , , . Bookmark the permalink.

1 Response to Sitecore poor database performance

  1. Pingback: Measuring Sitecore performance with HighResTimer | Brian Pedersen's Sitecore and .NET Blog

Leave a Reply

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

You are commenting using your 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.