I have noticed a lot of scans on a particular index, can you run a SQL trace to grab the TSQL query that is calling just that index?
If you can get there while the query plan is still in cache, you can list all the queries that use a particular index with the following:
DECLARE @StringToSearchFor VARCHAR(100) SET @StringToSearchFor = '%your_index_name%' SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 st.text AS [SQL] , cp.cacheobjtype , cp.objtype , DB_NAME(st.dbid)AS [DatabaseName] , cp.usecounts AS [Plan usage] , qp.query_plan FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE @StringToSearchFor ORDER BY cp.usecounts DESC
This is from a stored procedure by Ian Stirk at http://www.sqlservercentral.com/articles/Performance/66729/
If the query is in cache you can actually query the execution plan directly using XQuery so that you can search for the specific index name within the scan operations of your procedures. That's what I'd suggest to get at a specific combination of an index and a query. Any other approach using DMV's is going to get you aggregations or generalities.
Thanks. The following ended up doing the trick by querying the cached plans.
SELECT * FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE CAST(qp.query_plan AS NVARCHAR(MAX))LIKE '%INDEXNAME%'
No one has followed this question yet.