Mark Mintoff My superpower is common sense

20Oct/140

Checking and Fixing Index Fragmentation and Statistics in SQL Server

In recent experience, a query which took an hour to execute, executed for three consecutive days with no end in sight. The query had to be terminated early.

What caused this was a high index fragmentation and incorrect table statistics, which lead to an invalid execution plan to be created.

The following script checks and reports on the index fragmentation of all tables within a database:

 Microsoft recommends the following corrective statements:

  • For index fragmentation 5-30%; ALTER INDEX REORGANIZE
  • For index fragmentation greater than 30%; ALTER INDEX REBUILD

http://msdn.microsoft.com/en-us/library/ms189858.aspx

To that end, I created the following script to automatically Reorganize or Rebuild fragmented indices and update the statistics on the table:

 After running the above, the same query that was stuck running for three days, completed in just sixteen minutes; an improvement even on the original execution time!

VN:F [1.9.22_1171]
Rating: 5.0/5 (1 vote cast)
Checking and Fixing Index Fragmentation and Statistics in SQL Server, 5.0 out of 5 based on 1 rating
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

No trackbacks yet.