1. Out-of-Date or Insufficient Statistics
ALTER DATABASE YourDBName SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE YourDBName SET AUTO_UPDATE_STATISTICS ON
--For SQL Server 2008+
ALTER DATABASE YourDBName SET AUTO_UPDATE_STATISTICS_ASYNC ON
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
