SQL: Deadlock

Symptoms : SQL hangs temporarily for some users.

Impact : Medium

As well as delays experienced by users, there is possible loss of data when SQL acts to rollback one of the locked elements.

Expected behavior :

Deadlocks are not expected. This alert indicates needs to be investigated, especially checking data integrity. The longer a query runs, the more susceptible it will be to creating a deadlock.AimBetter Expert Support team can help in pinpointing the cause of the deadlocks, and suggesting the best methods for resolving the specific issues that will prevent deadlocks from happening in future.

Possible causes

Inefficient query design  Priority : Medium
Incorrect query code may result in inefficient data access.
Recommended action :

Identify the source of the query and try to achieve faster execution. Any application code that issues database queries should be equipped with error handling that deals with the possibility of this problem, and notify the calling client properly. We call this ‘defensive programming’ … a technique that anticipates and handles exceptions as a part of the general code base for an application or database. Since AimBetter lets you view and edit the code the necessary changes to code will be able to implemented much faster.
Long SQL tasks  Priority : Medium
SQL Processes started to load CPU due to long tasks performed such as :
– accessing data where indexes are missing or corrupt (causes long loops)
– pulling large volumes of data (results in many threads or long loops)
– coding problems (inefficient organization, missing indexes causing many threads or long loops)
Recommended action :
Optimize the application code – avoid starting a transaction in your application that is having long conversations with SQL Server, or risks leaving one open too long.
See our recommendations regarding long queries here. Identify the source of the load and if it is recurrent, try to reschedule to a time of low user demand.

Incorrect, unnecessary, missing or corrupt indexes  Priority : Medium
In cases or corruption, consult with your storage management team to identify the source. Our own support expert team’s experience has shown that one possible remedy in cases where the corruption is repeating is to clone the database onto a new hardware configuration.
In case of missing or incorrect index, refer back to your application design team. It may be possible to change the index type (clustered/non-clustered) or to switch to covering indexes which include all referenced columns.
Removing unnecessary index should be done in consultation with the application designers.
Recommended action :
Indexes are one of the most important features of the SQL Server but can be a source of many problems.

Incorrect indexing can not only destroy the benefits of indexing, but add more load onto the server. An example would be use of composite indexes where the order of the column is not followed in the calls leading to poor execution times.

Unnecessary indexes negatively impacts by incresing the load for index maintenance, as well as additional storage on disks that indexes require.

Corruptions in existing indexes are usually disk or disk driver/firmware related.

Missing indexes are usually identified by SQL Server at the time of execution, and should be built for future efficiency.

Background

A deadlock occurs when two or more processes are competing for exclusive access to a resource but are unable to obtain it, because some other process is preventing it. This results in a standoff where neither process can proceed. The only way out of a deadlock is for one of the processes to be terminated. SQL Server automatically searches for deadlocks (in the process called LOCK_MONITOR) and looks for deadlocks repeatedly. It looks at all waiting locks to determine if there are any cycles. When it detects a deadlock it chooses one of the transactions to be the victim and sends a message to the client which owns the connection. This transaction is then terminated and rolled back which releases all the resources on which it held a lock, allowing the other transaction involved in the deadlock to continue.

From AimBetter monitor you can drill down into any transactions involved in a deadlock, identifying both the killed one and the surviving one. You can also view deadlocks that have occurred in the recent past without having to reproduce the issue because with AimBetter all deadlocks are logged and stored in the cloud for as long as you have selected.