- Compare the query execution time, cost of the execution plan, number of logical reads to determine the effect of creating new index, modifying the SQL code etc.
- Make sure that proper clustred index is created. Click here for guidelines on cluster index creation.
- Creating indexes will not always improve performance. INSERT,UPDATE,DELETE may get affected if there are too many indexes. Even some of the SELECT queries will run slow if there are many unused indexes.
- Determine the columns for index creation by looking at FROM, WHERE Clause of the queries.
- Higher Selectivity is criteria for creating indexes on a column. The ratio of the number of distinct values in the indexed column / columns to the number of records in the table represents the selectivity of an index. The ideal selectivity is 1.
- Order of tables in query doesn’t really matter incase of INNER JOINs
- Order of tables in query does matter for OUTER JOINs because the semantics of the query changes depending on what order you specify the tables.
- Order of columns in SELECT clause doesn’t really matter
- For queries with single column filter in WHERE clause, only an index that includes the filtered column as the first key in it will be used.
- Order of conditions in WHERE clause doesn’t really matter incase of multi-column filter. Only selectivity of the index matters.
- An SQL statement can use an access path involving a composite index if the statement contains constructs that use a leading portion of the index. A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in the CREATE INDEX statement that created the index. Ex: CREATE INDEX idx_composite ON mytab (x, y, z): leading portions of the index: X, XY, and XYZ.
- Avoid using user-defined/default functions in queries where they invalidate the use of indexes
- Clustered index scan or table scan should be avoided by filter using WHERE clause and non-clustered index on filter columns.
- Bookmark/Key lookup should be avoided by creating a INCLUDE index or covering index that includes all columns of the query in the index.
- Consider creating a composite index on columns that are frequently used together in WHERE clause conditions combined with AND operators, especially if their combined selectivity is better than the selectivity of either column individually.
- Consider indexing columns that are used frequently to join tables in SQL statements.
- Plan guides can be used to guide query optimizer to use a specific query execution plan every time when a query runs.
- Parameterizing the queries using stored procedures is good because it will ensure that similar queries with different parameters are not compiled every time and single query execution plan will be used.
- Avoid cursors because they consume more memory and locks. Check if cursors could be replaced by WHILE loop or user defined functions.
- Avoid using views particularly with complex joins and aggregate operations like GROUP BY.
- Avoid using local variables in SELECT statements. Use sp_executesql or stored procedures with parameters instead of local variables.
Query Tuning Recommendations from Microsoft
When to Break Down Complex Queries