Which objects are using those barely used indexes, that you want to get rid of?

Indexes in SQL Server are obviously critical, but easy to overuse - to the point that the indexes themselves cause problems. For instance, you may have tables with indexes that are almost, but not quite, identical. Figuring out which queries are using specific indexes can be extremely helpful in consolidating indexes.

The problem is, there's not an easy way to do this. The standard advice, when figuring out which indexes are used and which are not, is to query against sys.dm_db_index_usage_stats. That advice is great, as far as it goes. However, it only tells you if an index is used, and whether it's used for a scan, or a seek. It doesn't tell you which objects/queries are using the index.

But what if you have two indexes that are very quite similar, and each have some usage? You suspect they could be consolidated. You need to know which queries are using each index. 

Unfortunately sys.dm_db_index_usage_stats will leave you hanging here. It'll tell you whether an index is being used, but not which specific object/query is using the index.

In order to do that, you need to use Query Store data. Query Store is a SQL Server component that tracks all kinds of very useful performance information, so it's important to learn about. By default it's turned off, so you'll need to turn it on. 

Here's a query you can use to figure out specifically which objects are using an index. It needs to be run on the production server, and can be resource intensive, so consider running it at a time when there's not much activity. Substitute the index name IX_IndexName for your specific index name. 

 ;With FilteredPlans as (  
   SELECT plan_id   
   FROM sys.query_store_plan  
   WHERE query_plan like '%IX_IndexName%'  
 )  
 SELECT distinct  
   ObjectName = Object_Name(q.object_id)  
   ,q.query_id   
   ,p.plan_id   
   ,qt.query_sql_text  
 FROM sys.query_store_runtime_stats rs  
   JOIN sys.query_store_plan p ON p.plan_id = rs.plan_id  
   JOIN sys.query_store_query q ON q.query_id = p.query_id  
   JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id  
 WHERE   
      p.plan_id in (Select plan_id from FilteredPlans)  

Note that some of the usage of the index that comes up will be when the index is updated, because rows have been modified. This is when the underlying table has been updated, deleted, etc. You can ignore those, because that's just maintenance to keep the index up to date, and not useful for an index that's not being used.