Avoiding Uniqueness for Performance
SQL Server seeks using a unique index can be much slower than the same index when not marked unique.
SQL Server seeks using a unique index can be much slower than the same index when not marked unique.
SQL Server can perform an equality seek much more quickly when the index used for seeking is marked as UNIQUE
.
The data types, number, and nullability of joined columns can dramatically affect SQL Server row mode hash join performance.
The SQL Server bitmap operator enables early semi join reduction for hash and merge joins.
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.
A compact way to write null-aware comparisons in SQL Server using EXISTS
or INTERSECT
.
The internals of parallel row mode query execution in SQL Server.
A SQL Server bug that can cause slow SUM
and AVG
calculations.
Query tuning considerations that are often overlooked. How deep internals knowledge can help you write better T-SQL.
LOB logical reads can be reported on a table with no LOB columns. This is caused by row overflow data (SLOBs).
Singleton seeks, range scan seeks, multiple seeks in one, and a seek on a heap with no indexes
Singleton seeks and seeks with range scans. Multiple seeking operations in a single SQL Server execution plan operator.
SQL Server can perform multiple index seeking operations in a single Index Seek operator.
A side effect of the Split, Sort, and Collapse operators used to avoid false transient unique key violations.
How a nested loops join with Eager Index Spools can be faster than a hash join.
SQL Server does not cache a separate serial version of a parallel plan.
SQL Server contains an optimization that means row locks under read committed isolation can sometimes be safely skipped.
How to properly implement a custom Sequence Table facility, safe under high concurrency, and able to pre-allocate a range of values.