Database Reference
In-Depth Information
Query Optimizer expects all queries to start with cold cache and perform physical I/O when
accessing the data. This may be incorrect in production systems when data pages are often
cached in the buffer pool. In some rare cases, it could lead to a situation where SQL Server
chooses a less efficient plan that requires less I/O at the cost of higher CPU or memory usage.
Query Optimizer assumes that sequential I/O performance is significantly faster than
random I/O performance. While this is usually true for magnetic hard drives, it is not
exactly the case with solid-state media, where random I/O performance is much closer to
sequential I/O, as compared to magnetic hard drives. SQL Server does not take drive type
into account, and overcosts random I/O operations in the case of solid-state based disk
arrays. It can generate execution plans with a
Clustered Index Scan
instead of
Nonclustered
Index Seek
and
Key Lookup
, which could be less efficient with SSD-based disk subsystems
for some of the queries. It is also worth noting that the same could happen with modern
high-performance disk arrays with a large number of drives and very good random I/O
performance.
With all that being said, the costing model in SQL Server
generally
produces correct and consistent results.
However, as with any mathematical model, the quality of the output highly depends on the quality of the input data.
For example, it is impossible to provide correct cost estimations when the cardinality estimations are incorrect due to
outdated statistics. Keeping statistics up to date helps SQL Server generate efficient execution plans.
Query Execution
SQL Server generates an execution plan in the final stage of query optimization. The execution plan is passed to the
Query Executor
, which, as you can guess by its name, executes the query.
The execution plan is a tree-like structure that includes a set of
operators
, sometimes called
iterators
. Typically,
SQL Server uses a
row-based
execution model, where each operator generates a single row by requesting the rows
from one or more children and passes the generated row to its parent.
■
SQL Server 2012 introduced a new “batch-mode” execution model, which is used with some data Warehouse
queries. We will talk about this execution model in Chapter 34, “introduction to ColumnStore indexes.”
Note
Let's look at an example that illustrates a row-based execution model, assuming that you have the query shown
in Listing 25-3.
Listing 25-3.
Row-based execution: Sample query
select top 10 c.CustomerId, c.Name, a.Street, a.City, a.State, a.ZipCode
from
dbo.Customers c join dbo.Addresses a on
c.PrimaryAddressId = a.AddressId
order by
c.Name
This query would produce the execution plan shown in Figure
25-6
. SQL Server selects all of the data from the
Customers
table, sorts it based on the
Name
column, getting the first 10 rows, joins it with the
Addresses
data, and
returns it to the client.
