Prove partition elimination with the actual plan, test date predicates, and use $PARTITION to check which slice SQL Server targets.
SQL SERVER – Compatibility Level 80 and Table Hint Behavior
A health check led me to a database on compatibility level 80, sudden deadlocks and a surprise in table hint behavior. Here is what I found.
SQL SERVER – When to Turn On Optimize for Ad Hoc Workloads?
A true story from a recent health check, with two instances on one server, shows when you should turn on Optimize for Ad Hoc Workloads.
How Readable Secondary Replicas Build Their Own Statistics
Learn why readable secondary replicas create temporary statistics in tempdb, how to find them, and when the primary needs a permanent statistic.
Querying a Histogram With sys.dm_db_stats_histogram Instead of DBCC
Query sys.dm_db_stats_histogram to inspect statistics steps, compare stored estimates with data, and identify values beyond the last step.
SQL SERVER – Wait Stats Collection Scripts for 2016 and Later Versions
Are my Wait Stats Collection Scripts still valid for SQL Server 2016 and later? Yes, they work on every version. Here they are, with a few recent enhancements.
SQL SERVER – Get Wait Stats Related to Specific Session ID With sys.dm_exec_session_wait_stats
From SQL Server 2016, sys.dm_exec_session_wait_stats shows wait stats for a specific session ID. Here is how to use it, and why it fails on older versions.