Query Optimizer Deep Dive—Part 1
The SQL Server query optimization pipeline, early stages of compilation, and cardinality estimation. Includes trivial plans.
Articles
Page 9 of 11
— showing articles
145 to
162
of 188
Fun with Scalar and Vector Aggregates
The important differences between SQL Server aggregates with a GROUP BY clause (vector) and those without (scalar aggregates).
Dynamic Seeks and Hidden Implicit Conversions
Dynamic seek plans driven by Constant Scan and Merge Interval operators.
Forcing a Parallel Query Execution Plan
How to force SQL Server to generate a parallel execution plan, including some of the reasons the server sometimes chooses a serial plan.
SQL Server Optimizer Bug with JOIN and GROUP BY
A SQL Server bug in cardinality estimation with serial partial aggregates. Covers how SQL Server uses local and partial aggregates.
Is Distinct Aggregation Still Considered Harmful?
Query rewrites to efficiently perform a DISTINCT aggregate using other aggregates. The SQL Server 2012 optimizer transformation that performs the rewrite for you.
Finding the Statistics Used to Compile an Execution Plan
Finding the statistics used to compile a SQL Server query using undocumented trace flags.
Can a SELECT query cause page splits?
Page splits caused by populating an Index Spool. EXECSYNC waits when loading a parallel spool.
SQL Server, Seeks, and Binary Search
SQL Server b-tree seeks can use binary search or linear interpolation (with linear regression). The latter can be more efficient, except when key values are separated by a small fixed gap.
Avoiding Uniqueness for Performance
SQL Server seeks using a unique index can be much slower than the same index when not marked unique.
Enforcing Uniqueness for Performance
SQL Server can perform an equality seek much more quickly when the index used for seeking is marked as UNIQUE.
Join Performance, Implicit Conversions, and Residuals
The data types, number, and nullability of joined columns can dramatically affect SQL Server row mode hash join performance.
Bitmap Magic
The SQL Server bitmap operator enables early semi join reduction for hash and merge joins.
Undocumented Query Plans: The ANY Aggregate
SQL Server does not allow us to write an ANY aggregate directly, but there is a way to get the optimizer to produce one using
Undocumented Query Plans: Equality Comparisons
A compact way to write null-aware comparisons in SQL Server using EXISTS or INTERSECT.
How Parallelism Works in SQL Server
The internals of parallel row mode query execution in SQL Server.
Slow T-SQL Sums and Averages
A SQL Server bug that can cause slow SUM and AVG calculations.
Why Internals Knowledge Matters
Query tuning considerations that are often overlooked. How deep internals knowledge can help you write better T-SQL.