patternsqlMinor
Automatically reject certain expensive query statements
Viewed 0 times
statementsautomaticallyrejectquerycertainexpensive
Problem
I have some clients which sometimes run some "not allowed" very expensive queries.
I've tracked them down using Extended Events.
Is it possible to reject these queries automatically? I know the SQL statement so would be able to catch them using the "sql_text" event in EE.
But I haven't found a way to perform any actions in EE when e.g. a particular event is happening.
Or is there another way around this?
I've tracked them down using Extended Events.
Is it possible to reject these queries automatically? I know the SQL statement so would be able to catch them using the "sql_text" event in EE.
But I haven't found a way to perform any actions in EE when e.g. a particular event is happening.
Or is there another way around this?
Solution
The main options are the query governor cost limit and Resource Governor.
The former is a bit limited because it applies to all queries and limits them based on estimated cost, which can be very inaccurate.
Further, SQL Server Management Studio issues
The better option is Resource Governor (if it is available to you) when combined with:
Update adds CPU timeout setting to Resource Governor workgroup REQUEST_MAX_CPU_TIME_SEC in SQL Server 2014, 2016 and 2017
When Trace Flag (TF) 2422 is enabled, and if a request exceeds the maximum time set by REQUEST_MAX_CPU_TIME_SEC, then the request is aborted. Additionally, you may receive an error message 10961 that resembles the following:
The request has been aborted because its CPU usage exceeds the maximum configured limit for the workload group.
It isn't possible to manage this with Extended Events.
Your other options were described by Dominique Boucher:
Why not simply talk to those people who are running those query and told them not to? [...] Remove access from those who run the queries after you told them not to and that should do it. Otherwise, [...] you could have a job that run all the time and check running queries and kill the one you don't what to have running. They will still start to execute but technically, the end user will get errors all the time so they may eventually give up.
Erik Darling had a similar suggestion:
If I knew the query text ahead of time, I'd probably just set up a while loop in an Agent Job to find when those queries are executing, collect the SPID, and kill the session.
The former is a bit limited because it applies to all queries and limits them based on estimated cost, which can be very inaccurate.
Further, SQL Server Management Studio issues
SET QUERY_GOVERNOR_COST_LIMIT 0 when connecting (with SSMS options set to default). This overrides the instance-level setting and so renders this option pretty ineffective.The better option is Resource Governor (if it is available to you) when combined with:
Update adds CPU timeout setting to Resource Governor workgroup REQUEST_MAX_CPU_TIME_SEC in SQL Server 2014, 2016 and 2017
When Trace Flag (TF) 2422 is enabled, and if a request exceeds the maximum time set by REQUEST_MAX_CPU_TIME_SEC, then the request is aborted. Additionally, you may receive an error message 10961 that resembles the following:
The request has been aborted because its CPU usage exceeds the maximum configured limit for the workload group.
It isn't possible to manage this with Extended Events.
Your other options were described by Dominique Boucher:
Why not simply talk to those people who are running those query and told them not to? [...] Remove access from those who run the queries after you told them not to and that should do it. Otherwise, [...] you could have a job that run all the time and check running queries and kill the one you don't what to have running. They will still start to execute but technically, the end user will get errors all the time so they may eventually give up.
Erik Darling had a similar suggestion:
If I knew the query text ahead of time, I'd probably just set up a while loop in an Agent Job to find when those queries are executing, collect the SPID, and kill the session.
Context
StackExchange Database Administrators Q#274042, answer score: 7
Revisions (0)
No revisions yet.