Basic Approach
Here are some things to think about.
- Identify Slow SQL : The quickest way to identify slow SQL is to use the SQL Server Profiler. You should focus on the queries having the biggest impact on the process you are investigating. For example, saving 1 second on a query that runs once a day is less important than saving 0.25 of a second on a query that runs 10,000 times a day.
- Tune One Query at a Time : Focus on a single query. Make adjustments and and test the performance again. It’s possible that an improvement in that query will have a positive effect on others in the system, as there may be a general reduction in blocking. If possible, once one query is tuned, use the profiler again.
- Adding/Removing Indexes : You will often want to add or remove indexes to fix a problem. Although this may have a positive effect on your query, you have to consider the system as a whole. Altering indexes could result in a detrimental effect on other queries. You have to consider changes holistically.
- Change Over Time : As your data changes, your query performance may change. Don’t be surprised if execution plans change over time if your data changes drastically. That is why people usually try to avoid hints.
- Diminishing Returns : Be aware that something will always be “the slowest query”, but there is a point where spending any more time will be a waste of effort. You have to understand where tuning becomes “Compulsive Tuning Disorder”, a great phrase coined by Gaja Krishna Vaidaynatha.
- Communicate Benefits : People will make the same mistakes again and again. When you have identified an issue and fixed it, make sure other people understand the problem and the impact it was having. It might stop them making the same mistakes again.
Query Tuning in Batch Mode
You can run two or more queries at a time in the query window by highlighting them and clicking the execute button. This is called running them in batch mode. If you turn on the “Include Actual Execution Plan” option, explained here, you can compare the execution plans of multiple statements. This is how you can tune queries in batch mode. There are several approaches you can use.
- Compare slightly different SQL statements against the same tables to see which is the most efficient.
- Compare similar SQL statements against two or more copies of your tables with differing indexes, allowing you to compare the impact of the different indexing strategies.
- Use the same tables, but force each query to use different indexes using hints, so you can compare the results. Try to avoid hints wherever possible, but it might be useful for this type of comparison.
For more information see: