Database Reference
In-Depth Information
Summary
The Query Life Cycle consists of four different stages: parsing, binding, optimization, and execution. A query is
transformed numerous times using tree-like structures, starting with a logical query tree at the parsing stage and
finishing with the execution plan after optimization.
Query Optimization is done in several phases. With the exception of trivial plans search, SQL Server uses a
cost-based model, evaluating the cost of access methods, resource usage, and a few other factors.
The quality of execution plans greatly depends on the correctness of input data. Accurate and up-to-date
statistics are a key factor that improves cardinality estimations and allows SQL Server to generate efficient execution
plans. However, as with any model, there are limitations. In some cases, you need to refactor, split, and simplify
queries to overcome such restrictions.
An execution plan consists of physical operators, which with exception of
Parallelism
, use a poll-based,
row-based model. Each parent operator requests data from its children on a row-by-row basis. Starting with SQL
Server 2012, there is another batch-mode execution model available, which is used with Columnstore indexes and
some Data Warehouse queries.
There are two types of operators: blocking and non-blocking. Non-blocking operators serve rows back to parents
as soon as they get them. Blocking operators acquire and cache all rows from children before returning rows to
parents.
Blocking operators require memory to store data. In cases when the memory estimation is incorrect, data is
spilled to
tempdb
. Such spills reduce the performance of queries and can be monitored with Sort and Hash Warning
SQL trace and Extended Events.
The two most common cases of incorrect memory grant sizes are incorrect cardinality and row-size estimates.
You can improve these by keeping statistics up to date and defining variable-length data columns about twice as big
as the actual data size stored there.
You can control some aspects of Query Optimization by using query and table hints. However, you should be very
careful when using them, documenting and periodically re-evaluating their usage. This helps to avoid subefficient
execution plans due to data size or distribution changes, which invalidate the correctness of hints usage.
