This Query Exercise was very different: I didn’t ask you to solve a particular problem. I pointed out that I’ve heard advice that SELECT MAX is faster than SELECT TOP 1, and that’s not quite true. I asked you to find factors that would cause these two queries to get different execution plans:
SELECT TOP 1 LastAccessDateFROM dbo.UsersORDER BY LastAccessDate DESC;SELECT MAX(LastAccessDate)FROM dbo.Users;
In the exercise post, I showed that with a nonclustered rowstore index like this, whose leading column is not LastAccessDate:
CREATE INDEX Location\_LastAccessDateON dbo.Users(Location, LastAccessDate);
That would give them different execution plans, leading to TOP 1 being faster because it went parallel, while MAX stayed single-threaded and ran longer.
I tell you what, when I was writing that blog post, the hardest thing by far was not to give away too many answers. I worded that sentence above really, really carefully because right there in that sentence alone, there’s a factor that changes the answers. We could just change something about the environment so that the MAX query goes multi-threaded too (or the TOP 1 query goes single-threaded!)
Cost Threshold for ParallelismContinuing with the above example, let’s change Cost Threshold for Parallelism from the common 50 down to, say, 25:
EXEC sys.sp\_configure N'cost threshold for parallelism', N'25'GORECONFIGUREGO
And then run our two queries again. Now, both of them go parallel, as shown in the actual execution plans:
Now both queries qualify for parallelism, and the MAX is faster. On the flip side, if we raise CTFP to a much higher number, like 500, the new execution plans are… wait… hang on a second here…
How does the TOP 1 query still have parallelism? Let’s hover our mouse over the SELECT and examine its Estimated Subtree Cost:
How’s that possible? If my Cost Threshold for Parallelism is 500, how can a query with a cost of 499.193 go parallel? Well, there’s a trick: we’re looking at the estimated cost of the parallel query, not the serial one. To see the cost of the serial query, add an OPTION (MAXDOP 1) hint to it:
SELECT TOP 1 LastAccessDateFROM dbo.UsersORDER BY LastAccessDate DESC OPTION (MAXDOP 1);
And we can see that the serial cost is a whopping 977 query bucks:
So that’s why the query goes multi-threaded. Parallelism: it’s a hell of a drug. Alright, let’s reset the playing field before we try other factors:
EXEC sys.sp\_configure N'cost threshold for parallelism', N'50'GORECONFIGUREGODropIndexes;GO
Columnstore IndexesThey’re great for aggregate queries like MAX, so let’s slap one on and see how it affects performance:
CREATE NONCLUSTERED COLUMNSTORE INDEX LastAccessDateON dbo.Users(LastAccessDate);GOSELECT TOP 1 LastAccessDateFROM dbo.UsersORDER BY LastAccessDate DESC;SELECT MAX(LastAccessDate)FROM dbo.Users;
The actual query plans look similar in the sense that they have the same operator, but the devil’s in the details on this one:
The TOP 1 uses a sort, which sounds bad, because it sounds like it would sort all 8,917,507 rows – especially with that monster arrow coming out of the columnstore index scan operator. However, that arrow doesn’t mean jack, as we explain in the Fundamentals of Columnstore training class.
The bottom line is that the TOP 1 uses 93ms of CPU time and runs in 205ms. The MAX uses 16ms of CPU time and runs in 97ms. The MAX wins both ways here, but it’s not a dramatic win – most folks aren’t going to complain too much about the difference between these two plans.
However, there’s a catch to this comparison: my database happens to be in SQL Server 2016 or newer compatibility mode for this one. Watch what happens when we introduce yet another variable into this experiment…
Compatibility LevelI’ve still got the columnstore index in place, but let’s drop back to 2014 compatibility level:
ALTER DATABASE [StackOverflow] SET COMPATIBILITY\_LEVEL = 120
And then check our new actual query plans:
Sometimes, smaller plans are better. However, not in this case: the wide TOP 1 plan finishes in just 351ms, but the seemingly-simple MAX plan takes a whopping 2.2 seconds to run!
This comparison is also a great reminder that the percentage query costs on plans are absolutely useless and meaningless! I catch people saying, “The bottom query looks better because it’s only 4% of the cost,” but that’s just garbage:
It drives me crazy that Microsoft even includes this junk in query plans in the year 2024. They’re doing a disservice with that number.
What We Learned in This ExerciseI’ve only covered a few variables in the equation that make TOP 1 and MAX perform differently. For more, check out the comments on the Query Exercise post.
Database servers have a butterfly effect: even the slightest change, seemingly unrelated to anything else, can affect query performance all over the place. I’m not saying you have to test everything before you change anything at all – the real world is just too busy and complicated to do that.
That butterfly effect should teach you 3 things: