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?
推荐答案
这表示您是
- 未运行SQL Server 2005;或更可能
- 未在兼容模式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屋!