Initially I thought that runaway query is another name for ad-hoc queries but it is NOT. It actually refers long running queries that takes long time than you expected or previously executed with a shorter period but not takes long time.
* image taken from www.dreamstime.com.
There can be many reasons for runaway queries. Poor statistics, poorly written codes, blocking and locking or even heavy resource consumption can be some of the reasons for this. Example, a report generally takes 1-2 minutes to open suddenly takes 15-30 minutes. Or a user runs a query expecting the result in few minutes but takes hours.
How can we handle this with SQL Sever?
You can monitor them easily and stop the execution by killing the process. You can use Activity Monitor (Active Expensive Queries window - Elapsed Time column) or sys.dm_exec_query_stats dynamic management view (see this: https://www.brentozar.com/blitzcache/long-running-queries/). However, rather killing the session, it is advisable (or always better) for detecting runaway queries and allocate limit resource consumption that makes sure other standard operations are not disturbed. Even though you cannot fully handle it, the Resource Governor can handle this up to some extent.
Remember, Resource Governor does not detect runaway queries. It can only limit resources for requests, identifying certain properties of the request such as application name, user name, etc. Example, if you want to limit resources from one of the reporting applications that has lengthy reports and requests coming from it have no higher priority, application name can be tracked via a function called Classifier User-Defined Function which we specifically write for implementing Resource Governor and assign the request to correct workload group that uses least resources. Read following articles for understanding Resource Governor and how to use it for limiting resources.
No comments:
Post a Comment