When I execute queries which include DMFs the queries return a syntax error.
For example:
SELECT — TOP 20
qs.sql_handle,
qs.execution_count,
qs.total_worker_time AS Total_CPU,
total_CPU_inSeconds = –Converted from microseconds
qs.total_worker_time/1000000,
average_CPU_inSeconds = –Converted from microseconds
(qs.total_worker_time/1000000) / qs.execution_count,
qs.total_elapsed_time,
total_elapsed_time_inSeconds = –Converted from microseconds
qs.total_elapsed_time/1000000
–, st.text
–, qp.query_plan
from
sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
ORDER BY qs.total_worker_time desc
The above query is failing for sys.dm_exec_sql_text(qs.sql_handle) and sys.dm_exec_query_plan (qs.plan_handle) DMFs.
The reason for this error is that when the user is attempting to run query is not compatible with previous version of the SQL Server. When SQL Server 2000 is upgraded to SQL Server 2005 or SQL Server 2008, the database object compatibility should be also upgraded to the next version.
To check the compatibility level of the master database:
select name,compatibility_level from sys.databases
where name = ‘master’
If the above query returns values 80 or less that means your database is not compatible with SQL Server 2005 or SQL Server 2008
You can change this by navigating to the Database property page of the master database and changing the compatibility level to 90 or 100.
Change the database compatibility level using following command.
For SQL Server 2005:
EXEC sp_dbcmptlevel 'master', 90
For SQL Server 2008:
EXEC sp_dbcmptlevel 'master', 100