Graduates of my Mastering Index Tuning class will already be familiar with the handful of ways the missing index DMVs and plan suggestions are just utterly insane. Let’s add another oddity to the mix: the usage counts aren’t necessarily correct, either. To prove it, Let’s take MattDM’s Stack Exchange query, “Who brings in the crowds?” He’s querying to see which users’ questions bring in the most new views:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT TOP 50 q.OwnerUserId as [User Link], count(q.Id) as Questions, sum(q.ViewCount/(30+datediff(day, q.CreationDate, datadumptime ))) AS [Question Views per Day] FROM dbo.Posts AS q, (select max(LastAccessDate) as datadumptime from dbo.Users) tmp WHERE q.CommunityOwnedDate is null AND q.OwnerUserId is NOT null AND q.PostTypeId=1 GROUP BY q.OwnerUserId ORDER BY [Question Views per Day] DESC; |
When I run that against the Stack Overflow database without any indexes on the Posts table, SQL Server 2017 CU15 (the latest) helpfully pipes up with a missing index request.
Make that two missing index requests.
And they’re identical twins:
In my head, I hear Clippy saying:
“HEY BUDDY! IT LOOKS LIKE YOU’RE TRYING TO QUERY BY COMMUNITY OWNED DATE AND POST TYPE ID! YOU KNOW WHAT WOULD MAKE YOUR QUERY 97.73% FASTER? AN INDEX ON THOSE FIELDS!”
“OH AND YOU KNOW WHAT…”
“WHILE YOU’RE AT IT, YOU KNOW WHAT WOULD MAKE YOUR QUERY 98.1201% FASTER? AN INDEX ON COMMUNITY OWNED DATE AND POST TYPE ID! DOESN’T THAT SOUND AMAZING? WHAT DO YOU MEAN YOU’VE HEARD THAT BEFORE?”
This artificially inflates the missing index DMVs.
When this query runs just once, sys.dm_db_missing_index_details records not one, but two requests for this same index, so sp_BlitzIndex looks like this:
The query only ran once – but it’s showing 2 uses for that missing index. <sigh>
There are parts of me that would love to see inside SQL Server’s source code to know how this kind of thing happens. The other parts of me are glad that I don’t know.
5 Comments. Leave new
“This is my brother Darryl and this is my other brother Darryl.”
HAHAHA, I recognize that reference.
Your Clippy references made me snort/laugh out loud Brent. Great work as always.
Thanks! I thought about recording audio for this post, hahaha.
You can run parallel queries and not have index contention. 🙂