Database Reference
In-Depth Information
You should also note the warning indicator on the
SELECT
operator. Looking at the properties for this operator,
you can see that
SalesOrderID
is actually getting converted as part of the process and the optimizer is warning you.
Type conversion in expression (CONVERT(nvarchar(23),[soh].[SalesOrderID],0)) may affect
"CardinalityEstimate" in query plan choice
I left this example in, with the warning, to illustrate a couple of points. First, warnings can be unclear. In this case,
the warning is coming from the calculated column,
SalesOrderNumber
. It's doing a conversion of the
SalesOrderID
to a string and adding a value to it. In the way it does it, the optimizer recognizes that this could be problematic, so it
gives you a warning. But, you're not referencing the column in any kind of filtering fashion such as the
WHERE
clause,
JOINS
, or
HAVING
. Because of that, you can safely ignore the warning. I also left it in because it illustrates just fine that
AdventureWorks
is a good example database because it has the same types of poor choices that are sometimes in
databases in the real world too.
For most Data Definition Language (DDL) statements (such as
CREATE TABLE
,
CREATE PROC
, and so on), after
passing through the algebrizer, the query is compiled directly for execution, since the optimizer need not choose
among multiple processing strategies. For one DDL statement in particular,
CREATE INDEX
, the optimizer can
determine an efficient processing strategy based on other existing indexes on the table, as explained in Chapter 8.
For this reason, you will never see any reference to
CREATE TABLE
in an execution plan, although you will see
reference to
CREATE INDEX
. If the normalized query is a Data Manipulation Language (DML) statement (such as
SELECT
,
INSERT
,
UPDATE
, or
DELETE
), then the query processor tree is passed to the optimizer to decide the processing
strategy for the query.
Optimization
Based on the complexity of a query, including the number of tables referred to and the indexes available, there may
be several ways to execute the query contained in the query processor tree. Exhaustively comparing the cost of all
the ways of executing a query can take a considerable amount of time, which may sometimes override the benefit
of finding the most optimized query. Figure
14-4
shows that to avoid a high optimization overhead compared to the
actual execution cost of the query, the optimizer adopts different techniques, namely, the following:
•
Simplification
•
Trivial plan match
•
Multiple optimization phases
•
Parallel plan optimization
