O cerne da query que coloquei no post anterior é a DMV sys.dm_exec_query_stats, que contém uma série de dados relevantes para a avaliação das consultas no cache, porém ela falha em alguns pontos. Vou apresentar alguns deles para apoiar a sua decisão, para ver se o melhor é usar o Profiler, fazer uso da DMV ou outra ferramenta:
Queries com a opção RECOMPILE não são capturadas
Não há muito o que dizer além do que está dito no título. Se você faz uso de queries com RECOMPILE, a DMV não vai armazenar informações pertinentes à sua execução e podem ser justamente as consultas causando a sobrecarga no seu banco de dados.
Queries estruturalmente similares não podem ser agrupadas
Isso é um problema maior no SQL Server 2005 do que no 2008, mas para explicar o problema vou lançar um exemplo. Digamos que executamos duas queries simples, como as que coloco abaixo:
SELECT Nome FROM Tabela WHERE id = 1
SELECT Nome FROM Tabela WHERE id = 2
Tanto no SQL Server 2005 quanto no SQL Server 2008, ele vai armazenar a instrução de maneira isolada no cache. Então no campo text retornado pela DMF sys.dm_exec_sql_text , não poderemos usar um GROUP BY, COMPUTE ou qualquer outra função de agrupamento pois em um tem o trecho "id = 1" e no outro "id = 2". No SQL Server 2008, tem algo bem legal que permite que consigamos agrupar as duas queries, que são os novos campos query_hash e query_plan_hash retornados pela DMV sys.dm_exec_query_stats. Estes dois campos geram um hash da consulta desconsiderando os parâmetros, permitindo que você agrupe as consultas estruturalmente iguais mas com parâmetros diferentes.
Algumas informações de execução da procedure não são consideradas
Alguns recursos de temporização como o WAITFOR não são considerados nas DMV e aparecem no Profiler.
Existem alguns outros pontos, mas acredito que os três pontos acima são os de maior criticidade. E até pegando o ponto do agrupamento, dá para alterar a query anterior, desta vez somente para SQL Server 2008, para fazer uso do query_hash e ter estatísticas acerca de queries estruturalmente idênticas. Porém, dica de outro, a Microsoft já lançou uma ferramenta muito legal que já faz isso pra você:
Query Hash Statistics
E cumprindo o que prometi no post anterior, aí vai uma lista de coisas que dá para se analisar com a sys.dm_exec_query_stats no SQL Server 2008 R2. Vou colar a tabela do MSDN (ver artigo aqui) e adicionar alguns comentários em vermelho para deixar alguns pontos mais claros.
| Nome da coluna | Tipo de dados | Descrição |
|---|---|---|
| sql_handle | varbinary(64) | É um token que se refere ao lote ou procedimento armazenado de que a consulta faz parte. Pode ser usado sql_handle, junto com statement_start_offset e statement_end_offset, para recuperar o texto SQL da consulta, chamando a função de gerenciamento dinâmico sys.dm_exec_sql_text. |
| statement_start_offset | int | Indica, em bytes, começando com 0, a posição inicial da consulta que a linha descreve dentro do texto de seu lote ou objeto persistente. |
| statement_end_offset | int | Indica, em bytes, começando com 0, a posição final da consulta que a linha descreve dentro do texto de seu lote ou objeto persistente. Um valor de -1 indica o fim do lote. |
| plan_generation_num | bigint | Um número de sequência que pode ser usado para distinguir entre instâncias de planos após uma recompilação. |
| plan_handle | varbinary(64) | Um token que se refere ao plano compilado de que a consulta faz parte. Este valor pode ser transmitido à função de gerenciamento dinâmico sys.dm_exec_query_plan para a obtenção do plano de consulta. |
| creation_time | datetime | Hora em que o plano foi compilado. |
| last_execution_time | datetime | Hora do início da execução do plano. |
| execution_count | bigint | Número de vezes que o plano foi executado desde sua última compilação. |
| total_worker_time | bigint | Tempo total da CPU, relatado em microssegundos (mas preciso somente em milissegundos), que foi consumido pelas execuções desse plano desde que foi compilado. |
| last_worker_time | bigint | Tempo de CPU, relatado em microssegundos (mas preciso somente em milissegundos), consumido na última vez em que o plano foi executado. |
| min_worker_time | bigint | Tempo de CPU mínimo, relatado em microssegundos (mas preciso somente em milissegundos), que esse plano já consumiu durante uma única execução. |
| max_worker_time | bigint | Tempo de CPU máximo, relatado em microssegundos (mas preciso somente em milissegundos), que esse plano já consumiu durante uma única execução. |
| total_physical_reads | bigint | Número total de leituras físicas efetuadas por execuções deste plano desde sua compilação. Novamente, leitura física é pertinente ao acesso ao disco para recuperação de dados da tabela. Logical reads são leituras que acessam o cache de dados e também o disco. O uso de cache é basicamente o resultado da substração Logical Reads - Physical Reads. |
| last_physical_reads | bigint | Número de leituras físicas efetuadas na última vez em que o plano foi executado. |
| min_physical_reads | bigint | Número mínimo de leituras físicas que este plano efetuou durante uma única execução. |
| max_physical_reads | bigint | Número máximo de leituras físicas que este plano efetuou durante uma única execução. |
| total_logical_writes | bigint | Número total de gravações lógicas efetuadas por execuções deste plano desde sua compilação. |
| last_logical_writes | bigint | Número de gravações lógicas efetuadas na última vez em que o plano foi executado. |
| min_logical_writes | bigint | Número mínimo de gravações lógicas que este plano efetuou durante uma única execução. |
| max_logical_writes | bigint | Número máximo de gravações lógicas que este plano efetuou durante uma única execução. |
| total_logical_reads | bigint | Número total de leituras lógicas efetuadas por execuções deste plano desde sua compilação. |
| last_logical_reads | bigint | Número de leituras lógicas efetuadas na última vez em que o plano foi executado. |
| min_logical_reads | bigint | Número mínimo de leituras lógicas que este plano efetuou durante uma única execução. |
| max_logical_reads | bigint | Número máximo de leituras lógicas que este plano efetuou durante uma única execução. |
| total_clr_time | bigint | O tempo, relatado em microssegundos (mas preciso somente em milissegundos), consumido dentro de objetos CLR (Common Language Runtime) Microsoft .NET Framework por execuções desse plano desde sua compilação. Os objetos CLR podem ser procedimentos armazenados, funções, disparadores, tipos e agregados. Para quem usa objetos CLR, é uma ótima ficar de olho nestes indicadores pois pode existir uma regra de negócio no seu objeto que causa uma flutuação grande no tempo de execução. |
| last_clr_time | bigint | Tempo, relatado em microssegundos (mas preciso somente em milissegundos), consumido pela execução dentro de objetos CLR .NET Framework durante a última execução desse plano. Os objetos CLR podem ser procedimentos armazenados, funções, disparadores, tipos e agregados. |
| min_clr_time | bigint | Tempo mínimo, relatado em microssegundos (mas preciso somente em milissegundos), que esse plano já consumiu dentro de objetos CLR .NET Framework durante uma única execução. Os objetos CLR podem ser procedimentos armazenados, funções, disparadores, tipos e agregados. |
| max_clr_time | bigint | Tempo máximo, relatado em microssegundos (mas preciso somente em milissegundos), que esse plano já consumiu dentro de CLR .NET Framework durante uma única execução. Os objetos CLR podem ser procedimentos armazenados, funções, disparadores, tipos e agregados. |
| total_elapsed_time | bigint | Tempo total decorrido, relatado em microssegundos (mas preciso somente em milissegundos), para execuções concluídas desse plano. Conforme disse antes, instruções de espera como WAITFOR não são consideradas nesse contadores "_elapsed_time". |
| last_elapsed_time | bigint | Tempo decorrido, relatado em microssegundos (mas preciso somente em milissegundos), para a execução completa mais recente desse plano. |
| min_elapsed_time | bigint | Tempo decorrido mínimo, relatado em microssegundos (mas preciso somente em milissegundos), para qualquer execução concluída desse plano. |
| max_elapsed_time | bigint | Tempo decorrido máximo, relatado em microssegundos (mas preciso somente em milissegundos), para qualquer execução concluída desse plano. |
| query_hash | binary(8) | Valor de hash binário calculado na consulta e usado para identificar consultas com lógica semelhante. Você pode usar o hash de consulta para determinar o recurso agregado usado para consultas que são diferentes apenas nos valores literais. Para obter mais informações, consulte Localizando e ajustando consultas semelhantes usando consulta e hashes de plano de consulta. |
| query_plan_hash | binary(8) | Valor de hash binário calculado no plano de execução de consulta e usado para identificar planos de execução de consulta semelhantes. Você pode usar o hash de plano de consulta para localizar o custo cumulativo de consultas com planos de execução semelhantes. Para obter mais informações, consulte Localizando e ajustando consultas semelhantes usando consulta e hashes de plano de consulta. |
| total_rows | bigint | O número total de linhas retornadas pela consulta. Não pode ser nulo. |
| last_rows | bigint | O número total de linhas retornadas pela última execução da consulta. Não pode ser nulo. |
| min_rows | bigint | O número mínimo de linhas retornadas pela consulta no número de vezes em que o plano foi executado desde sua última compilação. Não pode ser nulo. |
| max_rows | bigint | O número máximo de linhas retornadas pela consulta no número de vezes em que o plano foi executado desde sua última compilação. Não pode ser nulo. |
Então é isso pessoal. Qualquer dúvida ou caso queiram "encomendar" uma query, é só lançar nos comentários que responderei o mais rápido possível. Obrigado por visitar o blog e espero publicar novos posts logo mais. Abraços!
Nenhum comentário:
Postar um comentário