Performance Tuning Re-indexing and Update Statistics – A Case Study
By satnam singh
Recently we started experiencing a very strange issue in our production reporting environment where the Re-indexing and Update Statistics operation suddenly began taking more than 2 days to complete and was thus causing blockage in the database which in turn caused impairment in application performance.
Reporting Server Configuration Details:
SQL Server Version: SQL Server 2005 Enterprise Edition
Service Pack: 3
Windows Version: Windows Server 2003 R2 Enterprise Edition
Number of CPU’s: 12
RAM: 16 GB
To troubleshoot this issue, I first separated the Re-indexing and Update Statistics operations.
Originally the Re-indexing operation was performed using the DBCC DBReindex command. When this command was executed against the database, it drops and re-creates every index present in the tables of the particular database which will take a considerable amount of time as well as cause heavy usage of the Server Hardware resources.
As per best practice recommended by Microsoft there is no need for us to perform the re-indexing of each and every index in the tables of the database. If the fragmentation level of a particular index is less than 30% then we can simply re-organize it, if it is greater than 30% then we should elect for the re-building of the index.
First we need to understand the difference between the re-build and re-organize operations. Index rebuild will drop the existing index and re-create it again whereas index re-organize will physically re-organize the leaf node of the index. One thing to remember here is that the rebuildng operation is a very high resource utilization task.
Based upon this, I decided to modify the Re-indexing task as follows: