Deadlocks and blocks

  • Dear All,

    Could you please advise on the below.

    1.how to find which query/storedprocedure is causing for more deadlocks ?

    2.how to find which query/storedprocedure is causing for more blocking issues?

    3.how to find which query/storedprocedure is consuming more cpu time and IO?

    Since,I want to submit this report to our management and developer team.

    Thanks in advance.

    Regards,

    Ravichandra.

  • For deadlocks you can enable trace flag 1222 which will output to the SQL error log.

    To find queries with the highest CPU and IO you can install performance dashboard which will show you the top 10 for each.

    http://www.microsoft.com/downloads/en/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

  • Thanks.

    Please provide script or suggest free third party tool or any other solution apart from dashboard and trace flag.

    For deadlocks, I want to know which query mainly causing this.

    Regards,

    Ravichandra.

  • ravisamigo (11/11/2010)


    Thanks.

    Please provide script or suggest free third party tool or any other solution apart from dashboard and trace flag.

    For deadlocks, I want to know which query mainly causing this.

    Regards,

    Ravichandra.

    You dont really need a third party tool to enable a trace flag. Google abt trace flag 1222 and see what it does. What's more important is to analyze the output it gives.

    Also, there are several DMV's which you can use to find out which queries are using most cpu/IO.



    Pradeep Singh

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

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