Databases Reference
In-Depth Information
❑
Hovering the mouse over any of the arrows shows a pop-up information box showing how
many records are affected.
❑
The arrows that connect steps have different thicknesses. The thickness indicates the relative cost
in the number and size of rows of the data moving between each step. Thicker arrows indicate
more relative cost. This indicator is a quick gauge as to what is happening within the query plan.
Thicker lines on the left side than the right can be an indicator of trouble. Either too many rows
are being returned, or the query plan may not be optimal.
Typically, you may need to improve a stored procedure, which has multiple statements. Each statement
will have a cost relative to the total. As you can imagine, a small group of SQL statements can generate
an enormous execution plan. Therefore, you need to narrow down your analysis and focus on the most
offensive portions of the batch of SQL statements. Therefore, start with the statement that has the highest
relative cost. Next, each step in that plan has a cost associated with it. Again, find the step with the highest
cost. You now have a very specific and narrow area to concentrate on.
Tables 9-2 through 9-6 show excerpts from Books Online (BOL). They show all the operators you might
see in a query plan. Also listed are detailed information items that the operators may contain.
Table 9-2: Query Plan Operators
Operator
Operator
Icon
Icon
Arithmetic Expression (SQL
Server 2000 Only)
Clustered Index Seek
Assert
Clustered Index Update
Bitmap
Collapse
Bookmark Lookup
Compute Scalar
Clustered Index Delete
Concatenation
Clustered Index Insert
Constant Scan
Clustered Index Scan
Delete (Database Engine)
Continued
Search WWH ::
Custom Search