查找有问题的查询

Question

以下查询在SQL Server 2008中运行，但在SQL Server 2012中不起作用。为什么？

SELECT TOP 20
    total_worker_time / execution_count AS Avg_CPU_Time,
    Execution_count,
    total_elapsed_time / execution_count AS AVG_Run_Time,
    total_elapsed_time,
    (SELECT
        SUBSTRING(text, statement_start_offset / 2 + 1, statement_end_offset)
    FROM sys.dm_exec_sql_text(sql_handle))
    AS Query_Text
FROM sys.dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC

Answer 1

statement_end_offset表示statement_end_offset是当前正在执行的语句结束的当前正在执行的批处理或存储过程中的字符数。

问题：statement_end_offset有时返回 -1 值，它给出错误："传递给 LEFT 或 SUBSTRING 函数的长度参数无效"。

使用以下修改后的查询，该查询适用于任何SQL Server版本：-

SELECT TOP 20 total_worker_time/execution_count AS [Avg CPU Time],  
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
    ((CASE qs.statement_end_offset  
      WHEN -1 THEN DATALENGTH(st.text)  
     ELSE qs.statement_end_offset  
     END - qs.statement_start_offset)/2) + 1) AS Query_Text  
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
ORDER BY total_worker_time/execution_count DESC;  

Answer 2

CASE应该给你一些启发。

SELECT TOP 20
    total_worker_time / execution_count AS Avg_CPU_Time,
    Execution_count,
    total_elapsed_time / execution_count AS AVG_Run_Time,
    total_elapsed_time,
   (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+1)
    FROM sys.dm_exec_sql_text(sql_handle) )
    AS Query_Text
FROM sys.dm_exec_query_stats
ORDER BY Avg_CPU_Time DESC

Answer 3

正如我在 commet 中提到的，按子句排序将不起作用。请尝试APPLY功能sys.dm_exec_sql_text：

SELECT  TOP 20
        s.total_worker_time / execution_count AS Avg_CPU_Time,
        s.execution_count,
        s.total_elapsed_time / s.execution_count AS AVG_Run_Time,
        s.total_elapsed_time,
        SUBSTRING(t.text, s.statement_start_offset / 2 + 1, s.statement_end_offset)
FROM    sys.dm_exec_query_stats s 
        OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
ORDER BY 1 DESC

请向我们提供一些错误文本，也许SUBSTRING函数中有问题......