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?
asked
How do you run a trace to grab the TSQL query that is using a specific 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%'
Suggest you mark it as the answer if it solved your problem.
