Part 2 on force index query hints: instead of the inline WITH table hint, use the OPTION clause at the end of the query and name the table for each hint.
SQL SERVER – Introduction to Force Index Query Hints – Index Hint
An index hint lets you force SQL Server to use a specific index in a query. I show the WITH (INDEX()) syntax with examples, and why it is for special cases only.
SQL SERVER – Quickest Way to – Kill All Threads – Kill All User Session – Kill All Processes
The quickest way to kill all processes and user sessions in a database: SET SINGLE_USER WITH ROLLBACK IMMEDIATE, then back to MULTI_USER. No cursor needed.
SQL SERVER – FIX : ERROR : Msg 5834, Level 16, State 1, Line 1 The affinity mask specified conflicts with the IO affinity mask specified. Use the override option to force this configuration
Msg 5834 says the affinity mask conflicts with the IO affinity mask when you change fill factor with sp_configure. The fix is RECONFIGURE WITH OVERRIDE.
SQL SERVER – 2008 – 2005 – Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor
A quick script to rebuild every index of all tables in a database with a fillfactor of 80, plus a short note on what fillfactor does to leaf-level pages.
Operator Cost Percentages Are Estimates: Read Actual Time Instead
Read operator cost percentages as estimates, then use actual rows, elapsed time, IO, and repeated work to find the real bottleneck.
SQL SERVER – Shrinking NDF and MDF Files – A Safe Operation
Is shrinking NDF and MDF files safe? A reader asked if it causes data loss. It does not, but I explain why shrinking them is often not a good idea.