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 – Observation – Effect of Clustered Index over Nonclustered Index
An observation from my query optimization work: the effect of clustered index on a nonclustered index, shown with a sample table and execution plans.
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.
SQL SERVER – Difference Between Index Scan and Index Seek
What is the difference between Index Scan and Index Seek? I explain how SQL Server uses search predicates to decide which index operation a query gets.
Index Usage Snapshots: Reject Invalid Counter Deltas
Index usage snapshots require a valid counter window. Keep missing rows and lifecycle gaps visible. The guarded example rejects invalid pairs before calculating any activity delta.