Common Query Optimization Problems and Best Practices

1. Out-of-Date or Insufficient Statistics

--For SQL Server 2008+

2. Poor Index Design

·                 For composite indexes, keep the most selective columns leftmost in the index
·                 Index columns used in the joins
·                 Index the most critical queries and transactions
·                 Avoid indexes on columns with poor selectivity
·                 Choose the clustered and nonclustered indexes carefully
o   In most cases, use PK for the clustered index
o   Other cases, make the PK as unique nonclustered index and use other column(s) for the clustered index
o   Columns in the WHERE clause
o   Columns in the ORDER BY clause
o   Columns in the JOIN clause

3. Search Argument Problems

·                 Better to use constant
·                 Avoid to let the column against an expression, a variable, or an operation against the column
·                 Avoid to use negative
·                 Avoid to use No SARGs
·                 Avoid to use NULL in WHERE clause
·                 Avoid to use mismatching data types

4. Large Complex Queries

·                 Simplifying the query as much as possible

5. Triggers - Trigger can cause performance problems due to the statements within the trigger or nested triggers

·                 Tuning the trigger, starting with monitoring the statement executions within the trigger in the SQL Server Profiler