Expensive queries by query_hash are ranked by total cost, not by single plans. A demo runs one lookup with 40 different literals and shows that the top single plan is a scan, while the grouped total, ranked by reads, points at the lookup. The query ranks by reads, CPU, time or runs.
Case-Sensitive WHERE Clause: COLLATE Without Losing the Seek
A case-sensitive WHERE clause needs a COLLATE on the comparison, but the obvious version scans the index. A demo measures 286 reads against 2, and shows two fixes: keep the plain test beside it, or give the column a case-sensitive collation.
SQL SERVER – Data and Index Compression – Real-World Conversation
Index Compression needs workload measurements of storage, I/O and CPU. A customer conversation prompted my earlier advice.
Wait Stats for Performance – SQL in Sixty Seconds #157
I finally decided to create a video on that topic on my SQL in Sixty Seconds Channel. I will create more videos on the topic Wait Stats for Performance.
Waits Since Restart: A Wait Stats Query With Uptime Rates
Waits since restart show where a SQL Server spends its waiting time. One query ranks the real waits, removes the idle ones, and adds the average wait, the signal share and a rate per hour of uptime. A table maps the common waits to the area to check next.
SQL SERVER – Find Untrusted Foreign Key
An Untrusted Foreign key has not validated all existing rows. A customer bulk-load history prompted this inventory.
Index Build Speed: Which Rebuild Settings Change the Time
Index build speed depends on rebuild or reorganize, MAXDOP, ONLINE, SORT_IN_TEMPDB and the recovery model. This post times each choice on a million row table and measures the log under each recovery model. It shows which one changes the clock and which changes only the log.