"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

August 12, 2012

SQL Server - Index Tuning Basics

SQL Server Query Tuning Session. - One more session to add to SQL Tuning list.


Useful pointers from the session. Couple of pointers are covered in previous posts as well.

Optimizer's choice of Join
Nested Loop Join
  • Outer input is small
  • Inner input has an index on the join key
Merge Join
  • Medium to large inputs
  • Sorted inputs and equality operator required
Hash Join
  • Large inputs
  • Requires equality operator, inputs need not be sorted

MAXDOP Option - 1 (Ensure single processor for execution)
Density - How many distinct values available in a particular column
Multi Column Index
  • Index can be used to seek on second column if there is an equality operator on first column
Guildeline - Most Selective column should be first column where all other column predicates use the equality operator
Happy Learning!!!

No comments: