Page Free Space

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 with a filter.

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.

LOB logical reads without LOB columns

LOB logical reads can be reported on a table with no LOB columns. This is caused by row overflow data (SLOBs).

Seeking Without Indexes

Singleton seeks, range scan seeks, multiple seeks in one, and a seek on a heap with no indexes

Is it a Seek or a Scan?

Singleton seeks and seeks with range scans. Multiple seeking operations in a single SQL Server execution plan operator.

When is a Seek not a Seek?

SQL Server can perform multiple index seeking operations in a single Index Seek operator.

Beware Sneaky Reads with Unique Indexes

A side effect of the Split, Sort, and Collapse operators used to avoid false transient unique key violations.

Heaps of Trouble?

How a nested loops join with Eager Index Spools can be faster than a hash join.