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

Gerar registros em forma de colunas? Peça ajuda ao XML!

Bom dia pessoal.
Já vi muita pergunta em fóruns onde o pessoal vive tentando arranjar um jeito de mostrar uma série de registros em uma só coluna, separado por vírgula ou sei lá. Aproveitei uma thread que estava rolando no MSDN para usar de base para esse pequeno artigo...
O problema era pegar a consulta abaixo e retornar o resultado em somente uma linha:

USE MSDB
go

SELECT TOP 2 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'backupset'

-- Consulta retorna:
backup_set_id
backup_set_uuid

-- Resultado desejado:
backup_set_id, backup_set_uuid

Você pode fazer isso com cursores ou inventar outra maluquice qualquer, mas não é nada elegante. Lendo um livro do Itzik Ben Gan eu vi uma abordagem bem elegante que ele propunha e passei a adotá-la em meus treinamentos e dicas.

A consulta que retorna o esperado pode ser escrita da seguinte forma:

SELECT STUFF(
(SELECT TOP 2
N',' + QUOTENAME(COLUMN_NAME) AS [text()]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'backupset'
FOR XML PATH('')), 1, 1, N'')
go

Aqui você utiliza o FOR XML PATH('') para gerar um XML sem elemento por registro e usa a função text() para recuperar somente o texto do elemento, dispensando as tags que seriam o nome da coluna. Depois é só remover a primeira vírgula e pronto!

Gostou da solução? Eu sim... :-)
Aqui está a thread do MSDN para consulta: http://social.msdn.microsoft.com/Forums/pt-BR/transactsqlpt/thread/35db803c-44ce-4007-8cef-9b36801d86dc/?prof=required

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