March 7, 2012 at 7:59 am
I have used a number of DMV queries over the last few weeks which performed perfectly for me. Sudenly, over tthe last few days I cannot use them any more. They fail with the same type of error. Does anyone have any ideas what may be wrong - I guess it must be in my SMS set up as I see no massive discussion of this anywhere:
Typical query:
SELECT TOP 10 total_worker_time/execution_count AS [Avg CPU Time]
, Plan_handle
, query_plan
, qs.execution_count
,qs.creation_time
,qs.last_execution_time
, (total_worker_time/(execution_count * 1000.0)) as avg_worker_time_in_millisecs
FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
ORDER BY total_worker_time/execution_count DESC
Error:
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near '.'.
The error refers to the line: FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
March 7, 2012 at 8:01 am
Compatibility mode of the database you're running this in?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2012 at 9:20 am
We have made no changes to any of the databases that I run the queries against. Compatibility Level is 80.
March 7, 2012 at 9:34 am
That query won't work in anything below compat mode 90. In compat mode 80 and below, only constants, variables or parameters are allowed to be passed to a function, so the qs.plan_handle is considered syntatically incorrect. Remove the qs. and you'll get a different error, also a result of the database being in compat mode 80
Run the query from the master database (which should be mode 90) and it should work fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2012 at 9:48 am
Thank you for that. You have saved me from insanity. I have checked my other databases and they all run in mode 90. Which would explain why I am having an issue with this one! 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply