Below is a short list of steps to take when optimizing your query statements.
More details on the steps can be found in this blog.
- Check if you're missing any required table joins.
This could result in a Cartesian join. - Check if you're missing any required WHERE clause to prevent retrieving too much data.
- Check if statistics are being created & updated automatically.
- Check if the statistics are up-to-date.
The DBCC SHOW_STATISTICS command can be used to view the last updated date-time, total table rows and the number of rows sampled.
Outdated statistics can be renewed using the sp_updatestats stored procedure, or By using the FULLSCAN option to update all statistics of a table. - Check for any missing table or Index scans by using the execution plans.
you can use these DMV's to check for missing indexes: sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups. - Check for RID Lookups, also by using the execution plans.
These cannot always be eliminated, but by making use of covering indexes, RID Lookups can be reduced. - Check for any sort operator, again by using the execution plans.
There are 3 options:- Modify the underlining tables to create a CLUSTERED index on the required sort columns. It could be worth trying out creating the CLUSTERED index on another column which is not the Primary Key.
- Create an Indexed view on the underlining tables and sort the view by creating a CLUSTERED Index.
- Create a NON CLUSTERED Index on the specified columns and Include all other columns which will be returned.
- Check for excessive index fragmentation.
The DMV sys.dm_db_index_physical_stats can be used for this matter. - Check table locks.
To prevent locking problems, follow these guidelines:- Keep transactions as short as possible.
- Review the transaction isolation level, and consider minimizing locking contention, thus increasing concurrency by changing to 'Read Committed using row versioning' or 'Snapshot'.
- Specify table hints such as READUNCOMMITTED or READPAST on the select statements. Although both of these table hints do increase concurrency, both have disadvantages such as 'dirty reads' when using the READUNCOMMITTED or returning an incomplete data set when using the READPAST and therefore they may not be acceptable to use in all circumstances.
Source: http://www.sqlservercentral.com/articles/Performance+Tuning/70647/
