Post reply

Finding Long Running Queries

  • November 16, 2006 at 6:35 am

    #121568

    Hi,

    We have an issue with one of our Live SQL Servers which has a problem whereby the amount of CPU usage grows to max out the CPU after 6 to 18 hrs.  The SQL counters that I checked all seem to checkout, so I thought I would check for long-running queries which weren't finishing

    I set up a trace on rpc:starting, rpc:completed, SQL:Batchstarting, SQL:BatchCompleted, which I saved to a table.

    On the assumption  that spids are re-used I ran a query looking for a count for each spid which was odd.  This would in theory also pick up innoccuous entries for where the trace had picked up a rpc or SQL completeing statement which I could discount.

    I have found some interesting results which I don't understand

    If I take one particular spid in the trace list,  I found an rpc:starting entry with no corresponding ending entry (using the logic that the starttime on both should be identical) and then 2 minutes later Another rpc:starting entry for the same spid with a corresponding rpc:completed entry.  This would seem to indicate an execution that didn't end, but then sql server re-using the spid to begin another execution

    Has anybody else tried to find long-running queries and if so what technique have they used ?

    Thanks

     

     

     

     

     

     

     

     

     

  • November 16, 2006 at 7:49 am

    #672288

    For long running queries, you can filter by the duration. Watch the RPC:Complete event and Set duration to, say 2 minutes.

    It's not safe to filter by SPID becuase client side may use connection pool.

     

  • November 16, 2006 at 3:32 pm

    #672481

    Hi Peter,

    Thanks for the reply, I've been checking the duration and have noticed some relatively high figure in the region of 20 to 30 seconds, but also wanted to try to find any queries that were very long running, ie had started and not finished when I finished the trace (I strated the trace when I restarted SQL server), do you know if this is possible,

    With regard to the SPID and connection pooling, could you elaborate - do you mean that client processes on diferent machines could use the same SPID simultaneously

    Thanks

  • November 16, 2006 at 3:47 pm

    #672488

    How about master..sysprocesses table?

  • November 17, 2006 at 4:59 am

    #672548

    Thanks - good call, I'll keep an eye on that

    Regards

    Simon

     

     

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

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