CROSS APPLY中的语法错误 [英] syntax error in CROSS APPLY

问题描述

我正在尝试运行一个简单的查询，以查找具有最高平均CPU时间的查询.该代码从此处 :

I'm trying to run a simple query to find the queries with the highest average CPU time. The code is literally copy-pasted from here:

SELECT TOP 5 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 statement_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;

问题是，SQL Server在第8行的sys.dm_exec_sql_text:qs.sql_handle参数上抱怨语法错误

Problem is, SQL Server is complaining about a syntax error in line 8 at the parameter to sys.dm_exec_sql_text: qs.sql_handle which unhelpfully reads

Incorrect syntax near '.'.

我一生无法解决查询的问题.有什么想法吗?

I cannot, for the life of me, figure out what's wrong with the query. Any ideas?

推荐答案

这表示您是

1. 未运行SQL Server 2005；或更可能
2. 未在兼容模式90或更高版本下运行

您可以使用将其更改为90或更高，但是 很好 会破坏很多应用程序.

You can change it to 90 or above using, but it could very well break a lot of applications.

alter database MyDataBaseName set compatibility_level = 90

在SQL Server 2005及更高版本上，最简单的解决方案是仅从"master"或"tempdb"运行它，例如

The easiest solution on SQL Server 2005 and above is simply to run it from "master" or "tempdb", e.g.

USE tempdb;
SELECT TOP 5 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 statement_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;

这篇关于CROSS APPLY中的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！