[TSQL] 搜尋出某張資料表中使用特定Index做Seek的Query之執行計畫

2016-04-20

569
0
[SQL]TSQL語法技巧
2016-04-20

我們可以利用sys.dm_db_index_usage_stats這一個DMV來知道索引的使用狀況，例如哪一個索引被seek幾次scan幾次。但有時我會想要知道到底是哪一些語法使用到這一個索引。

爬了一些文看到的方法就是去搜尋執行計畫的XML，找出XNL中該Table的該Index是被Seek的。這樣一來我們就可以知道哪一些語法是會吃該Table的該Index。

語法如下 :

with tmptb as(
select qt.[text],total_worker_time,plan_handle from sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) qt where qt.[text] like '%TableName%')
select tb.*,qp.query_plan From tmptb tb
CROSS APPLY sys.dm_exec_query_plan(tb.plan_handle) qp
where qp.query_plan.exist('declare namespace 
qplan="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
            //qplan:RelOp[@LogicalOp="Index Seek"
            or @LogicalOp="Clustered Index Seek"]/qplan:IndexScan/qplan:Object[@Index="[IndexName]"]')=1
order by total_worker_time desc

上述語法中TableName請改成您欲搜尋的資料表，IndexName則是填入您欲搜尋索引名稱。

我們簡易測試一下，如下圖所示。建一資料表並一併建立Clustered Index及NonClustered Index。寫入資料後我們對該資料表做一次搜尋，Where條件式則是用Clustered Index。此時可以從下圖中看見SQL是用Clustered Index Seek來搜尋資料。