Post reply

Configure sql profiler to show queries that are timing out

  • November 12, 2012 at 9:05 am

    #393586

    Hello,

    How can I configure sql profiler to show me the actual query that has timed out. Im getting a few timeout application error from a web application and I want to see the actual query that is timing out. So far the events I have configured are

    attention, execution warnings and User Error Message. If add the tsql and stored procedure events then the profiler gets filled with info that I dont want to view.

    Thanks

    Im using SSMS 2008 R2

  • November 14, 2012 at 9:33 am

    #1559017

    As you mentioned, tsql started and completed event will gather too much data that it will often be hard to analyze and get to what we are looking for. Loading the trace file into database and running some queries to short list those trouble queries could help.

    There is nothing in SQL server that determines the query is taking too much time and has to be cancelled. This will be upto the client side. SQL server has the default execution time out of 0 (infinite).

    One analogy to track time outs is to look for the sql start events that does not have completed event. The video is available here:

    http://www.midnightdba.com/DBARant/?p=538

  • November 14, 2012 at 9:43 am

    #1559020

    mbhandari (11/14/2012)

    As you mentioned, tsql started and completed event will gather too much data that it will often be hard to analyze and get to what we are looking for. Loading the trace file into database and running some queries to short list those trouble queries could help.

    There is nothing in SQL server that determines the query is taking too much time and has to be cancelled. This will be upto the client side. SQL server has the default execution time out of 0 (infinite).

    One analogy to track time outs is to look for the sql start events that does not have completed event. The video is available here:

    http://www.midnightdba.com/DBARant/?p=538%5B/quote%5D

    Thanks for the reply. This is what I did to track down the queries. I know that the application will raise a timeout exception if a stored proc takes longer than 30 secs.

    So I traced these event:

    Stored Procs (RPC Completed)

    TSQL (Batch Completed)

    Error and Warning (User Error Messages)

    I filtered on the database the app uses and where the duration of the procs/tsql was >= 30 secs. This showed me the procs that were taking > 30 secs and I was able to cross reference the time stamps to the evnt log on the web server which matched the timeout errors on web server event log.

    In the end it turns out that the problem was 2 fold. The application had no command.timeout property set so it was defaulting to 30 secs. The databases was on an instance with other heavy usage databases

    Thanks

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

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