I want to use the following query:

    SUBSTRING(qt.TEXT, ( qs.statement_start_offset / 2 ) + 1,
     ( ( CASE qs.statement_end_offset
           ELSE qs.statement_end_offset
         END - qs.statement_start_offset ) / 2 ) + 1) AS [Text] ,
 qs.execution_count ,
 qs.total_logical_reads ,
 qs.last_logical_reads ,
 qs.total_logical_writes ,
 qs.last_logical_writes ,
 qs.total_worker_time ,
 qs.last_worker_time ,
 qs.total_elapsed_time / 1000000 total_elapsed_time_in_S ,
 qs.last_elapsed_time / 1000000 last_elapsed_time_in_S ,
 qs.last_execution_time ,
FROM sys.dm_exec_query_stats qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_worker_time DESC 

I took the code snippet from How to find CPU intensive queries? on the SQL Server Central blogs.

I need to run this query on database which is in compatibility mode 80 and APPLY is not available there.

  • 1
    What do you mean with "I need it compatible level 80"? Can't you run the query in a database with compatibility level 90 or higher, such as tempdb? – spaghettidba Aug 26 '15 at 7:49
  • 1
    Won't this run in the context of the master database? Obviously these are DMVs so won't run on SQL Server 2000 at all. – Peter Schofield Aug 26 '15 at 8:52
  • @spaghettidba I meant how would the query look like to be able to run with compatibility 80. Your hint was correct, running it in another db context works. at Peter, right, it runs in another context like master. The Server is not 2000, only the db I tried to run it in was. – Magier Aug 26 '15 at 11:57

The results from that query will not be scoped to particular database. They will show activity across the whole instance.

In general, it would be quite difficult to provide information at this granularity, because queries can access data from more than one database (Azure SQL Database aside).

In non-trivial queries, accounting for costs on a per-database basis (e.g. after joins and aggregations have been performed) would be pretty much impossible. Some DMVs do provide information about the context database at the time a query was executed, but again, the query might have accessed objects in an entirely different database(s).

The easiest answer, therefore, is to simply run the query in the context of a database that is set to compatibility level 90 or higher, such as tempdb or master. It will not affect the results (aside from not producing an error).

If this is not a suitable solution, please edit your question to clarify.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.