Implicit Conversion

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:

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.

CREATE OR ALTER PROCEDURE dbo.TallyVotes
(
    @UserId SQL_VARIANT = 0, @CreationDate SQL_VARIANT = '19000101'
)
AS
    SET NOCOUNT ON;
    BEGIN

        SELECT   v.UserId, v.VoteTypeId, COUNT(*)
        FROM     dbo.Votes AS v
        WHERE    v.UserId = @UserId
                 AND v.CreationDate >= @CreationDate
        GROUP BY v.UserId, v.VoteTypeId;

    END;
GO

CREATE OR ALTER PROCEDURE dbo.TallyVotes

(

@UserId SQL_VARIANT = 0, @CreationDate SQL_VARIANT = '19000101'

)

SET NOCOUNT ON;

BEGIN

SELECT v.UserId, v.VoteTypeId, COUNT(*)

FROM dbo.Votes AS v

WHERE v.UserId = @UserId

AND v.CreationDate >= @CreationDate

GROUP BY v.UserId, v.VoteTypeId;

END;

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

ostress -SNADAULTRA\SQL2016E -d"StackOverflow_2017" -Q"EXEC dbo.TallyVotes @UserId = '6309', @CreationDate = '19000101'" -E -q -n100 -r1 -o"C:\temp\crap"

1	ostress -SNADAULTRA\SQL2016E -d"StackOverflow_2017" -Q"EXEC dbo.TallyVotes @UserId = '6309', @CreationDate = '19000101'" -E -q -n100 -r1 -o"C:\temp\crap"

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.

CREATE INDEX ix_votes_helper ON dbo.Votes(UserId, CreationDate) INCLUDE (VoteTypeId);

1	CREATE INDEX ix_votes_helper ON dbo.Votes(UserId, CreationDate) INCLUDE (VoteTypeId);

Unfortunately, it doesn’t.

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.

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.

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

AWOO!

Clicky clicky

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.