While helping a client out with weird performance issues, we isolated part of the code that was producing a whole bunch of bad plans.
At the intersection of bad ideas, there was a cursor looping over a table gathering some data points with a local variable in the where clause.
For more background on that, check out these posts:
- Yet Another Post About Local Variables
- Things SQL Server vNext Should Address: Local Variable Estimates
One might expect the query inside the cursor to be subject to some special rules, but alas, nothing good comes of it.
Eau de Sample
To repro a little bit, we need to create a certainly suboptimal index.
CREATE INDEX p ON dbo.Posts (OwnerUserId);
If you have a lot of single key column indexes, you’re probably doing indexing wrong.
Full demo code is at the end because it’s a bit long, but the end result is five query plans that all share the same bad estimate based off the density vector.
The worst of them looks like this:
And the best of them looks like this:
Over and Over
If you’re getting bad guesses like this over and over again in any loop-driven code, local variables might just be to blame.
That guess of 10 rows of course comes from this calculation:
SELECT density = ( 1 / CONVERT ( float, COUNT(DISTINCT p.OwnerUserId) ) ) * COUNT_BIG(*) FROM Posts AS p
Which, with a little bit of rounding, gets us to the estimate we see in the query plan:
Thanks for reading!
SET STATISTICS XML OFF; SET NOCOUNT ON; DECLARE @CurrentId int; DROP TABLE IF EXISTS #UserIds; CREATE TABLE #UserIds ( UserId int PRIMARY KEY CLUSTERED ); INSERT #UserIds WITH(TABLOCK) ( UserId ) SELECT u.Id FROM dbo.Users AS u WHERE u.Reputation > 850000 ORDER BY u.Reputation DESC; DECLARE counter CURSOR LOCAL STATIC FOR SELECT UserId FROM #UserIds; OPEN counter; FETCH NEXT FROM counter INTO @CurrentId; WHILE @@FETCH_STATUS = 0 BEGIN SET STATISTICS XML ON; SELECT p.PostTypeId, c = COUNT_BIG(*) FROM dbo.Posts AS p WHERE p.OwnerUserId = @CurrentId GROUP BY p.PostTypeId ORDER BY c DESC; SET STATISTICS XML OFF; FETCH NEXT FROM counter INTO @CurrentId; END; CLOSE counter; DEALLOCATE counter;