Post reply

How to identify long running or blocking area in the stored procedure using sql server 2000 profiler

  • November 5, 2008 at 5:36 am

    #383308

    How to identify long running or blocking area in the stored procedure using sql server 2000 profiler

  • November 5, 2008 at 7:52 am

    #894249

    Hi,

    In 2000 it is a bit cumbersome to troubleshoot blocking and you cannot do it using profiler only (well, I suppose in theory you could do it by looking at the Locks acquired/released events but I'd say that would be a job of gargantuan proportions if this server is a live system).

    You need to correlate the information from Profiler with information from some virtual tables withing SQL Server. There is a very well known stored procedure called sp_blocker_pss80 that is published in http://support.microsoft.com/kb/224453.

    sp_blocker_pss80 correlates information from various places in SQL Server (such as sysprocesses, syslocks) and the output can be substantial if you have a lot of blocking. However, it will only write output if there is blocking at all.

    It uses DBCC INPUTBUFFER** to grab information about the statements running but this is not a very reliable source of information - it will only give the first 255 characters of the statement currently running and that might not be what is causing the blocking. Regardless, usually it gives you a good clue to what is going on but if it is not sufficient - this is where you correlate the SPIDs given to you by the blocker script with the output from Profiler.

    Another option is to run PSSDIAG and let SQLNexus crunch all the data for you - when I was still at Microsoft PSS, SQLNexus did not process the output from sp_blocker_pss80 but I don't know what the current version can do with SQL Server 2000 output (in 2005 we don't use sp_blocker_pss80).

    PSSDIAG: http://www.microsoft.com/downloads/details.aspx?familyid=5564386A-28C2-4483-8293-76FFF67B9EB3&displaylang=en

    SQLNexus: http://www.codeplex.com/sqlnexus/Release/ProjectReleases.aspx?ReleaseId=16225

    HTH!

    /Elisabeth

    ** There is function fn_get_sql that returns it properly, see http://support.microsoft.com/kb/325607 for a description.

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • November 6, 2008 at 7:31 am

    #894856

    if you want to use profiler to identify the code within a stored proc running slowly you will need to trace event SP:stmtcompleted under stored procedures as well

    ---------------------------------------------------------------------

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

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