Yet Another Way Missing Index Requests are Misleading

July 2, 2019

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:

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;

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:

HEY BUDDY!

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.