Prev Next |
Database Index Tuning
Summary
Database index tuning can increase the performance of high-volume Sitecore solutions to improve the user experience for both editors and the viewing public. This article applies primarily to Sitecore implementations using SQL Server 2005, though similar techniques could be applied on solutions using other supported RDBMS technologies. The specific techniques described in this resource have been shown to improve performance by up to 100 times for certain operations.
Instructions for Sitecore CMS 5.3.1
Note: this section applies to Sitecore versions through 5.3.1 070924. The upgrade process from 070924 and earlier does not apply these changes automatically (they should be applied manually after the upgrade).
Recommended Database Changes to Improve Performance
- For the SharedFields table in All Sitecore Databases:
- Change the ndxLookup index from Nonclustered to Clustered
- Configure Fill Factor for the ndxLookup index to between 50% and 75%
- Enable Pad Index for the ndxLookup index
- For the VersionedFields table in All Sitecore Databases:
- Create a clustered index named ndxItemIdon the column ItemId
- Configure Fill Factor for the ndxItemId index to between 50% and 75%
- Enable Pad Index for the ndxItemId
- For the UnversionedFields table in All Sitecore “Master” databases:
- Create a clustered index named ndxItemId on the column ItemId
- Configure Fill Factor for the ndxItemId index to between 50% and 75%
- Enable Pad Index for the ndxItemId index
Tuning Indexes in Management Studio
Updating the Type of an Existing Index (from Nonclustered to Clustered)
To update properties of an existing index take the following action;
- Expand Indexes under the table
- Double-click on the index
- Change the Index type property on the General tab.
Configuring the Fill Factor or enabling Pad Index for an Existing Index
To set the Fill Factor or enable Pad Index for an existing index take the following steps;
- Expand Indexes under the table
- Double-click the index
- Select the Options tab
- Select the Set fill factor checkbox and enter a percentage
- Select the Pad index checkbox if appropriate.
- Acknowledge the index properties
- Right-click on the index and choose Rebuild.
Creating a New Index
To create a new index take the following steps;
- Right-click Indexes under the table
- Choose New Index.
- On the General tab enter the Index name then select the Index type
- Press Add and select the column(s) to be indexed.
- If specifying a Fill factor or enabling Pad Index, select the Options tab, select the Set fill factor checkbox and enter a percentage.
- Select the Pad index checkbox if appropriate.
Index Maintenance
Sitecore recommends rebuilding clustered indexes daily using T-SQL using the following format for each index:
DBCC DBREINDEX('<tablename>','IndexName',<FillFactor>)
Where the first parameter specifies a database table (mandatory), the second parameter specifies which index of that table is to be rebuilt (a blank causes all indexes to be rebuilt) and the third parameter specifies the fill factor. For instance to rebuild all indexes for the VersionedFields table using a fill factor of 75 you would use the following string:
DBCC DBREINDEX('VersionedFields',' ',75)
Staging Considerations
Apply indexing as appropriate for all databases including those employed by both Sitecore CMS (Staging Master) and Content Delivery (Staging Slave) servers.
References
For more information on SQL Server indexing, Fill Factor and Pad Index, please see:
- http://www.quest-pipelines.com/newsletter-v4/1203_B.htm
- http://msdn2.microsoft.com/en-us/library/ms181671.html
Instructions for Sitecore CMS 6.x
Note that you do NOT need to apply instructions for Sitecore CMS 5.3 mentioned above to Sitecore CMS 6 and later solutions.
You should apply the instructions in the SQL Server Index Fragmentation Level and the Database Properties sections that are described in the OMS Performance Tuning Guide to all Sitecore content databases:
http://sdn.sitecore.net/Reference/Sitecore%206/OMS%20Performance%20Tuning%20Guide.html
Database Options
Database options at all Sitecore databases should be set as it is presented on the following screenshot (the important settings are marked with red):
Note: Set the compatibility level according to your SQL server: 100 for SQL Server 2008 and 90 for SQL Server 2005.
Note: You can set any other Recovery model values but this will dramatically increase the size of the active database.
Prev Next