Quais consultas estão utilizando meu índice

Estou dando uma olhada nos scripts que temos como referência para a reunião do SQLServerDF que vai rolar hoje e, por coincidência, um amigo me pegou no messenger para fazer a seguinte pergunta: "Como sei quais são as consultas que estão usando determinado índice?".

Note que ele não quer saber quais são os índices mais ou menos usados (isso temos fácil por DMVs e ele sabe disso), ele quer saber COMO o índice está sendo utilizado.

A maneira que me veio a cabeça foi consultar o plan cache para descobrir os planos de execução que temos guardados e, através do XML, descobrir aqueles que possuem em seu plano o índice que você está analisando.

Para colocar alguma coisa em cache, use o AdventureWorks e faça a consulta "SELECT * FROM Sales.SalesOrderDetail", que vai fazer um scan no índice PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID. Feito isso você já pode consultar a cache de procedimentos para saber quem está usando o índice em questão, usando por exemplo, a consulta (tosca, ok!) abaixo:

SELECT *
FROM sys.dm_exec_cached_plans as ECP
CROSS APPLY sys.dm_exec_query_plan(ECP.plan_handle) AS EQP
CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST
WHERE CAST(EQP.query_plan as varchar(max))
like '%PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID%'

O problema dessa consulta não está somente na ineficiência, mas por procurar pela string, você pode encontrar consultas que não utilizam o índice mas fazem menção ao nome, como essa que acabamos de executar.

Dando uma rápida olhada no XML dos planos de execução, podemos escrever uma consultinha utilizando o método exist() do tipo XML.

SELECT
*,
CASE
EQP.query_plan.exist(N'//*:Object[@Index eq "[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]"]')
WHEN 1 THEN 'Usa o índice'
WHEN 0 THEN 'Não usa o índice'
ELSE 'XML é NULL'
END
FROM sys.dm_exec_cached_plans as ECP
CROSS APPLY sys.dm_exec_query_plan(ECP.plan_handle) AS EQP
CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST

Essa consulta analisa cada plano existente procurando pelo elemento Object (em qualquer posição/namespace no XML) que possua o atributo "Index=[NomeIndice]". Com essa consulta conseguimos tirar os casos onde o nome do índice não é usado no plano efetivamente.

Você também poderia usar o exist() no where da sua consulta.

SELECT
*
FROM sys.dm_exec_cached_plans as ECP
CROSS APPLY sys.dm_exec_query_plan(ECP.plan_handle) AS EQP
CROSS APPLY sys.dm_exec_sql_text(ECP.plan_handle) AS EST
WHERE EQP.query_plan.exist(N'//*:Object[@Index eq "[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]"]') = 1

Legal né?
Tenho que colocar um disclaimer aqui, eu não sei de cabeça o schema (XSD) que rege o XML dos planos de execução, então escrevi consultas genéricas que me parecem funcionar corretamente, mas pode ser que algum caso ou outro fuja a regra. Se você se deparar com isso, conte para nós!
A consulta não é eficiente, mas como você não vai ficar rodando isso a todo momento, serve de base para brincarmos um pouco com o SQL Server.

Com isso vemos claramente que, com a enorme quantidade de informações que o SQL Server nos oferece, basta usarmos um pouco a criatividade para resolver nossos problemas.

[]s
Luciano Caixeta Moreira - {Luti}
Chief Innovation Officer
Sr. Nimbus Serviços em Tecnologia Ltda
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm