Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2006
    Posts
    115

    Question Unanswered: Top 50 CPU intensive queries, what's wrong with this query?

    I was given this query to check the top 50 CPU intensive queries in the SQL server. However, I can't parse it....any idea what went wrong?

    SELECT TOP 50 (a.total_worker_time/a.execution_count) as [Avg_CPU_Time],
    Convert(Varchar,Last_Execution_Time) as 'Last_execution_Time',
    Total_Physical_Reads,
    SUBSTRING(b.text,a.statement_start_offset/2,
    (case when a.statement_end_offset= -1 then len(convert(nvarchar(max), b.text)) * 2
    else
    a.statement_end_offsetend -a.statement_start_offset)/2) as Query_Text,
    dbname=Upper(db_name(b.dbid)),
    b.objectidas 'Object_ID'
    FROM sys.dm_exec_query_statsa
    cross apply
    sys.dm_exec_sql_text(a.sql_handle) as b
    ORDER BY
    [Avg_CPU_Time] DESC

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your CASE expression needs an END at the end to close it. A second error:
    Code:
    sys.dm_exec_query_statsa
    Code:
    sys.dm_exec_query_stats AS a
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •