By: Ranga Babu | Comments (3) | Related: > Data Types
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 with nvarchar data type](/tipimages2/4380_ResultSet.png)
![Result Set1 query plan with nvarchar data type](/tipimages2/4380_query_plan_1.png)
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 with varchar data type](/tipimages2/4380_SETSTATISTICS.png)
![Result Set2 query plan with varchar data type](/tipimages2/4380_query_plan_2.png)
Selecting data with an integer value
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 Scan, because of an implicit conversion where it is converting the table column to integer to match the requested data type.
![Result Set2 with implicit conversion](/tipimages2/4380_QueryIOStats.png)
![Result Set3 query plan with implicit conversion](/tipimages2/4380_query_plan_3.png)
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.
![Result Set2 with matching data types](/tipimages2/4380_clauses.png)
![Result Set2 with matching data types](/tipimages2/4380_query_plan_4.png)
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.
![Result Set2 with matching data types](/tipimages2/4380_N_prefix.png)
![Result Set2 with matching data types](/tipimages2/4380_query_plan_5.png)
Selecting data with an integer value
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 integer to match the requested data type.
![Result Set2 with an implicit conversion](/tipimages2/4380_database_table.png)
![Result Set2 with an implicit conversion](/tipimages2/4380_query_plan_6.png)
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.
Next Steps
- Check your query plans to see if implicit conversions are occurring
- Keep your data types consistent to avoid having these issues
- Read more tips on tuning SQL Server
About the author
![MSSQLTips author Ranga Babu](/images/Ranga-Babu.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips