Equivalnet of sys.dm_exec_sql_text(qs.sql_handle) in compatibility mode

CodeWrangler
CodeWrangler used Ask the Experts™
on
Hello all,

Following query generates an error for me:
(from http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!828.entry)
 -- Get Top 20 executed SP's ordered by total worker time (CPU pressure)
   SELECT TOP 20 qt.text AS 'SP Name', qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.execution_count AS 'Execution Count',
    ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
    ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes,
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_worker_time DESC

Error: Syntax error at "CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt"

The problem is that my db is in sql 2000 compatibility mode. See http://jeremeguenther.blogspot.com/2009/06/cross-apply-incorrect-syntax-near.html for the solution to this.

Unfortunately, I am unable to raise the level. Has anyone figured out a way aorund this or some alternative code that achieves the same or similar effect.

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Awarded 2008
Awarded 2008

Commented:
No alternative....this is 2005 functionality.  

Author

Commented:
Right, but what I am looking for is maybe a table/view I can join to to get a similar result. i.e., join sys.dm_exec_query_stats to something else other than sys.dm_exec_sql_text(qs.sql_handle)
Awarded 2008
Awarded 2008

Commented:
There isn't anything to join to in 2000....
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Author

Commented:
My server is 2008, the db which has the SPs I need to get stats on is level 80.
What chapmandew is telling you is that you don't get the SQL 2005+ DMVs when you are running a DB in 2000 (80) compatibility mode and that there is no SQL 2000 table/view that produces the same or similar data.

Author

Commented:
Yeah, just tested that against my db and AdventureWorks2008R2... <sigh>

Will keep this open for a couple of days just in case someone has a suggestion
Commented:
if you want to look at long running queries, you can use sp_who2.  it will display all of the processes with information on blocked processes, cpu_time, etc.  Get the spids with the largest CPU time then run dbcc inputbuffer(spid) to get the sql text.  

Note that these processes will pull information based on the current processes running on the server.  If you want to get longest transactions from cached queries, you can run a profiler trace to store all transactions that was run on the server.  You can follow this article designed for sql server 2000 performance tuning:

http://www.sql-server-performance.com/articles/per/performance_audit_part8_p1.aspx

Author

Commented:
Excellent alternative monicai. This might be the only way I can get some kind of performance info on my level 80 databases on my sql 2008 box. I will keep this open for a few more days in case there are more suggestions.

This makes me appreciate the (performance tracking related) changes made to 2005/2008 a lot more!!

Commented:
Can you also try running your query to a DB compatible 90 database and change your syntax a bit that will point to your DB compat 80.

For example your db is testDBCompat80.



use master

SELECT qt.text AS 'SP Name',
    qs.total_worker_time AS 'TotalWorkerTime',
    qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
    qs.execution_count AS 'Execution Count',
    ISNULL(qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()), 0) AS 'Calls/Second',
    ISNULL(qs.total_elapsed_time/qs.execution_count, 0) AS 'AvgElapsedTime',
    qs.max_logical_reads, qs.max_logical_writes,
    DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache'
    FROM testDBCompat80.sys.dm_exec_query_stats  AS qs
    CROSS APPLY testDBCompat80.sys.dm_exec_sql_text(qs.sql_handle) AS qt
    WHERE qt.dbid = db_id() -- Filter by current database
    ORDER BY qs.total_worker_time DESC

Open in new window

Commented:
One more alternative is create a temp table to store sys.dm_exec_query_stats and run a cursor to query for sys.dm_exec_sql_text and append the sql text on your temp table.  :-)

Author

Commented:
monicai, I have not had the chance to try out the solutions yet... hopefully in the next few days! Apologies all for the delay in reporting back.

Commented:
no worries.

I guess it is worthy to note here that the myth of DMVs not available on SQL Server 2000 compatibility mode (aka DB compatible 80)  databases is NOT true.  

It has been demystified by Paul Randal.  Check it out here on page 17 http://www.sqlskills.com/blogs/paul/CommonSQLServerMyths.pdf

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial