I am using a script to review missing indexes on various SQL Servers.
Most of the time, it works just fine.
Sometimes, however, certain DMVs that are part of the script, just don't return any rows.
Usually, the main issue is sys.dm_db_missing_index_group_stats.
1. I know that restarting the service clears DMVs - that's not it :-)
2. I know I need the VIEW SERVER STATE permission - that's not it either :-)
3. I know I need actual missing indexes:
select * from sys.dm_db_missing_index_details
returns 573 rows, yet select TOP 1 * from sys.dm_db_missing_index_group_stats
returns 0 rows.
This renders all the various missing index scripts useless, because the JOIN doesn't work, and so the script doesn't return any results.
I keep running into servers with this issue, and so far, only restarting the service has resolved it. Data collection starts from scratch, and within just a few minutes, I get data in the sys.dm_db_missing_index_group_stats.
Check back a few days, weeks or sometimes months later, and the DMV again returns no results - same user, same DB, etc.
I have Googled the heck out of this issue and can't find a solution. It is frustrating, since missing indexes are often a major issue of the application I am troubleshooting on many different SQL Servers.
Any help would be greatly appreciated :-)