Silly Execution Plan Warnings

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!

Leave a Reply

Your email address will not be published. Required fields are marked *