- Open the Server Profiler : “SSMS” -> “Tools” – >”Sql Server Profiler”
- Choose Tuning Template
- Limit the Database to your required one and the query duration to certain limit ( Start attacking the most expensive ones first. Hence set higher duration at first.)
- Start optimizing the query based on the duration and the frequency consideration
- Wola Done!!
PS: For ease, you can also export the profile to a database table and start tuning.
For example, In one of the instances, I tracked down a expensive query and tuned its duration by three times from “15 sec” to “4 sec” with a simple Search Space reduction. In the first query there were couple of joins and the filter was done at the end after join operations and hence the query was expensive. Simple introducing the filter during the record extraction and then applying joins was the optimal strategy and reduced the query execution time by three time to to 4 seconds.