特定のテーブルに張ってある各インデックスって、どんなクエリ実行によってアクセスされているのか分からないことがあると思います。
そういうときに、どうにかDMVを使ってインデックスとクエリを紐付けられないかなと試行錯誤してみました。
インデックスの最新のアクセス日時と、クエリの最新の実行時間をjoinでくっつけることで、それなりの精度で取得できるクエリがつくれました。
declare @TableName varchar(100)
set @TableName = 'テーブル名'
select
*
into
#TmpStats
from
(
select
OBJECT_NAME(i.object_id) as tableName
,i.name as indexName
,ps.row_count as rowCnt
,ps.reserved_page_count * 8.0 / 1024 as 'size(MB)'
,type_desc
,us.*
from
sys.dm_db_partition_stats as ps
left join sys.indexes as i on ps.object_id = i.object_id and ps.index_id = i.index_id
left join sys.dm_db_index_usage_stats as us on ps.object_id = us.object_id and ps.index_id = us.index_id
where
OBJECT_NAME(i.object_id) = @TableName
--last_execution_timeとテーブルへのアクセス日時でぶつける。完璧な精度ではないけど
) as A
join
(
select
total_worker_time / qs.execution_count / 1000 as 'Average CPU used(ms)'
,total_worker_time / 1000 as 'Total CPU used(ms)'
,qs.execution_count as 'Execution count'
,SUBSTRING(qt.text, qs.statement_start_offset / 2, (
case
when qs.statement_end_offset = - 1
then LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
else qs.statement_end_offset
end - qs.statement_start_offset
) / 2) as 'Individual Query'
,qt.text as 'Parent Query'
,DB_NAME(qt.dbid) as 'DatabaseName'
,last_execution_time
,creation_time
,total_elapsed_time / 1000 as 'Total Duration(ms)'
,total_elapsed_time / qs.execution_count / 1000 as 'Average Duration(ms)'
,plan_handle
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
where
qt.text like '%' + @TableName + '%'
) as b on a.last_user_seek = b.last_execution_time
or a.last_user_scan = b.last_execution_time
or a.last_user_lookup = b.last_execution_time
select
*
from
#TmpStats
cross apply sys.dm_exec_query_plan(plan_handle) as qp
drop table #TmpStats
間違ったレコードも取れてきちゃいますが、、もっと良い方法をご存知の方がいれば教えていただけると嬉しいです。