Databases Reference
In-Depth Information
Figure 10-19
Figure 10-19 shows that the time from the new replay is 21 minutes, 49 seconds, and 793 ms (1,309 sec-
onds). Comparing this replay time with the base reference replay time (1,589 seconds, see Figure 10-17),
it is a 17.6 percent improvement for the whole workload. The percentage improvement is calculated as
follows: (1
−
1309/1589)
×
100
=
17.6%.
Now, let's calculate what the improvement was for the BROWSE_BY_CATEGORY stored procedure after
adding the new index suggested by DTA.
Before adding the index, from Figure 10-17 we see that the replay time for the whole workload is 26
minutes, 29 seconds, and 650 ms (1,589 seconds). From Table 10-1 we see that the stored procedure
BROWSE_BY_CATEGORY is executed 15,448 times; and the average duration to execute the procedure
is 30 milliseconds. The total cumulative time spent for the procedure is 463 seconds (15,448 occurrences
multiplied by 30 milliseconds equals to 463 seconds). The overhead of the rest of the procedure executions
is 1,126 seconds (1,589 seconds minus 463 seconds equals to 1,126 seconds).
After adding the index, from Figure 10-19 we see that the total replay time is 1,309 seconds. Assuming
the overhead of all other procedure executions remain the same (1,126 seconds), the new overhead of the
procedure BROWSE_BY_CATEGORY is 183 seconds (1,309 minus 1,126 equals to 183). Now we can cal-
culate the average execution time for the stored procedure call after adding the new index. This average
execution time is 11.8 seconds (183 seconds divided by 15,448 occurrences equals to 11.8 milliseconds).
Finally, the percentage improvement in the execution time for the stored procedure is about 61 percent:
(1
−
×
=
11.8/30)
100
61%.
In this scenario, we demonstrated how to use Profiler replay to validate a performance estimate from
DTA. Based on a single stored procedure execution of BROWSE_BY_CATEGORY, DTA estimated a 98
percent improvement with the suggested index. Using Profiler replay you saw this new index improved
Search WWH ::
Custom Search