How to get slowest queries on SQL 2005, without requiring compability change?

Question

I was reading this fabulous list of sql dba scripts at http://www.sqldbtools.com/Scripts.aspx, not affiliated with them. And I want to run this query...

select top 10
    qs.execution_count,
    st.dbid,
    DB_NAME(st.dbid) as DbName,
    st.text from sys.dm_exec_query_stats as qs cross
apply sys.dm_exec_sql_text(sql_handle)
st order by execution_count desc

But for our production server, it is way too risky, to change compatibility modes, is there a script i can use that doesn't require that?

Answer 1

Your query will work on SQL Server 2005, as long as you don't run it in context of the database with the lower compat level

USE master
GO
select top 10
    qs.execution_count, st.dbid, DB_NAME(st.dbid) as DbName, st.text
from 
    sys.dm_exec_query_stats as qs
    cross apply
    sys.dm_exec_sql_text(sql_handle) st order by execution_count desc

From Paul Randall's Misconceptions about running DMVs on databases with lower compatibility levels