SQL Server Query Execution Plan Analysis Part4/4

Sometimes, the Query Optimizer will need to create a temporary worktable in the tempdb database. If this is the case, it will be indicated in the graphical query execution plan with an icon labeled like this: Index Spool, Row Count Spool, or Table Spool.

Anytime a worktable is used, performance is generally hurt because of the extra I/O required to maintain the worktable. Ideally, there should be no worktables. Unfortunately, they cannot always be avoided. And sometimes their use can actually boost performance because using a worktable is more efficient that alternatives.

In any event, the use of a worktable in a graphical query execution plan should raise an alert with you. Take a careful look at such a query and see if there is anyway it can be rewritten to avoid the worktable. There may not be. But if there is, you are one step closer to boosting the performance of the query. [7.0, 2000, 2005] Updated 7-10-2006

*****

In a graphical query execution plan, often you see the Stream Aggregate icon. This just means that some sort of aggregation into a single input is being performed. This is most commonly seen when a DISTINCT clause is used, or any aggregation operator, such as AVG, COUNT, MAX, MIN, or SUM. [7.0, 2000, 2005] Updated 7-10-2006

*****

Query Analyzer and Management Studio are not the only tools that can generate and display query execution plans for queries. The SQL Server Profiler can also display them, albeit in text format only. One of the advantages of using Profiler instead of Query Analyzer or Management Studio to display execution plans is that it can do so for a great many queries from your actual production work, instead of running one at a time.

To capture and display query execution plans using Profiler, you must create a trace using the following configuration:

Events to Capture

Performance: Execution Plan
Performance: Show Plan All
Performance: Show Plan Statistics
Performance: Show Plan Text
Data Columns to Display

StartTime
Duration
TextData
CPU
Reads
Writes
Filters

Duration. You will want to specify a maximum duration, such as 5 seconds, so that you don't get flooded with too much data.
Of course, you can capture more information than is listed above in your trace; the above is only a guideline. But keep in mind that you don't want to capture too much data, as this could have a negative affect on your server's performance as the trace is being run. [7.0, 2000, 2005] Updated 7-10-2006

*****

If you use the OPTION FAST hint in a query, be aware that the Execution Plan results may not be what you expect. The Execution Plan that you get is based on the results of using the FAST hint, not the actual Execution Plan for the full query.

The FAST hint is used to tell the Query Optimizer to return the specified number of rows as fast as possible, even if this hurts the overall performance of the query. The purpose of this hint is to return a specified number of records quickly in order to produce an illusion of speed for the user. Once the specified number of rows is returned, the remaining rows are retuned as they would be normally.

So if you are using the FAST hint, the execution plan will be for only those rows that are returned FAST, not for all of the rows. If you want to see the execution plan for all the rows, then you must perform an Execution Plan of the query with the hint removed.