The DMV sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats give you excellent detailed information about how the indexes are being used and what operations are occurring (select, insert, update, delete) in the database.

But they don’t tell you a particular index or table is being used by which all the queries. So, when you want to tune the existing indexes, want to create a new index, or drop a current index from a table, I am sure would like to know which are queries are using the particular index or table.

In this tip, I am going to share how you can leverage the SQL Server plan cache to get the information about what all the queries are using a particular index or table.

Note: The script will only show the data from the plan cache. So, if any query plan cache has been evicted because of memory pressure or any other reason, you won’t get that query information from the below script.

use <DatabaseName>
go
SELECT OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) +'.'+OBJECT_NAME(stx.objectid, stx.dbid) AS object_name,
  SUBSTRING	(stx.[text],(eqs.statement_start_offset / 2) + 1,
				(CASE WHEN eqs.statement_end_offset =-1 
					THEN DATALENGTH(stx.text) 
					ELSE eqs.statement_end_offset 
					END - eqs.statement_start_offset
				 )/ 2 + 1) AS QueryText,
  CAST(pl.query_plan AS XML) AS sqlplan,
  stx.[text] as complete_text,
  eqs.execution_count,
  eqs.creation_time [compilation time],
  eqs.total_worker_time/execution_count AS avg_cpu_time,
  eqs.total_worker_time AS total_cpu_time,
  eqs.total_logical_reads/execution_count AS avg_logical_reads,
  eqs.total_logical_reads,
  eqs.last_execution_time
FROM sys.dm_exec_query_stats AS eqs
     CROSS APPLY sys.dm_exec_text_query_plan(eqs.plan_handle, 
											 eqs.statement_start_offset, 
											 eqs.statement_end_offset) AS pl
     CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS stx
WHERE pl.query_plan not like '%OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) %'
and pl.dbid=DB_ID()
and pl.query_plan like '%XXXXXXXXX%' ----Replace the "XXXXXXXXX" with index name

Demonstration:

Let’s follow a step-by-step approach to check which all the queries are using a particular index on a table. To demonstrate it, I am going to use WideWorldImporters database.

• Execute the below code under the WideWorldImporters database to generate some random workload. 

USE WideWorldImporters
GO
------Workload 1
declare @LastCutoff	datetime2
declare @NewCutoff	datetime2

select 
@LastCutoff = ValidFrom,
@NewCutoff = ValidFrom
from Application.People
order by NEWID() 
exec Integration.GetEmployeeUpdates @LastCutoff, @NewCutoff
GO 100

------Workload 2
SELECT top 10 FullName, PreferredName FROM Application.People
order by NEWID()
GO 10


------Workload 3
declare @SearchText nvarchar(1000)
declare @MaximumRowsToReturn int = 5

select 
	@SearchText = CONCAT(p.FullName, N' ', p.PreferredName)
from [Application].People p
order by NEWID()

exec [Website].[SearchForCustomers] @SearchText, @MaximumRowsToReturn
go 80

------Workload 4
select top 5 StateProvinceID, SalesTerritory 
from Application.StateProvinces
order by NEWID() 
go 20

• Now, let’s run the provided the above script to check which all the queries are using the table “People”.  In case, you want to check which all the queries are using a particular Index. You can replace the Table name with Index name.

use WideWorldImporters
go
SELECT OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) +'.'+OBJECT_NAME(stx.objectid, stx.dbid) AS object_name,
  SUBSTRING	(stx.[text],(eqs.statement_start_offset / 2) + 1,
				(CASE WHEN eqs.statement_end_offset =-1 
					THEN DATALENGTH(stx.text) 
					ELSE eqs.statement_end_offset 
					END - eqs.statement_start_offset
				 )/ 2 + 1) AS QueryText,
  CAST(pl.query_plan AS XML) AS sqlplan,
  stx.[text] as complete_text,
  eqs.execution_count,
  eqs.creation_time [compilation time],
  eqs.total_worker_time/execution_count AS avg_cpu_time,
  eqs.total_worker_time AS total_cpu_time,
  eqs.total_logical_reads/execution_count AS avg_logical_reads,
  eqs.total_logical_reads,
  eqs.last_execution_time
FROM sys.dm_exec_query_stats AS eqs
     CROSS APPLY sys.dm_exec_text_query_plan(eqs.plan_handle, 
											 eqs.statement_start_offset, 
											 eqs.statement_end_offset) AS pl
     CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) AS stx
WHERE pl.query_plan not like '%OBJECT_SCHEMA_NAME (stx.objectid, stx.dbid) %'
and pl.query_plan like '%People%' ----Replace the "XXXXXXXXX" with index name
and pl.dbid=DB_ID()

Here is the output of the above query. It shows what all the queries are using the table. You can see the “workload 4” is not showing in the output because it is not using the “People” table.

• To check which index of the table is being used, you can click on the query plan of the query, and it will give you detailed information like below. I clicked on the row3 of the above image to show the query is using which index.

I wish this makes your job easier when it comes to troubleshooting of SQL Server or tuning of indexes. Kindly let me know your thought by providing your comment below. 

Happy Learning!