Today, we are going to see one more trick from the book – Remove Key Lookup and improve the performance of queries.
Splitting OR Conditions Into UNION ALL for Index Seeks
Test splitting OR conditions into disjoint UNION ALL branches, preserve NULL behavior and row identity, and compare actual index access.
Cached Plan Reuse: How to Tell if a Query Used a Cached Plan
Cached plan reuse is invisible in a result set. This post shows three checks on SQL Server: the compiled and runtime parameter values in the plan, the usecounts column of the plan cache, and the plan creation time.
TOP N Sort: Why a Small TOP Still Reads the Whole Table
Explain TOP N Sort, compare an indexed ordered access path, and align filter and sort keys before adding indexes for frequent requests.
Aggregate Pushdown: Letting the Columnstore Scan Do the Summing
Verify aggregate pushdown in columnstore scans, inspect locally aggregated rows, and compare numeric aggregates with expressions.
SQL SERVER – Stored Procedure Timing and Cached Measurements
SET STATISTICS TIME reports CPU and elapsed execution time for Stored Procedures. A customer wanted to measure a run without adding statements manually.
Query Progress: Watching a Running Plan With dm_exec_query_profiles
Elapsed time cannot tell you if a query is almost done. Live operator rows can. See how to read them and how far to trust the percentage.