Post reply

Query to Find Top CPU Queries

  • May 1, 2014 at 6:34 pm

    #285193

    Comments posted to this topic are about the item Query to Find Top CPU Queries

  • June 3, 2014 at 5:17 am

    #1718474

    Thanks, very interesting. I will play around with it in our environment and see what I see with this.

  • June 3, 2014 at 8:23 am

    #1718551

    Please do and post your feedback on the flaws with it and possible fixes so i can keep improving it.

  • June 20, 2014 at 12:58 pm

    #1723690

    akhamoshioke (6/3/2014)

    Please do and post your feedback on the flaws with it and possible fixes so i can keep improving it.

    Thanks for this script!

    It works great on SQL Server 2008 R2 Enterprise Edition.

    On SQL Server 2005 Standard Edition, however I got these errors when I tried to run it.

    Msg 139, Level 15, State 1, Line 0

    Cannot assign a default value to a local variable.

    Msg 137, Level 15, State 2, Line 10

    Must declare the scalar variable "@debugFlg".

    Msg 137, Level 15, State 2, Line 11

    Must declare the scalar variable "@ServerTime".

    Msg 137, Level 15, State 2, Line 20

    Must declare the scalar variable "@ServerTime".

    Msg 137, Level 15, State 2, Line 43

    Must declare the scalar variable "@FilterHours".

    Msg 137, Level 15, State 2, Line 51

    Must declare the scalar variable "@debugFlg".

    Msg 137, Level 15, State 2, Line 66

    Must declare the scalar variable "@ConvertMiliSeconds".

    Any thoughts on how to fix this? Or does the code need to be modified for SQL 2005?

    Thanks again,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • June 21, 2014 at 9:37 am

    #1723780

    Thanks for sharing your findings. The code was written to work with SQL Server 2008/2008R2 and will have to be modified for sql 2005. I do have a link from databasejournal.com tha should help you. Please do test it before using it in production;

    🙂

    http://www.databasejournal.com/features/mssql/article.php/3737936/Top-Queries-in-SQL-Server-2005.htm

    Hope this helps. Remember to mark it as a solution if it does help you

  • June 24, 2014 at 11:34 pm

    #1724511

    Hi, Thanks for sharing script for finding top CPU queries.

    select

    servername,runtime,max(DBName) as DBName,max(QueryExecuted) as QueryExecuted,(sum(total_worker_time)/sum(execution_count))/@ConvertMiliSeconds as AvgCPUTime

    ,sum(execution_count) as execution_count,query_hash, max(ObjectName) as ObjectName

    into ##FindTopCPUQueries_set2

    from ##FindTopCPUQueries_set1

    group by query_hash,servername,runtime

    order by AvgCPUTime desc

    could you explain me? AvgCPUTime

    As per output, there is one query taking too much time as below

    AvgCPUTime = 136722

    execution_count = 25,

    So, what is the per execution time?

    per execution = 136722/25 = 5468 (is it milliseconds or seconds)

    Thanks

    ananda

  • June 26, 2014 at 8:48 am

    #1725126

    AvgCPUTime = 136722 -- Mathematical average cpu time (in miliseconds) it took per query execution.

    execution_count = 25, -- Total count of executions of the query.

    I bet when you look at the dmvs for your wait tasks and waiting tasks, you'll see a lot of CXPACKET waits (indicative of CPU waits) and you'll also notice a lot of query timeouts et all with that query during peak period.

  • July 2, 2014 at 2:00 am

    #1726395

    Hi

    I have this output after run this script.

    servername runtime DBName

    KLAS-HDC-SQLPRD\APPSPRD50:14.1 PreparedSQL

    Why it is returning PreparedSQL? I dont have database name as PreparedSQL.

    One more thing, what is query_hash column for?

  • July 2, 2014 at 2:59 am

    #1726411

    "PreparedSQL" means Ad Hoc query witten in that application side for DML operation, also it is not refer any stored procedure..

  • June 25, 2015 at 7:47 am

    #1808064

    It looks interesting, I'll have to give it a try. Thanks.

  • February 17, 2016 at 4:53 pm

    #1859435

    Thanks for the script.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply