Querys com sys.dm_exec_query_stats para medição de performance

Com base nos Scripts apresentados no ótimo livro Inside MS-SQL 2005 Query tuning and otimization, realizei algumas modificações nas querys que utilizam a view sys.dm_exec_query_stats para acompanhamento da performance do banco de dados.

Usando o dm_exec_query_stats para retornar as 10 querys com o maior tempo de trabalho.

SELECT TOP 10

    total_worker_time/execution_count AS [Média de custo de CPU],

    execution_count [Número de execuções do plano],

    last_execution_time [Hora da última execução],

    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

      (CASE WHEN statement_end_offset = -1

            THEN LEN(CONVERT(nvarchar(max), text)) * 2

            ELSE statement_end_offset

       END - statement_start_offset)/2)

     FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

ORDER BY [Média de custo de CPU] DESC;

Query que retorna as 10 querys que tomam o maior volume de tempo de CPU.

SELECT TOP 10

    total_worker_time/execution_count AS [Média de custo de CPU],

    execution_count [Número de execuções do plano],

    last_execution_time [Hora da última execução],

    total_physical_reads [Total de leituras físicas],

    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

       (CASE WHEN statement_end_offset = -1

               THEN LEN(CONVERT(nvarchar(max), text)) * 2

               ELSE statement_end_offset

        END - statement_start_offset)/2)

     FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

ORDER BY [Média de custo de CPU] DESC;

Query para retornar as querys (top 10) mais frequentemente executadas

SELECT TOP 10

      total_worker_time [Tempo total de trabalho],

      execution_count [Número de execuções do plano],

      (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

          (CASE WHEN statement_end_offset = -1

                  THEN LEN(CONVERT(nvarchar(max),text)) * 2

                  ELSE statement_end_offset

           END - statement_start_offset)/2)

       FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

ORDER BY execution_count DESC;

Use a query abaixo para acompanhar as querys que estão sendo recompiladas com frequência.

SELECT TOP 10

      plan_generation_num as [Número de execução do plano],

      execution_count as [Número de execuções],

    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

      (CASE WHEN statement_end_offset = -1

            THEN LEN(CONVERT(nvarchar(max),text)) * 2

            ELSE statement_end_offset

       END - statement_start_offset)/2)

     FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

WHERE plan_generation_num >1

ORDER BY 1 DESC;

A query abaixo lista as 10 primeiras querys que geram o maior volume de I/O.

SELECT TOP 10

 (total_logical_reads/execution_count) AS [Média de leituras lógicas],

 (total_logical_writes/execution_count) AS [Média de escritas lógicas],

 (total_physical_reads/execution_count) AS [Média de leituras físicas],

  execution_count,

  plan_handle,

  (SELECT SUBSTRING(text, statement_start_offset/2 + 1,

    (CASE WHEN statement_end_offset = -1

          THEN LEN(CONVERT(nvarchar(MAX),text)) * 2

          ELSE statement_end_offset

     END - statement_start_offset)/2)

   FROM sys.dm_exec_sql_text(sql_handle)) AS [Texto da query]

FROM sys.dm_exec_query_stats

ORDER BY (total_logical_reads + total_logical_writes) DESC;

Lembrando que os scripts acima funcioname apenas no SQL SERVER 2005 ou superior.

Semana que vem, falaremos sobre as diferenças entre atribuições com SET e SELECT. Não perca!