Archive for February 16th, 2011

Performance Tuning–Data Type Mismatch February 16th, 2011

Vinod Kumar

I was recently at a customer discussion and something strange happened during the code review. As best practice, it is advised when using stored procedure not make mistakes of sending the data in wrong data types. These induce an implicit conversion that can cause performance bottlenecks inside SQL Server. These basics are often forgotten and missed as part of the process.

For illustration purposes I used a very simple table for example.

CREATE TABLE test
  (
     col1 VARCHAR(10),
     col2 CHAR(200)
  ) 

The stored procedure is also made simple. The catch here is the parameter as NVARCHAR whilst the column is VARCHAR. In many places this confusion is common. In the name of internationalization I have seen people just use the NVARCHAR and forget the same while writing the SPs.

CREATE PROCEDURE Proc_test_mismatch @MyParam NVARCHAR(10)
AS
  SELECT *
  FROM   test
  WHERE  col1 = @MyParam 

SET statistics profile ON
GO
EXEC Proc_test_mismatch N’1000000′
GO
SET statistics profile OFF 

Execution of the above SP creates an interesting Plan that one needs to keep an eye on. The hidden perf deterrent is the IMPLICIT CONVERT that is happening inside SQL Server.

       |–Clustered Index Scan(OBJECT:([tempdb].[dbo].[Test].[idx1]), WHERE:(CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[Test].[col1],0)=[@MyParam]))

You might argue what is the biggest drawback because of this. Actually, behind the scenes there are two problems that can be anticipated.

  1. First, when SQL Server has to convert a column, it is no longer able to use index.  
  2. Additionally, cardinality estimate will be off as well.  It can no longer use statistics on the column being converted

So something that look as simple as not using the ā€œNā€ in front of the data types can help you manage and improve performance just because the data is designed like that. The solution is as simple as possible.

  1. If it’s a stored procedure, the easiest solution is to change the stored procedure parameters to match the data type of the column the parameter is comparing against.
  2. If the columns of two tables don’t match when joining, one of the column’s data type needs to be changed to match.

Once we do the simple steps, the execution plans will become clean. Check the SEEK operation on execution of the same SP now after the changes.

  |–Clustered Index Seek(OBJECT:([tempdb].[dbo].[Test].[idx1]), SEEK:([tempdb].[dbo].[Test].[col1]=[@MyParam]) ORDERED FORWARD)

So, just go around and check if you have such mistakes in your products code. And drop me a line here if you found one. All the Best !!!

Continue reading...