October 20, 2011 at 12:24 pm
Those DMVs are server-wide. There's nothing about that query that is restricted to one DB over another.
What do you mean by 'doesn't work'? Throws an error? Returns no data? Sucks the server into a black hole?
p.s. Why are you querying only the CLR stats and ordering by CLR time? Odd thing to do unless you're troubleshooting CLR problems.
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
October 20, 2011 at 12:44 pm
its ok found the issue databse compatability level was the wrong type for this query thank you 🙂 retrieve server from the abis 🙂
updated my SQL to only report on my DB 🙂
declare @db int
set @db=DB_ID('yourDB here')
SELECT creation_time, last_execution_time, total_clr_time,
total_clr_time/execution_count AS [Avg CLR Time], last_clr_time,
execution_count,
SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
((CASE 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
where dbid=@db
order by 1 desc
thank you all for your help think i mioght need it again soon :ermm:
***The first step is always the hardest *******
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply