Post reply

Query Governor

  • May 24, 2004 at 1:34 pm

    #84426

    Due to the high number of poorly performing queries that are run on my servers on a daily basis I insituted the Query Governor today. It works great and has already annoyed many an ad-hoc report user by preventing them from killing the servers.

    I've changed the sysmessage for this event (8649) so that it writes an event to the log so that I know this has happened, however the problem is that it does not give me any other information regarding the query that was trying to be executed.

    Does anyone know of a way that I could somehow gather this information and include it in the error message, I've tried to find something further, but I think this might go too far into the SQL internals to actually get all that I need.

    Any thoughts?

    Thanks


    Shamless self promotion - read my blog http://sirsql.net

  • May 24, 2004 at 5:20 pm

    #507558

    You need to find out which query or queries is perfroming poorly! Once you have established that, do an execution plan to see if Indexes are been used. This is the place to start!

    Query Govenor is a temporary measure only!

    Kindest Regards,

  • May 25, 2004 at 4:04 am

    #507600

    You could set up a trace with SQL Profiler (or sp_tracecreate/sp_trace_setevent) for the Errors and Warning:Exception class, selecting at least Error, SPID, LoginName, NTUserName, DBUserName columns and maybe filtering for Error 8649. That will at least give you the culprit session/user.

    You could also trace SQL:StmtStarting (or similar) to see the submitted query.


    Salvor

  • May 25, 2004 at 5:12 am

    #507614

    The problem is that I have users writing ad-hoc queries on the server and I am not in a position to be able to prevent that from happening, I am trying to prevent a single user from killing the server and stopping other users from getting the information they require.


    Shamless self promotion - read my blog http://sirsql.net

  • May 25, 2004 at 1:16 pm

    #507750

    I wonder if you could use @@spid and dbcc input_buffer to get what you need.

    Steve

  • May 25, 2004 at 1:27 pm

    #507752

    The problem is that the governor uses is not called using SQL that I have been able to trap so I don't believe that I can change the way that the error is called to include further parameters than currently are passed.


    Shamless self promotion - read my blog http://sirsql.net

  • May 26, 2004 at 1:47 am

    #507804

    I set up the simple trace session I described in my previous post, then ran this batch within QA:

    SET QUERY_GOVERNOR_COST_LIMIT 1

    select o.orderid,p.productid

    from orders o

    cross join [order details] p

    This is the trace collected:

     

    EventClassTextDataNTUserNameClientProcessIDApplicationNameLoginNameSPIDStartTimeError
    TraceStart      2004-05-26 08:24:14.783 
    SQL:StmtStartingSET QUERY_GOVERNOR_COST_LIMIT 1pippo2088SQL Query Analyzerpippo512004-05-26 08:24:20.793 
    SQL:StmtStartingselect o.orderid,p.productid from orders o cross join [order details] ppippo2088SQL Query Analyzerpippo512004-05-26 08:24:20.793 
    ExceptionError: 8649, Severity: 17, State: 1pippo2088SQL Query Analyzerpippo512004-05-26 08:24:20.7938649

    As you can see, the SQL statement is trapped even if it's not executed.


    Salvor

  • May 26, 2004 at 5:37 am

    #507822

    Thanks Salvor, I was looking to try and avoid running a trace if I could, but it looks as though thats going to be the only way to get the information.


    Shamless self promotion - read my blog http://sirsql.net

Viewing 8 posts - 1 through 7 (of 7 total)

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