In SQL Server, Some Execution Plan Warnings Don’t Make Any Sense

Posted on by Erik Darling

First Time, Long Time

I’ve written about all of these separately in various places, so if you’ve been reading my blog(s) and Stack Exchange answers for a while, these may seem old news.

Of course, collecting them all in one place was inspired by another recent Q&A.

Let’s get going.

Eyeroll

SELECT TOP ( 1 )
       CONVERT(NVARCHAR(11), u.Id)
FROM   dbo.Users AS u;
Seems pretty explicit to me, pal.

Yep. I’d ignore this one all day long.

Squints

DECLARE @NumerUno SQL_VARIANT = '10000000';
SELECT *
FROM   dbo.Users AS u
WHERE  u.Reputation = @NumerUno;
Cheap haircut

That’s awfully presumptuous.

I don’t even have A index on Reputation, nevermind enough index to facilitate an entire Seek Plan.

I’ve seen this catch people off guard. They fix the implicit conversion, and expect an index seek.

Ah well.

Checks Notes

CREATE INDEX tabs
	ON dbo.Comments(UserId);

CREATE INDEX spaces
    ON dbo.Votes(UserId);

SELECT TOP (1) *
FROM   dbo.Comments AS c
JOIN   dbo.Votes AS v
    ON v.UserId = c.UserId
WHERE  c.UserId = 22656;
RFC

The check for this happens at the join. There’s no further down-plan check on the index access operations.

If there were, it’d see this:

Complaint Apartment

Only matching rows come out, anyway. The join predicate is, like, implied in the where clause.

Oh Um Sweatie No No No No No

CREATE INDEX handsomedevil
    ON dbo.Users(Reputation) 
        WHERE Reputation > 1000000;

SELECT COUNT(*) AS records
FROM dbo.Users AS u
WHERE u.Reputation > 1000000;
Ayyyyyyyy

So the simple parameterization thing fires off a warning about a filtered index that we used not being used.

yep yep yep yep yep yep

First Of All, Ew.

Genie In A Bottle

This thing needs some boundaries. Maybe like available memory should figure in or something?

Probably?

Call me, I have lots of great ideas.

Thanks for reading!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

Related Posts