Quite a bit, I find myself working with people who are able to change indexes, but unable to change queries.
Even making small, sane changes would nix their support, should they ask for it. I do sometimes have to laugh at the situation: if support were that great, they wouldn’t need me, and if we made the change, they probably wouldn’t need support.
Oh well, though. Keep me employed, ISVs.
When we are allowed to change indexes, sometimes we can fix problems enough to avoid needing to change the code.
Let’s start by creating two different indexes. They have the same key columns, just in different order.
CREATE INDEX v ON dbo.Votes (UserId, CreationDate); CREATE INDEX vv ON dbo.Votes (CreationDate, UserId);
The query that we care about it this one:
SELECT c = COUNT_BIG(*) FROM dbo.Votes AS v WHERE ISNULL(v.UserId, 0) > 0 AND v.CreationDate >= '20180601';
I know, you’re smart, you’re savvy, you’d never write a query like this. But I see it constantly.
If we compare query performance using the two different nonclustered indexes, the one that leads with CreationDate is the clear winner.
The main advantage of a Seek here is we’re able to seek to a much smaller range of rows first, and then apply the non-SARGable predicate to UserId.
Obviously, scanning 182,692,000 rows is a bit slower than seeking to 32,411,155 rows and applying the residual predicate.
If you can rewrite queries like this, you absolutely should. That people still write queries like this is a sad testament to… Well, I’m not sure what.
In tomorrow’s post, we’ll look at how dynamic SQL can help ward off non-SARGable predicates.
Thanks for reading!
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.