Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Performance tunning

Author  Topic 

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2015-04-20 : 21:10:44
I am working performance issue of stored procedure and would like to tune the scripts.

Please provide the general performance scripts to determine and identify the long running queries..

thanks for your help in advance ..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2015-04-20 : 22:17:40
Glenn Berry has a number of diagnostic queries you can use:

http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-march-2015/

Better to start with those, he describes them on his blog. You should also check Paul Randal's blog on the same site:

http://www.sqlskills.com/blogs/paul/category/performance-tuning/
http://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/ <--- this is is a good intro for the following

Also look at the "SQL 2005 Waits and Queues" Whitepaper here:

http://blogs.msdn.com/b/sqlcat/archive/2013/09/05/sqlcat-com-consolidated-index-of-whitepapers.aspx

It's still relevant for SQL Server through version 2014.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-21 : 01:46:56
If you want to check by CPU time use http://www.sqlserver-dba.com/2012/11/sql-server-performance-top-queries-by-total-cpu-time.html .

Once you've identified the long running queries , you need to complete some root cause analysis. It's important to ask the right questions , is it a workload , efficiency or capacity issue ? http://www.sqlserver-dba.com/2013/02/sql-performance-tuning-asking-the-right-question.html .

Your analysis will inform what solution you use

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -