SQL Server 6.5 Performance Optimization and Tuning Handbook
An explanation of fundamental performance tuning concepts for SQL Server targeted to intermediate level database developers and administrators.
TABLE OF CONTENTS SQL Server 6.5 Performance Optimization and Tuning Handbook
Section 4.2.4: Tools for Investigating Query Strategy
4.2.4 Tools for Investigating Query Strategy
We have now discussed the steps that the query optimizer performs during query optimization, namely:
-
Query Analysis
-
Index Selection
-
Join Order Selection
To facilitate performance, tuning and optimization it is essential that we are able to see the decisions that the query optimizer has made so that we can compare the decisions with what we expect. We also need to be able to measure the work done in executing the query so we can compare the effectiveness of different indexes.
| Note | You should always calculate a rough estimate of the logical I/Os a query should use. If the logical I/Os used differs by a large amount it could be that your estimate is very inaccurate or more likely the query plan is not what you expected! |
There are a number of tools at our disposal for checking what the query optimizer is doing. There are various options we can set in ISQL/w and there are also a number of trace flags that can be used.
4.2.4.1 SET SHOWPLAN
In ISQL/w we can request the display of the query optimizer strategy for a query with the SET SHOWPLAN ON statement:
SET SHOWPLAN ON SELECT * FROM branches STEP 1 The type of query is SETON STEP 1 The type of query is SELECT FROM TABLE branches Nested iteration Table scan...
Copyright Butterworth-Heinemann 1997 under license agreement with Books24x7
