Início / dba / Perguntas / 182000
Accepted
Ollie
Asked: 2017-07-28 07:58:37 +0800 CST

Obtenha as principais consultas SQL que não são procs/funções/gatilhos armazenados

  • 772

Tarde,

Eu preciso de uma maneira de obter as estatísticas de consulta para consultas que não são procedimentos/funções/gatilhos.

sys.dm_exec_query_stats só parece conter estes, preciso reunir estatísticas semelhantes que estão neste dmv, mas apenas para consultas normais enviadas por ssms/EF/Web etc.

Isso é o que eu usei e não retorna nenhum resultado. (não o mais eficiente, mas apenas uma rápida prova de conceito)

SELECT txt.text
   last_execution_time,
   execution_count,
   total_elapsed_time,
   last_elapsed_time,
   min_elapsed_time,
   max_elapsed_time
   FROM sys.dm_exec_query_stats
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS txt 
   WHERE
   txt.text NOT LIKE '%CREATE PROC%' 
   AND txt.text NOT LIKE '%CREATE FUNC%'
   AND txt.text NOT LIKE '%CREATE TRIGG%'
   AND txt.dbid = DB_ID('$(DB)')

Eu sei que existem várias consultas enviadas regularmente de aplicativos cliente que não usam procs armazenados e levam muito tempo para serem executados. Eu teria pensado que o SQL mantém um registro disso em algum lugar.

sql-server sql-server-2008-r2

3 respostas

  1. Existe uma maneira fácil (e gratuita!) de fazer isso usando sp_BlitzCache (divulgação completa, eu contribuo com este OSS).

    Você pode executá-lo com a @QueryFilteropção (que também pode obter apenas procs armazenados e apenas funções (mas as funções são apenas 2016+).

    EXEC master.dbo.sp_BlitzCache @QueryFilter = 'statement'

    Isso lhe dará suas 10 principais declarações por CPU geral. Se quiser ordenar por outras métricas, você pode usar o @SortOrderparâmetro para ir por leituras, duração, execuções e muito mais.

    Espero que isto ajude!

    • 2

  2. Aaron sugeriu a resposta. Há um item de conexão arquivado sobre isso. dbidsendo NULL para instruções SQL ad hoc e preparadas. Há também uma solução alternativa mencionada no mesmo artigo. Está fechado agora.

    Modifiquei um pouco a solução alternativa para obter todas as colunas necessárias. Isso será executado significativamente mais do que sua consulta, pois está obtendo o dbid do próprio plano.

    SELECT COALESCE(Db_name(Cast(pa.value AS INT)), Replace( 
              Replace(qp.query_plan.value('(//@Database)[1]', 'nvarchar(255)'), 
              '[', 
                                                       ''), ']', ''), 
       Db_name(qp.dbid)) AS [DatabaseName], 
       qt.text, 
       last_execution_time, 
       execution_count, 
       total_elapsed_time, 
       last_elapsed_time, 
       min_elapsed_time, 
       max_elapsed_time 
FROM   sys.dm_exec_query_stats qs WITH(nolock) 
       CROSS apply sys.Dm_exec_plan_attributes(qs.plan_handle) pa 
       CROSS apply sys.Dm_exec_query_plan(qs.plan_handle) qp 
       CROSS apply sys.Dm_exec_sql_text(qs.sql_handle) AS qt 
WHERE  pa.attribute = 'dbid' 
       AND qt.text NOT LIKE '%CREATE PROC%' 
       AND qt.text NOT LIKE '%CREATE FUNC%' 
       AND qt.text NOT LIKE '%CREATE TRIGG%' 
ORDER  BY total_elapsed_time DESC
    • 2
  3. Best Answer

    Finalmente consegui o equilíbrio certo entre desempenho e precisão. O abaixo retornará as 100 principais consultas com base no tempo total médio decorrido.

    É para o modo SQLCMD, você terá que alterar isso se não quiser usá-lo nessa configuração.

    :SETVAR DB "TargetDB"

     WITH MostExpensive
    AS (SELECT TOP 100
            SQLTXT.text,
            STATS.last_execution_time,
            STATS.execution_count,
            STATS.total_elapsed_time,
            STATS.last_elapsed_time,
            STATS.min_elapsed_time,
            STATS.max_elapsed_time,
            STATS.total_elapsed_time / STATS.execution_count AS avg_elapsed_time,
            DB_NAME(CONVERT(INT, ATTR.value)) DBNAME
        FROM sys.dm_exec_query_stats STATS WITH (NOLOCK)
            CROSS APPLY sys.dm_exec_plan_attributes(STATS.plan_handle) ATTR
            CROSS APPLY sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTXT
        WHERE ATTR.attribute = 'dbid'
              AND SQLTXT.text NOT LIKE '%CREATE PROC%'
              AND SQLTXT.text NOT LIKE '%CREATE FUNC%'
              AND SQLTXT.text NOT LIKE '%CREATE TRIGG%'
              AND ATTR.value = DB_ID('$(DB)')
        ORDER BY avg_elapsed_time DESC
       )
    SELECT ROW_NUMBER() OVER (ORDER BY MostExpensive.avg_elapsed_time DESC) Rnk,
           *
    FROM MostExpensive;

    Graças ao @SQLWorldWide, que sugeriu ler o banco de dados do xml, isso adicionou muito tempo (3 minutos), mas enquanto eu estava brincando no dm_exe_query_plan, notei que a coluna de valor era igual ao ID do banco de dados. pode não ser 100% abrangendo todo o tráfego, mas é rápido (1 segundo) e funciona para o que eu preciso.

    • 1

relate perguntas