Database Reference
In-Depth Information
Figure 25-13 shows the corresponding trace output captured.
Figure 25-13. The Extended Events output showing the effect of optimizing the costliest query on the complete workload
From this trace, Table 25-5 summarizes the resource use and the response time (in other words, Duration ) of the
query under consideration.
Table 25-5. Resource Usage and Response Time of the Optimized Query Before and After Optimization
Column
Before Optimization
After Optimization
Reads
1901
289
Writes
0
0
CPU
16 ms
0 ms
Duration
1313 ms
19.4 ms
the absolute values are less important than the relative difference between the before optimization and
the corresponding after optimization values. the relative differences between the values indicate the improvement in
performance.
Note
It's possible that the optimization of the worst-performing query may hurt the performance of some other
query in the workload. However, as long as the overall performance of the workload is improved, you can retain the
optimizations performed on the query. In this case, the other queries were not impacted. But now, there is a query
that takes longer than the others. It too might need optimization, and the whole process starts again.
Iterating Through Optimization Phases
An important point to remember is that you need to iterate through the optimization steps multiple times. In each
iteration, you can identify one or more poorly performing queries and optimize the query or queries to improve the
performance of the overall workload. You must continue iterating through the optimization steps until you achieve
adequate performance or meet your service-level agreement (SLA).
Besides analyzing the workload for resource-intensive queries, you must also analyze the workload for error
conditions. For example, if you try to insert duplicate rows into a table with a column protected by the unique
constraint, SQL Server will reject the new rows and report an error condition to the application. Although the data was
not entered into the table and no useful work was performed, valuable resources were used to determine that the data
was invalid and must be rejected.
 
 
Search WWH ::




Custom Search