Implicit type conversation and the plan cache

You probably read already something about caveats and catches when relying on implicit type conversation. Here’s another one regarding the plan cache.

Whenever an execution plan is compiled, SQL Server will try to store this plan in the plan cache. That’s because plan compilations are quite expensive in respect of CPU usage and therefore should be minimized. But sometimes this process doesn’t work as expected. Have a look at the following example.

Problem

First, we clear the plan cache

dbcc freeproccache

Now let’s execute the following query:

select * from msdb.dbo.backupfile  where backup_set_id=100000

and see what’s in the plan cache by invoking this query:

select qt.text       ,qs.execution_count   from sys.dm_exec_query_stats as qs        cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

Here’s the result:

 

Ok, no surprise. There’s a cached plan with a usage count of 1. This is as expected.

Now, let’s execute a second query like this:

select * from msdb.dbo.backupfile  where backup_set_id=1000

I expected the cached plan was reused. But surprisingly not! Let’s see what the plan cache reveals now. It looks like this:

As we asked for backup_set_id=1000 this time, our parameter is implicitly converted into a SMALLINT, since SMALLINT is the smallest possible data type a value of 1000 fits into. Consequently a new plan is generated and cached.

We can repeat our experiment with a predicate of WHERE backup_set_id=10 and see another plan is going into the plan cache:

So, we have three plans now, all with an execution count of 1!

As for me, I think this behavior is somewhat strange. The column backupset_id is of type INT, so why not just create one plan with INT as parameter type and stick to this plan?

Solutions

Since implicit type conversation is the root of our problem, we may solve it by avoiding this implicit conversation and cast explicit. We can re-write our queries like this:

select * from msdb.dbo.backupfile  where backup_set_id=cast(100000 as int)   select * from msdb.dbo.backupfile  where backup_set_id=cast(1000 as int) select * from msdb.dbo.backupfile  where backup_set_id=cast(10 as int)

After doing so, there’s only one plan in the cache. But I suppose, you wouldn’t want to introduce those explicit casts to all of your queries. It’s not very readable and looks like bad programming style, doesn’t it?

Another solution comes through sp_executesql:

exec sp_executesql N'select * from msdb.dbo.backupfile                       where backup_set_id=@id'                   ,N'@id int'                   ,@id = 100000   exec sp_executesql N'select * from msdb.dbo.backupfile                       where backup_set_id=@id'                   ,N'@id int'                   ,@id = 1000 exec sp_executesql N'select * from msdb.dbo.backupfile                       where backup_set_id=@id'                   ,N'@id int'                   ,@id = 10

sp_executesql expects parameter declarations and, therefore, no implicit type conversation is involved this time. We see only one cached plan with an execution count of 3, when all of the above three queries have been executed.