Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server performance issues when using mismatched data types


By:   |   Read Comments (1)   |   Related Tips: More > Performance Tuning

Attend a SQL Server Conference for FREE >> click to learn more


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

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 with varchar data type

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

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

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.

Result Set2 with matching data types

Result Set2 with matching data types

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

Result Set2 with matching data types

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

Result Set2 with an implicit conversion

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ranga Babu Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, July 25, 2016 - 1:47:32 PM - Amita Patil Back To Top

 

 Thanks for this information .. 


Learn more about SQL Server tools