Find the tipping point where key lookups cost more than a scan, then compare covering and filtered indexes with measured reads.
How to Force a Parallel Execution Plan for a Query? – Interview Question of the Week #170
A customer asked how to force a Parallel Execution Plan for a query. Here are two methods, including QUERYTRACEON 8649, with a simple example.
Faster Backups With BUFFERCOUNT and MAXTRANSFERSIZE, Measured
Measure faster backups by changing BUFFERCOUNT, MAXTRANSFERSIZE, and backup stripes one at a time while tracking speed and memory.
Which is Better for Performance – SELECT or SET? – Interview Question of the Week #166
Which is Better for Performance, SELECT or SET? In a loop, one SELECT that assigns several variables beats separate SET statements. Here is why.
Automatic Plan Correction: Reading SQL Server’s Tuning Advice
Read automatic plan correction advice, compare regressed and recommended plans, and verify that forcing the last good plan held.
How to Drop All the User Created Statistics by SQL Server? – Interview Question of the Week #158
SQL Server creates statistics on its own, but DBAs and the Tuning Advisor add more. Here is a script to drop all user Created Statistics safely.
Partition Elimination: Proving a Query Reads Only What It Needs
Prove partition elimination with the actual plan, test date predicates, and use $PARTITION to check which slice SQL Server targets.