Dica do Mês – Identificando as Top 10 querys mais pesadas e seus respectivos planos de execução

Olá, boa tarde, salve, salve….. tudo bem?

Por um instante até pensei que estava ouvindo aquela conhecida música “Alô, alô realengo aquele abraço……”, mas não era isso não, estou cá com meus botões pensando como começar mais este post, sinceramente esta faltando muita criatividade para tentar fazer algo diferente, mesmo assim, vou tentando quem sabe um dia eu consigo.

Seguindo em frente, falando sério, seja bem vindo a mais um post do meu blog dedicado a sessão “Dica do Mês“, que alias vem recebendo nos últimos dias diversos feedback sobre os atuais posts, bem como, sugestões para novos, mais uma vez obrigado pela participação.

Como a voz do povo é a voz de deus, e se o povo esta pedindo temos que tentar agradar, vou publicar na dica deste mês, um script que eu particularmente uso muito, mas muito mesmo, principalmente quando eu escuto a seguinte frase:  “Nossa como o servidor de banco de dados esta lento….” ou esta outra “O sistema esta lento, muito lento, não consigo fazer nada…..” ao ouvir uma destas frases sinceramente eu fico louco e feliz ao mesmo tempo(kkkkk), louco por muitas vezes o usuário, desenvolvedor, gestor enfim a pessoa que falou não tem noção do que esta dizendo, e feliz porque vou mergulhar de cabeça no lado interno do SQL Server, como eu costumo dizer para meus alunos na lado sobrio e quente de  um servidor ou instância SQL Server, conhecido por muitos como SQL Server Internals.

Alias se conhecer e trabalhar com este lado do SQL Server, posso dizer que não é coisa de outro mundo mas requer muito calma, cuidado e conhecimento, principalmente no que se relaciona as camadas do Database Engine, Storage Engine, Buffer Cache, Query Processor, Query Optimizer, entre outros.

Antigamente poderíamos dizer que esta seria uma tarefa não muito amigável, algo que mudou muito nos últimos anos com a avanço das novas versões do SQL Server e principalmente pela possibilidade de contato com os maiores profissionais do mundo relacionados a banco de dados e SQL Server, dentre os quais destaco: Paul S. Randal, Kalen Delaney, Kimberly L. Tripp, sem se esquecer dos nossos brasileiros Fabricio Catae, Fabiano Amorin, Luciano Moreira, peço desculpas aos outros não citados, mas todos sabem do meu respeito e admiração.

Continuando nossa viagem, e se você que neste momento acabou de se deparar com esta mesma situação que eu relatei anteriormente, por algum motivo de uma hora para outro seu servidor de banco de dados ou até mesmo um determinado sistema começou a apresentar uma possível lentidão, nossa é uma situação bastante difícil e em muitos momento complexa para se decidir em pouco tempo ou tomar alguma possível ação.

É justamente nestes momentos que devemos tentar estabelecer uma linha de raciocínio, uma baseline para darmos início a nossa frente de combate, procurando definir uma forma de análise, mapeando os possíveis riscos e impactos, impactos que normalmente você já conhece e terá que conviver com ele até encontrar a possível causa raiz deste cenário que esta se apresentando.

Causa raiz que por diversas situações ou condições esta se apresentando em seu servidor ou instância SQL Server oriunda da execução de uma ou mais querys consideradas, as quais podem estar gerando um custo de processamento altíssimo para o Database Engine ou Storage Engine em seus componentes que a transforma em uma possível query candidata a participar da lista de querys pesadas.

Você pode estar estranhando isso, mas é exatamente desta forma que o SQL Server nos permite identificar e categorizar nossas querys, onde através de uma análise da complexidade do seu plano de execução, em conjunto com indicadores internos como:

  • Execution Count;
  • Logical Reads;
  • Logical Writes; e
  • Total Elapsed.

Podemos dizer que esta ou outra query esta presente na lista de querys impactantes ao processamento do nosso servidor, ou até mesmo dizer que é uma query pesada e precisa ser analisada e revista toda sua lógica e complexidade de execução.

Há dica de hoje, vai justamente nos ajudar a obter esta lista das top 10 querys consideradas com a maior carga de processamento e permitir apresentar seu plano de execução. E ai isso não é legal, vale a verdade não é uma grande dica que poderá lhe ajudar muito, particularmente falando eu acho uma dica fantástica.

Vamos então conhecer a dica deste mês apresentada abaixo:

— Dica do Mês – Top 10 querys mais pesadas e seus respectivos planos de execução —

SELECT TOP 10

SUBSTRING(qt.TEXT,(qs.statement_start_offset / 2) + 1,((CASE qs.statement_end_offset WHEN 1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END qs.statement_start_offset) / 2) + 1) As ‘Query’,

qs.execution_count As ‘Execution Count’,

qs.total_logical_reads As ‘Total Logical Reads’,

qs.last_logical_reads As ‘Last Logical Reads’,

qs.total_logical_writes As ‘Total Logical Writes’,

qs.last_logical_writes As ‘Last Logical Writes’,

qs.total_worker_time As ‘Total Worker Time’,

qs.last_worker_time As ‘Last Worker Time’,

qs.total_elapsed_time / 1000000 As ‘Total Elapsed Time in seconds’,

qs.last_elapsed_time / 1000000 As ‘Last Elapsed Time in seconds’,

qs.last_execution_time As ‘Last Execution Time’,

qp.query_plan As ‘Query Execution Plan’

FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_logical_reads DESC

Dicadomesabril1
Figura 1 – Resultado da execução da dica do mês.

 


 

 

Note que os indicadores listados anteriormente são justamente colunas existentes na DMV sys.dm_exec_query_stats, sendo esta uma das principais dynamic management view introduzidas no SQL Server a partir da versão 2008, que nos permite obter todos estes dados referentes ao custo, tempo e esforço de processamento realizado pelo SQL Server. Para saber mais sobre esta DMV acesse: https://msdn.microsoft.com/en-us/library/ms189741.aspx

Para que esta mágica funcione também utilizamos uma outra importante e conhecida DMV sys.dm_exec_text, que possui a finalidade de possibilitar obter exatamente a instrução processada por cada query através do handle “identificador” contido na coluna sql_handle, como também, e o plano de execução desta mesma query com base no seu “identificador” armazenado na coluna plan_handle. Sendo esta dmv um élo de ligação entre a sys.dm_exec_query_stats e a sys.dm_exec_query_plan. Para saber mais sobre esta DMV acesse: https://msdn.microsoft.com/en-us/library/ms181929.aspx

Além disso, outro fator muito importante esta relacionado a capacidade de apresentar de forma gráfica o plano de execução pertencente a cada query, fazendo uso da DMV sys.dm_exec_query_plan, outra fundamental e excencial capacidade adicionada ao SQL Server 2008. Para saber mais sobre esta DMV acesse: https://msdn.microsoft.com/en-us/library/ms189747.aspx

 


Muito bem, chegamos ao final de mais uma dica do mês, tenho a certeza que você gostou deste post, mais uma vez agradeço sua visita, comentários, sugestões, enfim sua participação e interesse.

Caso você ainda não tenha acessado os posts anteriores desta sessão, não se preocupe utilize os links listados abaixo e bom divertimento:

Um grande, meu muito obrigado, nos encontramos no próximo mês com mais uma dica do mês.

Até lá.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s