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!!!