Today, I read an outstanding article by Gail Shaw at simple-talk.com about the best way to identify the stored proc or t-sql query that is the culprit of a slow performance website. The basic technique, as suggested by the author, is to use SQL Profiler GUI to create the trace definition, focusing on TextData, CPU, write, reads, and duration data columns; run Profiler for a short period and stop, save the trace definition file. Author highly recommended that we should not run Profiler for too long as it will compete for server resource and sometimes can bring the server to a halt.

After trace is run, the results are exported to a table and by querying that table, sorted by CpuImpact, IOImpact, and TimeImpact, we can easily identify the stored proc name that costs most. It was a great writing, very clean and truck load of useful information. Click here to access the part I of the article…