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:

Thanks to Michael Sundstrøm for the tip.

Advertisements

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.

One 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:

WordPress.com Logo

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

Google+ photo

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

Connecting to %s