Metric: Count of processes that have been killed.
Impact: Informational
AimBetter tracks the number of queries that have been terminated with a ‘kill’. This may be happening for a number of reasons, including user action, error handling in code and others as described in the section below.
Default settings are:
| CRITICAL : | ||
| MEDIUM : | ||
| LOW : | 10 | |
Expected behavior: There is no standard level for kills, but in general, spikes or exceptional numbers may indicate that investigation is required.
Background
The kill command is associated with a specified session ID and is commonly used to end a process that is blocking other important processes with locks. It can also be used to stop a process that is executing a query that is using necessary system resources.
The kill may come from inside executing code, which generally means that an abnormal condition has been found and the query cannot complete. If the code is set to handle errors (via the TRY – CATCH – THROW logic) it could terminate the query directly. It may also come via direct action from a user.
A ‘query timeout’ value is set before any query runs. SQL Server estimates how much memory it needs to run this query and tries to reserve this amount of memory from the buffer pool. If the reservation succeeds, the query is executed immediately and the default timeout value (usually 30 seconds) is set. If there is not enough memory readily available from the buffer pool, then the query is put into the ‘runnable’ queue with a timeout value calculation based on the estimated query cost. If the waiting time of this query (see more about waits here) exceeds the estimated timeout value, a timeout error is thrown and the query is removed from the queue and killed.
The kill process normally involves rolling back the transaction so that when a process is killed without completing, all data changed by any uncompleted transactions will be returned to the state it was in before the transaction started.
All the uncommitted changes made by the process are going to be undone. In most cases, the rollback process happens very quickly and does not have a major impact. In cases where there are a large number of uncompleted transactions, the rollback process can take some time and have a major impact on the performance of the database during that time.
Kill also ends a normal connection, which internally stops the transactions that are associated with the specified session ID.
Note: System processes, and processes running from an extended stored procedure, cannot be killed.
