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?