Guide to handle high CPU caused by deadlocks

by

We were working for a customer who was facing below issues on MS SQL Database server:

  1. Consistent high CPU near to 100% making application performance to suffer badly.
  2. Frequent deadlocks on multiple tables.
  3. Simple single row update queries taking lot of time to finish.

Initial Approach:

  1. We did basic health check of database server and implemented few changes from configuration perspective related to performance (memory settings, optimize for adhoc workload, parallelism settings, tempdb configurations etc.)
  2. We suggested and implemented a few missing indexes.

Issue Identification:

Above things helped very little so we started further investigation of deadlocks and execution plans of high CPU queries. We identified below issues:

  1. SQL server generated 100 deadlocks in a day. Many deadlock were same. Other deadlocks also had similar pattern however impacting different tables.
  2. Victim as well as winner processes were same UPDATE queries impacting different rows of table. The update operation on a single row was consuming a lot of time and CPU, resulting in another update query being deadlocked. Non clustered index was already available on column specified in where clause of update query.
  3. Concerned tables had different number of records. One of the table had around 2.6 million records.
  4. Execution plan of one of the update query pointed that non clustered scan was happening on column specified in where clause. It was scanning all the 2.6 million records of the table even for updating a single row. Non Clustered Index Scan is a resource intensive operation, it was taking majority of query execution time as well as CPU. Please refer to the screenshot below.

5. Winner query acquired key lock on entire non clustered index due to above scan operation. The query caused the victim to become deadlocked.

6. We further looked at execution plan of update query and it pointed towards implicit conversion. Implicit conversion was causing query to use Non Clustered Index Scan.

We observed similar implicit conversions in other deadlocks and we should avoid them in all query executions to prevent locks, deadlocks, and high CPU usage.

Resolution
So now the task was to identify the cause of implicit conversion and fix it. Implicit conversion was being observed on primary key of concerned table. Cause of this was below:

  1. Primary key column was defined as varchar in table definition.
  2. Application was sending related parameter in nvarchar

Above caused problem. We suggested customer to bring data types in sync. This was possible either by making data type changed to nvarchar at table level or by sending related parameter as varchar from application.

Customer fixed at application end to make sure parameters are sent as varchar. This solution got rid of all the implicit conversions. Non cluster index scans stopped happening for single row updates. queries became very fast, deadlocks were eliminated , CPU came down significantly.

I hope this Guide to handle high CPU caused by deadlocks helps you if similar issue is encountered. Do share your inputs if any.

Post Views: 398