主页 / dba / 问题 / 182000
Accepted
Ollie
Asked: 2017-07-28 07:58:37 +0800 CST

获取不是存储过程/函数/触发器的顶级 SQL 查询

  • 772

下午，

我需要获取非过程/函数/触发器查询的查询统计信息的方法。

sys.dm_exec_query_stats 似乎只包含这些，我需要收集此 dmv 中的类似统计信息，但仅适用于 ssms/EF/Web 等提交的正常查询。

这是我使用的，它没有返回任何结果。（不是最有效的，只是概念的快速证明）

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)')

我知道有几个不使用存储过程的客户端应用程序定期提交的查询需要很长时间才能执行。我原以为 SQL 会在某处记录这些。

sql-server sql-server-2008-r2

3 个回答

  1. 有一种使用sp_BlitzCache的简单（而且免费！）方法可以做到这一点（完全公开，我为这个 OSS 做出了贡献）。

    您可以使用@QueryFilter选项运行它（它也可以只获取存储的过程，并且只获取函数（但函数只有 2016+）。

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

    这将为您提供总体 CPU 的前 10 个语句。如果您想按其他指标排序，可以使用@SortOrder参数按读取次数、持续时间、执行次数等进行排序。

    希望这可以帮助！

    • 2

  2. 亚伦暗示了答案。有一个关于此的连接项目。dbid对于临时和准备好的 SQL 语句为 NULL。同一篇文章中还提到了一种解决方法。现在关门了。

    我稍微修改了解决方法以获得您需要的所有列。这将比您的查询运行的时间长得多，因为它是从计划本身获取 dbid 的。

    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

    我终于在性能和准确性之间取得了正确的平衡。下面将根据平均总运行时间返回前 100 个查询。

    它用于 SQLCMD 模式，如果您不想在该设置中使用它，则必须更改它。

    :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;

    感谢@SQLWorldWide 关于从 xml 读取数据库的建议，这增加了很多时间（3 分钟），但是当我在 dm_exe_query_plan 中游玩时，我注意到值列等于数据库 ID。它可能不会 100% 包含所有流量，但速度很快（1 秒）并且可以满足我的需要。

    • 1

相关问题