How To find slow running queries on SQL 2005

neellotus

Dear All,

I am running following query on SQL2005 Server to find slow running queries :-

SELECT creation_time ,last_execution_time,total_physical_reads,total_logical_reads,total_logical_writes, execution_count, total_worker_time
,total_elapsed_time, total_elapsed_time / execution_count avg_elapsed_time,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) st
ORDER BY total_elapsed_time / execution_count DESC;

But it is showing following error:-

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '.'.

Basically it showing error at Line 5 and i am writing this "sys.dm_exec_sql_text(qs.sql_handle) st" at Line 5.

Please suggest some solution and tell me where i am doing wrong.

Thanx in advance.

Regards
neel

kriki

I tried your query and in runs without errors on my SQL 2008 R2.
That means this query uses some objects that do not exist in SQL 2005.

stryk

Tested the query with SQL 2008 R2 and SQL 2005 - works with bosth servers!

kriki

stryk wrote:

Tested the query with SQL 2008 R2 and SQL 2005 - works with bosth servers!

SQL2005 : which SP did you use to test it? It is possible the query doesn't work on an older SP.

stryk

I was using SQL Server 2005 x64 Enterprise 9.0.5000

aysugnacar

This Query ran successfully in the SQL 2008 environment. I think may be it is the issue of memory cache or the memory cursors that doesn't allow proper pre-caching of instructions pipeline.

nav_student

What are the values that i must be aware to know that are the most significant slow running queries?~~

stryk

Depends ...

I actually start to look into queries taking longer than 20 msec "Duration", using more than 1000 "Reads" and are at least executed several dozens of times ...

Than again, it depends ...