asked
performance issue in sql server 20005 related to stored procedure
application is running very slow how can i trouble shoot and how to improve that performance. How to find which stored procedure more time and each query taking how much time How to trouble shoot that.
With all the helpful answers below you should indicate them by clicking on the thumbs up next to them. If any one of the answers helped you solve your problem, click on the thumbs up next to that answer.
It may not be one particular stored procedure. Try using Wait Statistics to locate the bottleneck and follow from there to the source of your problem.
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ is a great starting place for this sort of investigation
If you already know specifically which query is causing you pain, you will need to look at the code on that query to identify if it has [common poor coding practices][1] that might cause poor performance. You can also look to the [execution plan][2] of the query to see what it is doing to access your data within SQL Server. For a general overview on dealing with these issues, I've written an [introduction to query tuning here][3]. For further details, I'd suggest getting a copy of my book on [Query Tuning][4]. [1]:
https://www.simple-talk.com/sql/performance/the-seven-sins-against-tsql-performance/ [2]:
https://www.simple-talk.com/sql/performance/sql-server-execution-plans---ebook-download/ [3]:
https://www.simple-talk.com/sql/performance/sql-server-performance-crib-sheet/ [4]:
http://www.amazon.com/gp/product/B008E6HOIS/ref=s9_simh_gw_p351_d0_i1?pf_rd_m=ATVPDKIKX0DER&pf_rd_s=center-2&pf_rd_r=0HVMV6BP2ASTVVY415K0&pf_rd_t=101&pf_rd_p=1389517282&pf_rd_i=507846
16 People are following this question.
