Archive for the ‘ performance tuning ’ Category

A More Effective Selective Index Rebuild/Reorganize Strategy

This is a follow up post to : Why SQL Fragmentation Remains High

As mentioned in this previous post, our dilemma was we were rebuilding every index on a nightly basis, and :

  1. the process proves to be very resource intensive
  2. we don’t see the benefit. The fragmentation for some of the indexes remain high.

After some digging, this is what we found:

Rebuild the index only if there will be at least 1000 pages affected. If < 1000 pages will be affected, fragmentation will not really be affected and potentially would remain high.

This is the script that we now use to selectively identify which indexes to rebuild, which indexes to reorganize, and which indexes to leave alone. This script uses the following criteria in determining which action to take:

  • current fragmentation %
  • number of pages used by the index

The current logic is:

  • reorganize index : if fragmentation is > 10 % but < 30% and number of pages > 1000
  • rebuild index : if fragmentation is > 30 and number of pages > 1000

Read the rest of this entry »

VN:F [1.9.22_1171]
Rating: 8.9/10 (25 votes cast)
VN:F [1.9.22_1171]
Rating: +3 (from 3 votes)

Why Index Fragmentation Remains High

In one of companies I worked for, we used to do nightly index rebuilds. What was baffling is even after the nightly rebuilds, the fragmentation for some of the tables remain high.

I did some digging; here’s an explanation on why some index fragmentations remain high.

Both Paul Randal and Kalen Delaney (highly respected, very reputable SQL Server developers/trainers) suggest to rebuild the index only if there’s at least 1000 pages affected. If < 1000 pages, they say fragmentation will not really be affected and potentially would remain high.

I checked the "questionable" tables in our databases, and yes, Paul and Kalen were right on the ball. These pages had <1000 pages each.

  • tableA has 9 pages and initial fragmentation of 875. After rebuilding, fragmentation flip flops between 66% and 77%, and never improves beyond 66%
  • tableB has almost 95000 pages and initial fragmentation of 69%. After rebuilding indexes, fragmentation drops to 0.01%. Succeeding rebuilds keep fragmentation to 0.01%

This makes sense, because for smaller tables an index will not really help – SQL Server will usually prefer to do a table scan.

I will post the script that I used to determine and apply selective index rebuilds.

 

Follow Up: Script is posted in

A More Effective Selective Index Rebuild/Reorganize Strategy

VN:F [1.9.22_1171]
Rating: 9.8/10 (8 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`