How To find slow running queries on SQL 2005

neellotusneellotus Member Posts: 18
edited 2012-12-20 in SQL Performance
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

Comments

  • krikikriki Member, Moderator Posts: 9,094
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • strykstryk Member Posts: 645
    Tested the query with SQL 2008 R2 and SQL 2005 - works with bosth servers!
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • krikikriki Member, Moderator Posts: 9,094
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • strykstryk Member Posts: 645
    I was using SQL Server 2005 x64 Enterprise 9.0.5000
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • aysugnacaraysugnacar Member Posts: 1
    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.
    Shipping register software for handling boating accounts.
  • nav_studentnav_student Member Posts: 175
    What are the values that i must be aware to know that are the most significant slow running queries?~~
  • strykstryk Member Posts: 645
    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 ...
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
Sign In or Register to comment.