Optimizing T-SQL queries that change data
Optimizing the performance of SQL Server INSERT, UPDATE, DELETE, and MERGE statements. Execution plan analysis and undocumented trace flags. Narrow (per-row) and wide (per-index) plans.
Articles
Page 8 of 11
— showing articles
127 to
144
of 182
MERGE Bug with Filtered Indexes
A SQL Server bug with unique filtered indexes. Covers narrow update plans and the Split, Sort, Collapse operator combination.
Cardinality Estimation Bug with Lookups
Incorrect SQL Server estimated row counts on Key and RID Lookup operators where a Filter is applied.
Why Doesn’t Partition Elimination Work?
SQL Server can fail to eliminate partitions from a query due to implicit type conversion.
Compute Scalars, Expressions and Execution Plan Performance
How the SQL Server Compute Scalar operator works internally.
Deletes that Split Pages and Forwarded Ghosts
Slow SQL Server DELETE peformance caused by unexpected page splitting or forwarded ghost records.
Temporary Table Caching Explained
Internal details of SQL Server temporary table and table variable caching.
Temporary Table Caching in Stored Procedures
How SQL Server temporary table caching can affect statistics, execution plans, and recompilations in unexpected ways.
Parallel Execution Plans Suck
Why TOP may not work well with SQL Server parallel queries.
Query Optimizer Deep Dive—Part 4
Internal operation of the SQL Server query optimizer. Writing queries that will take advantage of the optimizer's relational reasoning.
Query Optimizer Deep Dive—Part 3
SQL Server query optimizer memo structure, search stages, and final plan selection.
Query Optimizer Deep Dive—Part 2
Cost-based optimization in the SQL Server query optimizer, the input tree, properties, and rules.
Query Optimizer Deep Dive—Part 1
The SQL Server query optimization pipeline, early stages of compilation, and cardinality estimation. Includes trivial plans.
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.