If the site is running slowly or timing out due to SQL timeouts being reported on the site, it may be an indication that an SQL database reindex is needed. Here are the steps necessary to perform this task.
Primary Note
If the hardware is that where the server is dedicated to SQL, large space, powerful CPUs with greater than 4 GB RAM, chances are maintenance has not been done to the database itself. It's easy to perform these maintenance tasks below.
Procedure
- In SQL Management Studio, open a new query for the database in question, and type DBCC checkdb. Let the query complete and check on the very end of the results for 0 allocation errors and 0 consistency errors. If you see any allocation or consistency errors, STOP! There is database damage that Microsoft will need to work with the client to resolve.
- In SQL Management Studio, open a new query for the database in question, and type EXEC cs_system_dbreindex.
Now try to access the site as normal, and you should see the performance increase greatly. We recommend to clients to create scheduled SQL jobs that perform these tasks at least once a week for good measure for the "rebuild and reindex" tasks, and daily for consistency checks.For more database maintenance information, see this article.