Database Reference
In-Depth Information
Figure 25-6.
The index fragmentation of the various tables after rebuilding indexes
As you can see in Figure
25-6
, the fragmentation was not reduced at all in any of the indexes in the tables used
by the poorest-performing query. In most cases, this is because the number of pages is so small that defragmentation
just isn't possible. In general, I wouldn't even bother defragmenting an index with fewer than 100 pages. The
recommendation from Microsoft is to wait until 1,000 pages before defragmenting.
Once you've analyzed the external factors that can affect the performance of a query and resolved the nonoptimal
ones, you should analyze internal factors, such as improper indexing and query design.
Analyzing the Internal Behavior of the Costliest Query
Now that the statistics are up-to-date, you can analyze the processing strategy for the query chosen by the optimizer to
determine the internal factors affecting the query's performance. Analyzing the internal factors that can affect query
performance involves these steps:
•
Analyzing the query execution plan
•
Identifying the costly steps in the execution plan
•
Analyzing the effectiveness of the processing strategy
