CHAPTER 28 ■ PERFORMANCE TUNING
Because there are two queries, you’ll also see two actual execution plans (See Figure 28-10).
Figure 28-10. Actual execution plan of sp_help
The results of this stored procedure show two different execution plan panes within the stored procedure, with each pane showing the various operations used to generate the result sets. You can see in the header of the first query that the query cost is 93% (see Figure 28-11) versus the second query’s 7% cost (see Figure 28-12)—as a percentage of the whole stored procedure execution:
Figure 28-11. Cost relative to Batch Query 1
Figure 28-12. Cost relative to Batch Query 2
So, if your job was to troubleshoot the performance of a stored procedure, your first step would be to identify the batch in the procedure with the highest cost. After identifying the highest cost batch, you should then focus in on the highest cost operations within that batch. For example, within the sp_help system-stored procedure, Query 1 consumed 93% of the total cost. Looking within Query 1’s operators, you can then look for the higher percentage operations. For example in Figure 28-13, you can see that the Hash Match Inner Join operator has a cost of 36%, while the two Clustered Index Scan operations take 16% for one operation and 26% for another.