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
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)
WHERE col1 = @MyParam
SET statistics profile ON
EXEC Proc_test_mismatch N’1000000′
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.
- First, when SQL Server has to convert a column, it is no longer able to use index.
- 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.
- 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.
- 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 !!!
This entry was posted on Wednesday, February 16th, 2011 at 12:22 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.