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.
Show!!!
Valeu Balabuch