SQL Server performance issues when using mismatched data types

By: Ranga Babu | Comments (3) | Related: More > Performance Tuning

Problem

For one our applications we encountered deadlock issues when deleting rows for a high concurrency application. The application queries were deleting only one row at a time, but we were still having deadlock issues.

Solution

After analyzing the deadlocks using Profiler with lock events and the deadlock graph, we found that when deleting a single row it acquired 3500 locks. Further analysis revealed the below points:

The delete query used an index scan. (not always, but most of times).
The data type of the table column is varchar, but the values stored are integer (we should be using bigint instead of varchar).
The delete query uses N as prefix in the WHERE clause (i.e. delete from table where col1 = N'value').

To simulate this I created a table with a similar schema and some sample data

CREATE TABLE QueryIOStats (id varchar(25) not null, NAME VARCHAR(25))

DECLARE @I INT,@M INT
SET @I =1 
SET @M =100000
WHILE @I<=@M
BEGIN
   INSERT INTO QueryIOStats VALUES(@I,'SampleTest')
   SET @I=@i+1
END

I then created a primary key on the id column.

ALTER TABLE QueryIOStats ADD CONSTRAINT PK_QueryIOStats PRIMARY KEY CLUSTERED (id)

Use different scenarios in the WHERE clause to illustrate issue

Selecting data with N prefix

SET STATISTICS IO ON
SET STATISTICS TIME ON 
SELECT * FROM QueryIOStats WHERE id = N'1'

Below we can see the query statistics and the query plan. We can see this query is doing a Clustered Index Scan, because of an implicit conversion where it is converting the table column to nvarchar to match the requested data type. In my testing, I had conflicting results. Sometimes it was doing a full clustered index scan and other times it was not.

Result Set1 query plan with nvarchar data type

Selecting data without N prefix

SET STATISTICS IO ON
SET STATISTICS TIME ON 
SELECT * FROM QueryIOStats WHERE id = '1'

Below we can see the query statistics and the query plan. We can see this query is doing a Clustered Index Seek.

Result Set2 query plan with varchar data type

Selecting data with an integer value

SET STATISTICS IO ON
SET STATISTICS TIME ON 
SELECT * FROM QueryIOStats WHERE id = 1

Result Set3 query plan with implicit conversion

Change Table Structure to use nvarchar

Now drop the primary key, modify the column to nvarchar and create the primary key again.

ALTER TABLE QueryIOStats DROP CONSTRAINT PK_QueryIOStats;

ALTER TABLE QueryIOStats ALTER COLUMN id nvarchar(25) not null

ALTER TABLE QueryIOStats ADD CONSTRAINT PK_QueryIOStats PRIMARY KEY CLUSTERED (id)

Selecting data with N prefix

Below we can see the query statistics and the query plan. We can see this query is doing a Clustered Index Seek.

Selecting data without N prefix

Below we can see the query statistics and the query plan. We can see this query is doing a Clustered Index Seek.

Selecting data with an integer value

Conclusion

I made same change to my database table and changing the data type to nvarchar solved my problem as well as keeping the data types consistent.

We should use int or bigint for columns where we store integer values. There is no point in using varchar as the data type and storing integer values in the form of varchar. I asked my developer to change the column to bigint, but he simply refused.

As you can see having the requested data type values match the table data type values can greatly impact how queries behave.