Descobrindo as queries que precisam de índices

Olá pessoal, estive um pouco afastado do blog, mas estou de volta e com uma dica legal. Para quem ainda  não sabe é possível visualizar no SQL Server as sugestões de índices com uma série de informações, como por exemplo quando esse índice foi acessado pela última vez, quantidade de seeks que houve nesse índice que ainda não existe, ou até mesmo o impacto que ele terá caso seja criado. O SQL Server consegue armazena temporariamente esses dados até que a instância do SQL Server seja reiniciada e quem gera essas informações é o QO(Query Optimizer) ou Otimizador de Querys. Eu confesso que por várias vezes eu já executei a consulta abaixo para ver as sugestões, mas nunca criei um índice por não saber qual query o usaria esse índice. Segue a query de sugestões(Não me lembro onde achei):

SELECT migs.avg_user_impact
,migs.last_user_seek
,mid.[statement] AS [Database.Schema.Table]
,mid.equality_columns
,mid.inequality_columns
,mid.included_columns
,migs.unique_compiles
,migs.user_seeks
,migs.avg_total_user_cost
FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY avg_user_impact DESC

Para maiores informações sobre o Missing Index Details veja aqui: http://msdn.microsoft.com/en-us/library/ms345434.aspx Cansado de ver as sugestões dos índices e sem saber quais queries seriam afetadas eu dei mais uma pesquisada na internet e achei uma query que busca dentro do XML dos planos de execuções que estão armazenados buscando somente pelos planos com sugestão. Segue a query original que faz a busca nos cached plans:

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan',N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
SELECT ECP.[usecounts]
,ECP.[refcounts]
,ECP.[objtype]
,ECP.[cacheobjtype]
,EST.[dbid]
,EST.[objectid]
,EST.[text]
,EQP.[query_plan]
FROM sys.dm_exec_cached_plans AS ECP
CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
WHERE ECP.[usecounts] > 1 -- Plan should be used more then one time (= no AdHoc queries)
AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
ORDER BY ECP.[usecounts] DESC

Para maiores informações sobre essa query vejam aqui: http://gallery.technet.microsoft.com/scriptcenter/Get-all-SQL-Statements-9af68abc Para facilitar eu adicionei o campo Impact que mostra o impacto positivo que esse índice terá sob a query e coloquei uma busca para por exemplo filtramos apenas os índices sugeridos que terão mais de 90% de impacto positivo com sua criação. Segue minha versão da query:

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan',N'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ShowPlan)
SELECT 
ECP.[usecounts]
,ECP.[refcounts]
,ECP.[objtype]
,ECP.[cacheobjtype]
,EST.[dbid]
,EST.[objectid]
,EST.[text]
,EQP.[query_plan]
,EQP.[query_plan].value(N'(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','[decimal]') as Impact
FROM sys.dm_exec_cached_plans AS ECP
CROSS APPLY sys.dm_exec_sql_text(ECP.[plan_handle]) AS EST
CROSS APPLY sys.dm_exec_query_plan(ECP.[plan_handle]) AS EQP
WHERE ECP.[usecounts] > 1 -- Plan should be used more then one time (= no AdHoc queries)
AND EQP.[query_plan].exist(N'/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup') <> 0
AND EQP.[query_plan].value(N'(/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]','[decimal]') > 90
ORDER BY ECP.[usecounts] DESC

Uma observação importante: Nunca levem essas sugestões como regras, pois nem sempre elas são o melhor caminho para melhorar o desempenho de uma consulta. Eu indico que primeiramente entendam o que a consulta faz de fato, analisem o plano de execução e crie você mesmo os índices e após isso é legal comparar com o que o SQL Server sugere, ou caso queiram criar de cara o índice eu sugiro que criem e vejam se o índice está sendo usado corretamente. Espero que gostem e que isso possa ser útil  no dia a dia.