Rumors Of My Demise
Let’s talk about some common hints that people use to fix parameter sniffing:
- RECOMPILE: Won’t work here to get us a better plan for VoteTypeId 5, because it sucks when the optimizer knows what’s coming
- OPTIMIZE FOR UNKNOWN: Works like once every 5 years, but people still bring it up, and really sucks here (picture below)
- OPTIMIZE FOR (VALUE): Plan sharing doesn’t work great generally — if we were gonna do this, it’d have to be dynamic
This is what happens when we optimize for unknown. The density vector guess is 13,049,400.
That guess for Vote Types with very few rows ends up with a plan that has very high startup costs.
This version of the query will run for 13-17 seconds for any given parameter. That sucks in zero gravity.
Pictured above is the plan for VoteTypeId 4, which previously finished sub-second using Plan 1 and Plan 2.
With those out of the way, how can we fix this thing?
In some circumstances, a #temp table would help if we pre-staged rows from Votes.
The problem is that for many calls, we’d be putting between 7 and 130 MILLION rows into a temp table.
Not my idea of a good time.
But what about the other part of the query?
If count up distinct OwnerUserIds, we get about 3.2 million.
Better, we can reduce the rows further in the procedure with an EXISTS to Votes (I’ll show you that in a minute).
SELECT COUNT_BIG(DISTINCT p.OwnerUserId) AS records --3,236,013 FROM dbo.Posts AS p JOIN dbo.Badges AS b ON b.UserId = p.OwnerUserId WHERE p.PostTypeId = 1
That’s not too bad, depending on:
- How frequently it runs
- How concurrently it runs
- How overwhelmed tempdb is
- Your Mom
That gives us:
CREATE OR ALTER PROCEDURE dbo.VoteSniffing ( @VoteTypeId INT ) AS BEGIN SET XACT_ABORT, NOCOUNT ON; SELECT DISTINCT p.OwnerUserId INTO #p FROM dbo.Posts AS p JOIN dbo.Badges AS b ON b.UserId = p.OwnerUserId WHERE p.PostTypeId = 1 AND EXISTS ( SELECT 1/0 FROM dbo.Votes AS v WHERE v.UserId = p.OwnerUserId AND v.VoteTypeId = @VoteTypeId ); SELECT ISNULL(v.UserId, 0) AS UserId, SUM(CASE WHEN v.CreationDate >= '20190101' AND v.CreationDate < '20200101' THEN 1 ELSE 0 END) AS Votes2019, SUM(CASE WHEN v.BountyAmount IS NULL THEN 0 ELSE 1 END) AS TotalBounty, COUNT(DISTINCT v.PostId) AS PostCount, @VoteTypeId AS VoteTypeId FROM dbo.Votes AS v WITH(FORCESEEK) WHERE v.VoteTypeId = @VoteTypeId AND NOT EXISTS ( SELECT 1/0 FROM #p AS p WHERE p.OwnerUserId = v.UserId ) GROUP BY v.UserId; END; GO
Which works pretty well across all calls, and avoids the primary issue with VoteTypeId 5.
I’m generally happy with this, with the slight exception of VoteTypeId 8. Yeah, it beats the pants off of when we sniff Plan 2, but it’s about 7 seconds slower than when we get Plan 1.
I pulled the 17 minute execution from this graph for Plan 2/VoteTypeId 5, too, because it’s so distracting. Not having to worry about that thing is a trade off I’m willing to make for Plan 3 being about a second slower than Plan 1.
Not bad for a lazy Sunday afternoon of blogging, though.
Save One For Friday
Query tuning in SQL Server 2019 isn’t always a whole lot different from performance tuning other versions of SQL Server.
You have some more help from optimizer features (especially if you’re on Enterprise Edition), but they don’t solve every problem, and you can run into some very common problems that you’re already used to solving.
You may even be able to use some very familiar techniques to fix things.
In tomorrow’s post, I want to look at a quirk that would have thrown us way off course to explore on our way here.
Thanks for reading!
This week I’m having a sale on my SQL Server 2019 course, normally $99.95.
If you want to see the entire thing, it’s available this week for just $19.99.
All you have to do is add it to your cart, and the discount will be applied at checkout.
If you like what you see here, sign up for my email list to get 50% off your next purchase.