SQL Server - Quick Query Optimization Tips based on Execution Plan

SQL Server Quick Query Optimization Tips based on Execution Plan

1.    If hash or merge algorithms are selected by the query engine for the join, data retrieved from the table should be sorted by the join key. If a clustered index is built on the join key, retrieved data is already sorted.

2.    Always look at the query plan first. It will show you the optimal current execution plan from the query engine's point of view. Find the most expensive part of the execution plan and start optimizing from there. However, even before that, make sure that the statistics on all tables in your query are up to date, by running the update statistics <TableName> command on all tables in your query.

3.    If you see clustered index scan, find out whether it can be replaced with index seek. For that, find what conditions are applied to this table. Find out the most selective condition (that is, the condition that would produce the smallest number of records if applied alone), and see whether an index on this field exists. Any index that lists this field first will qualify. If there is no such index, create it and see whether the query engine picks it up.

4.    If you see bookmark lookup, it means that your index is not covering. Try to make it covering if it makes sense (see the preceding guidelines).       

5.    Avoid any operations on the fields, where possible. Some operations will prevent the use of the index on this field even if it exists—for example, the infamous ltrim(rtrim(FieldName)); other operations will degrade the performance. For example, instead of using the condition cast(DateField as varchar(20)) = @dateString, try to convert @dateString to an expression of datetime type first, and then compare it to DateField.

6.    When it is not possible to avoid operation on the field, use an index built on that expression. This can be done in two ways:

Create a calculated field based on your expression.

Create a view, and build an index on it.

7.    Indexed views are a good way to further speed up the query if you are not satisfied with the results. Indexed view is a clustered index built over the view's select list. You can also define additional indexes for the indexed view, just as you can for any regular table. Indexed views take disk space and involve some maintenance overhead (every time underlying tables change, the indexed view also has to change), but they usually provide a good boost in performance, even after all other optimization techniques are exhausted.

8.    Check for deadlocks happening; Open a new query editor window and exec below commands, it will give you all the deadlocks which are happening.  This command Returns the resources and types of locks participating in a deadlock and also the current command affected.

dbcc traceon(3604)

go

dbcc traceon(1204)

go

or the better command to check for deadlock in 2005+ servers is below

DBCC TRACEON (1222, 3605, -1)

1222 – deadlock detection

3605 – O/P to errorlog

-1 – signifies that flag is enabled at global level and not local

9.    In an execution plan, if there is difference between “expected rows” and “actual rows” then there is something wrong with Statistics.

Ardent-DBA