Qual a consulta mais custosa executada?

No dia a dia de um DBA, é extremamente comum a necessidade de identificar quais as consultas mais custosas que são executadas em um servidor de banco de dados SQL Server.

Esse é um trabalho continuo, e que se realizado de maneira proativa pode auxiliar muito na estabilidade e também na boa performance de todas as aplicações que utilizam um servidor de banco de dados SQL Server.

Existem várias maneiras de realizar esta monitoração, como por exemplo utilizando o SQL Profiler, porém uma das maneiras mais rápidas e menos invasivas é através das Dynamic Management Views (DMVs), existentes desde a versão SQL Server 2005.

Atualmente utilizo a seguinte consulta para encontrar os 10 piores comandos executados nas instâncias de SQL Server que administro:

SELECT TOP ( 10 )

     SUBSTRING(ST.text,(QS.statement_start_offset/2)+1,

((CASE statement_end_offset

         WHEN-1 THENDATALENGTH(st.text)

         ELSE QS.statement_end_offset

     END- QS.statement_start_offset )/ 2 )+ 1)AS statement_text ,

     execution_count ,

     total_worker_time / 1000 AS total_worker_time_ms ,

(total_worker_time / 1000)/ execution_count AS avg_worker_time_ms ,

     total_logical_reads,

     total_logical_reads / execution_count AS avg_logical_reads ,

     total_elapsed_time / 1000 AS total_elapsed_time_ms ,

(total_elapsed_time / 1000)/ execution_count AS avg_elapsed_time_ms ,

     qp.query_plan

FROM   

     sys.dm_exec_query_stats qs

CROSSAPPLY

     sys.dm_exec_sql_text(qs.sql_handle) st

CROSSAPPLY

     sys.dm_exec_query_plan(qs.plan_handle) qp

ORDERBY

     total_worker_time DESC

Nesta consulta utilizo a DMV sys.dm_exec_query_stats para conseguir retornar os comandos T-SQL que utilizaram a CPU por mais tempo, ordenando de forma descendente pelo tempo de execução.

Também utilizo a DMV sys.dm_exec_sql_text para extrair o comando executado e a DMV sys.dm_exec_query_plan para retornar o plano de execução gerado pelo otimizador de consulta do SQL Server e que está armazenado no Plan Cache.

Com o resultado desta consulta em mãos, é possível realizar um trabalho de performance bem mais eficiente, já que o processo de tuning será realizado nos comandos T-SQL mais custosos executados no servidor de banco de dados recentemente.

Espero que tenham gostado da dica.

Grande abraço a todos.

Anúncios
  1. Rafael Silva dos Anjos
    30/07/2013 às 11:06

    Excelente post, bem objetivo e de ótima ajuda. Obrigado

  2. 05/08/2013 às 14:11

    Estranho comigo esta aparecendo o erro:

    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near ‘THENDATALENGTH’.

    • Samuel
      13/08/2013 às 12:04

      Separe a palavra THEN de DATALENGTH. O THEN é referente ao CASE.

  1. No trackbacks yet.

Deixe um comentário

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

%d blogueiros gostam disto: