I'm being asked to provide historical proof before I can maintain indexes - which is fine - over the past year I've kept a table full of index usage statistics for all the tables on the server.

I show some indexes that are barely used over the last year, and they're large sizes, and I have been asked which queries used those indexes. Can I show them, can I prove that they won't be adversely affected or can we mitigate the effects.

Now, I know I am on SQL Server 2014, so I don't have the QueryStore, but I can look up Query Plans in the Cache... only it's difficult interpreting the XML to see if the index is being updated/inserted or if it's being used for query. The problem here is, the cache is flushed regularly, and no references to these indexes are in it.

Any ideas on how on earth I can, short of waiting another year and saving the entire query cache regularly to a table, show which queries/stored procedures have used an index?

  • If you have the usage stats, can't you see a pattern to the usage of the ones you want to do work on (I assume drop?)? For example, for low usage indexes, it could be a monthly query that uses them. Or more likely an ad-hoc query that someone wrote. I would think that a week or so of query plans would be enough to capture 95% of your "typical" queries. And any query that runs less than 6 weeks should definitely be looked at when/if it becomes a problem again. Save the index definitions so you can re-create them if needed. Jun 27, 2019 at 13:26
  • For me, yes. The folks I work with though, want to see usage data on indexes that aren't used regularly at all - including indexes with 5 reads across a year. Which, I think most of us would say is overkill, but I get arguments of "Well, if those handful of times is critical then..." I want to do enough due diligence that I can argue that I have done the best I can to provide the data, and there isn't more I could have done.
    – crucible
    Jun 28, 2019 at 0:06
  • I think you are looking for a technical solution to a policy problem. I don’t know of any way to get the information you want without scraping it out of the system every for the given interval. I would explain that gathering the information requested is prohibitively impactful, especially for an entire year. But unless you can convince the business it doesn’t seem like it’s going to happen. Jun 28, 2019 at 0:38
  • I don't disagree, I just have no argument that will appease these intractable foes :)
    – crucible
    Jun 28, 2019 at 7:16


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.