Sql-server – Unexpected Implicit conversion in a procedure

execution-planperformancequery-performancesql serversql-server-2008-r2stored-procedures

I have a procedure like this (simplified):

CREATE PROCEDURE test @userName VARCHAR(64)
SELECT * 
FROM member M
INNER JOIN order O  
ON M.MemberId=O.MemberId
WHERE M.Username = @userName

There is a non-clustered index on Username column of the Member table.

Plan cache shows an Implicit Conversion as such:

Seek Keys[1]: Prefix: [MyDatabase].[dbo].[Member].Username = Scalar
Operator(CONVERT_IMPLICIT(varchar(64),[@Username],0))

I was just wondering what might be causing this implicit conversion as both the parameter and the field data type "UserName" is varchar(64)?

SP is called from framework like this:

EXEC test @Username=N'webSite.com'

Thank you.

Best Answer

It was all down to collation of the column. It was different from the database's (and the table's) collation. Now changed the column's collation to database's and no more implicit conversion shows up. Have no idea about the internals and why it caused the problem.

Related Solutions

Sql-server – SQL Server – Varbinary Column – Extremely Slow Statistics Update

This can be addressed in two additional ways:

Enable Asynchronous Auto Update Statistics and the database level (not my preference). If your production is stuck because due to this issue then this is the quick fix as it is instantaneous but it globally affects the database and can produce sub-par query plans since the plan is generated prior to the stats update.
```
ALTER DATABASE [DatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT
```
Remove the auto generate stat and replace it with a manually created one with no-recompute. The downside is that, while this is a surgical approach, it takes some time to create the statistic and will cause blocking.
```
DROP STATISTICS [dbo].[TableName].[_WA_Sys_00000004_7D78A4E7]
GO
CREATE STATISTICS [_Manual_Stat_Name] ON [dbo].[TableName]([Column]) WITH NORECOMPUTE;
GO
```

When we were forced to deal with a similar issue we went with option 1 first and later option 2. During a "green zone" we reverted the database wide setting and then replaced the column's statistic with the manually created NORECOMPUTE one.

Sql-server – How to avoid implicit conversion for an Integer column

How to avoid implicit conversion for an Integer column

It is the parameter that has been implicitly converted, not the column.

The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The implicit cast of a tinyint parameter to an int won't cause any problems.

However to avoid having multiple plans in cache for int, smallint, tinyint and get rid of the implicit cast you could explicitly parameterize the query yourself - with a parameter of datatype int rather than having it be parameterized automatically.

EXEC sys.sp_executesql
  N'SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode]
  FROM [AdventureWorks2014].[HumanResources].[Employee]
  where BusinessEntityID = @BusinessEntityID',
  N'@BusinessEntityID INT',
  @BusinessEntityID = 5;

One other alternative would be to block simple parameterisation by adding a redundant AND 1=1 as below. But I don't recommend this as then you will get plans compiled and cached for every different literal value that you pass.

SELECT [BusinessEntityID],
       [NationalIDNumber],
       [LoginID],
       [OrganizationNode]
FROM [AdventureWorks2014].[HumanResources].[Employee]
where BusinessEntityID = 5 
      AND 1=1

Best Answer

Related Solutions

Sql-server – SQL Server – Varbinary Column – Extremely Slow Statistics Update

Sql-server – How to avoid implicit conversion for an Integer column

Related Question