Achando a Query com problema nas DMV´s de Missing Indexes


 

Pessoal,

Um grande problema que temos nas dmv´s de missing indexes é que infelizmente não conseguimos linkar a informação destas com a sys.dm_exec_request por exemplo, ou com a sys.dm_exec_sql_text pois não temos nem a o session_id ,sql_handle ou outra coluna que faça este join.

Uma saida é usar os planos que estão em cache, pois no SQL 2008 sabemos que quando pedimos o plano no SSMS e está faltando algum índice este aparece em verde escrito “Missing Index”.

Pois esta informação é retirada das DMVs (eu não tenho certeza disso, mas todos os testes que fiz o que é mostrado no SSMS é a mesma coisa que as DMVs)

a

Infelizmente o Plano tem que estar em cache pra isso.

Como este palavra  “Missing Indexes” faz parte do plano e se você pegar o XML ela também estará lá, podemos filtrar pelos planos que tem a palavra “Missing” neles. Se você fizer um join nas dmv´s de missing indexes com a sys.objects, sys.dm_db_partition_stats consegue trazer o nome da tabela.

Vejam a SP Util_MissingIndexes que falo no meu artigo de Reduzindo IO com as DMV´s de Missing Indexes – Projeto Real, nela você tem todas as informações necessárias. Pode jogar o resultado dela pra uma temporaria e fazer o join com o select abaixo, ou incorporar este select na procedure. A gosto do Freguês !!!!

A query pra achar é esta :

  1: SELECT total_worker_time/execution_count as AvgCPU  
  2: , total_elapsed_time/execution_count as AvgDuration  
  3: , (total_logical_reads+total_physical_reads)/execution_count as AvgReads 
  4: , execution_count   
  5: , substring(st.text, (qs.statement_start_offset/2)+1 , ((case qs.statement_end_offset when -1 then datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as texto  
  6: , qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/@Impact)[1]' , 'decimal(18,2)') * execution_count AS TotalImpact
  7: , qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Database)[1]' , 'varchar(100)') AS [Database]
  8: , qp.query_plan.value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan"; (/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple/QueryPlan/MissingIndexes/MissingIndexGroup/MissingIndex/@Table)[1]' , 'varchar(100)') AS [Table]
  9:     
 10: from sys.dm_exec_query_stats qs
 11: cross apply sys.dm_exec_sql_text(sql_handle) st
 12: cross apply sys.dm_exec_query_plan(plan_handle) qp
 13: where cast(query_plan as varchar(max)) like '%missing%'
 14: order by TotalImpact desc
 15: 
 16: 

Original :

Statistics IO – Jason Massie – Missing Indexes from a Different Angle

No caso da minha query acima, o resultado foi :

c

Não é o melhor dos mundos, mas já é um começo !!!!

Não testei com o SSMS do 2005. Somente com o banco em compatiblidade 90 e 100 usando o SSMS do 2008.

Obrigado ao Jorge Segarra (@SQLChicken) que me ajudou nesta busca !!!!

Abraços !!!!

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in SQL SERVER EM GERAL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s