Sorting, Row Goals, and the TOP (100) Problem
The Top N Sort operator uses different implementations depending on the number of rows requested. For up to 100 rows it uses replacement selection and will never spill to tempdb.
Articles
Page 10 of 10
— showing articles
163 to
177
of 177
Row Goals and Grouping
Why SQL Server will choose a Hash Match Flow Distinct operator when a row goal is present. Other options for grouping, and when they are chosen.
Inside the Optimizer: Row Goals In Depth
SQL Server does not always optimize to return the full result set. Various query features can introduce a "row goal", which aims to produce the first few rows quickly.
Viewing Another Session’s Temporary Table
Reading the contents of a local temporary table created in a different SQL Server session.
The Impact of Non‑Updating Updates
SQL Server optimizations for UPDATE statements that do not change data values. Covers logging, dirty pages, and avoiding the update completely.
Iterators, Query Plans, and Why They Run Backwards
How SQL Server row mode execution plans execute in detail.
Another Interesting MERGE Bug
A SQL Server bug with MERGE statements on table variables with an OUTPUT clause.
Inside the Optimizer: Constructing a Plan—Part 4
Exploring the internal operation of the SQL Server cost-based optimizer with DBCC RULEON and DBCC RULEOFF.
Inside the Optimizer: Constructing a Plan—Part 3
Using an undocumented DMV to explore the rules used by the SQL Server query optimizer to generate a good execution plan.
Inside the Optimizer: Constructing a Plan—Part 2
How the SQL Server query optimizer matches and applies internal rules to refine an execution plan.
Inside the Optimizer: Constructing a Plan—Part 1
The rules the SQL Server cost-based query optimizer uses to build an execution plan.
Ranking Function Optimizer Transformations
SQL Server execution plans for window functions including the Segment Spool plan shape. How you can get a window function plan without writing the OVER clause.
Partitioning and the Common Subexpression Spool
The SQL Server common subexpression spool plan shape, and how it is used to implement queries using the OVER clause.
The Segment and Sequence Project Iterators
How the SQL Server Segment and Sequence Project operators are used to implement window functions like ROW_NUMBER, RANK, and DENSE_RANK.
The “Segment Top” Query Optimization
Why you will sometimes see a Top operator directly above a Segment operator in SQL Server execution plans.