Blitz Result: Implicit Conversion Found in the Plan Cache

This is when you mismatch data types in a WHERE clause or JOIN condition, and SQL Server needs to convert one on the fly.

The penalty you pay here is that indexes won’t be used efficiently, you’ll burn CPU in the conversion process, and in the case of inadequate indexing, no missing index request will be logged. Now, SQL Server’s missing index requests aren’t anywhere near perfect, but they’re a good place to start identifying cries for help from the optimizer.

This is what the Votes table looks like:

This is gonna go poorly.

To take the path of least resistance, let’s create a stored procedure with obviously mismatched datatypes.

I’m using SQL_VARIANT here, when the table datatypes are INT for UserId, and DATETIME for CreationDate.

If I use ostress, and set up 100 users to run this proc at the same time, my server immediately pegs out CPUs.

It hurts when I do this

This is running on my home server, which is pretty decent.

Now, you’d think that an index specifically for this query would keep CPUs from pegging.

Unfortunately, it doesn’t.

Better, but…

Now, the index does help — the ostress process runs for about half the time.

Of course, nothing beats the correct data types. If we alter our proc to use the correct ones (INT and DATETIME), you can’t even tell when the proc is running.

Careless Whisper

Of course, no one would actually use SQL_VARIANT as a datatype and expect to be taken seriously. But you don’t have to!

There are all kinds of datatype clashes that can cause issues.

In the query plan, you’ll see this mess of information that’s not terribly easy to decode.

And what is this?

If you use sp_BlitzCache, we decode it all for you.

AWOO!

Clicky clicky

Reader’s Digest

This part of our SQL Server sp_Blitz script checks the plan cache looking for resource-intensive queries that are doing implicit conversions.

To Fix the Problem

Read about Identifying and Correcting SQL Server Implicit Conversions, then review the execution plans found by sp_Blitz.  You can often change the query parameters or variable datatypes to make them match the table.

Return to sp_Blitz or Ask Us Questions

Menu
{"cart_token":"","hash":"","cart_data":""}