March 25, 2009 at 12:18 am
Dear all,
We are using SQL Server 2005 for production and the database compatability level is kept for 80 not 90. I have a qery for
finding the long running queries which supports on SQL 2005 not 2000 database. Its throwing error when i run the query
to identify the long running queries.
Query :
select top 50
qs.total_worker_time / execution_count as avg_worker_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
avg_worker_time desc
ERROR :
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'apply'.
Can anyone please have a look into this to work fine for 2000 compatibility databases.
Or else anyone please provide me the query for identifying the long running queries.
Thanks,
CH&HU.
March 25, 2009 at 12:50 am
Please don't cross post, replies here.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 25, 2009 at 1:53 am
Mohith :
--------------------------------------------------------
SELECT TOP 50 qs.total_worker_time / execution_count as avg_worker_time,
(SELECT 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)
FROM sys.dm_exec_sql_text(qs.sql_handle) AS ST) AS statement_text,
*
FROM sys.dm_exec_query_stats AS qs
ORDER BY avg_worker_time DESC
--------------------------------------------------
The query must be ran on SQL Server 2005 server and the database which is in copatability level of 80 (set to 2000 as should work for Frame Work processes), please make a note
Error : Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '.'.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply