Post reply

How to detect and troubleshoot slow statements within stored procs

  • November 20, 2013 at 1:01 pm

    #301560

    Hi All,

    I want to know about how to go about detecting and troubleshooting slow sql statement inside stored procedures.

    For example ) If I have 10 sql statements inside my stored procedure and statement-7 is hung or taking more time, and I see in ssms that stored procedure is keep on executing. Now, what are the possible ways, I can figure out that stored procedure hung/stuck at sql statement-7.

    How can I nail it down to that extent and tell this is the statement in this particular stored procedure which are running slow and have to be optimized. Is there any direct DMV's or what profiler events or any coding tips like inserting print statements which can help me figure out where the stored procedure is currently executing. But again, print statement will be helpful only if I am executing that stored proc's.

    Could anyone please provide tips and different ways to nail these slowly running sql statements within stored procedures. I work on sql 2005,2008 instances.

    Thanks in Advance.

  • November 20, 2013 at 1:03 pm

    #1667909

    https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 20, 2013 at 11:31 pm

    #1668029

    Thank you very much Gail.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply