Avoiding Uniqueness for Performance
SQL Server seeks using a unique index can be much slower than the same index when not marked unique.
Articles
Page 9 of 10
— showing articles
145 to
162
of 179
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.
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.
Myth: SQL Server Caches a Serial Plan with Every Parallel Plan
SQL Server does not cache a separate serial version of a parallel plan.
The Case of the Missing Shared Locks
SQL Server contains an optimization that means row locks under read committed isolation can sometimes be safely skipped.
Sequence Tables
How to properly implement a custom Sequence Table facility, safe under high concurrency, and able to pre-allocate a range of values.