1. Open the Server Profiler : “SSMS” -> “Tools” – >”Sql Server Profiler”
  2.  Choose Tuning Templatedata-product-profiler-1
  3. 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.)data-product-profiler-2
  1. Start optimizing the query based on the duration and the frequency considerationdata-product-profiler-3
  2. 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.

Advertisements