quinta-feira, 4 de março de 2010

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

3 comentários:

  1. Excelente LUTI. Não encontrei nenhum artigo falando sobre isso na NET.

    Abraços

    Demétrio Silva

    ResponderExcluir
  2. Muito bom,valeu mesmo a dica!

    Felipe Santana

    ResponderExcluir
  3. Luti,
    Como você ja deu a base para descobrir,segue a query mais aperfeiçoada que eu montei,lembrando que eu quebrei a cabeça em algo que não sabia,os valores dentro do XML é case sensitive,então tome muito cuidado,execute a query abaixo e depois me fala o resultado =)

    SELECT ecp.bucketid,
    ecp.refcounts,
    ecp.usecounts,
    (convert(money,ECP.size_in_bytes))/1024 as size_in_Kbytes,
    ecp.cacheobjtype,
    ecp.objtype,
    eqp.query_plan,
    est.text
    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('declare namespace ns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
    /ns:ShowPlanXML/ns:BatchSequence/ns:Batch/ns:Statements/ns:StmtSimple/ns:QueryPlan/
    ns:RelOp/ns:IndexScan/ns:Object[@Index = "[PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]"]') = 1


    Estou aguardando,parabéns pello post e um abraço.
    Fernando Garcia
    PASS 2010 here we go!!!!!!

    ResponderExcluir