Post reply

Query

  • November 19, 2011 at 10:43 pm

    #390909

    I need a query to find out a user query which is utilizing the maximum memory/CPU usage..

    Thansk for your help in advance

  • November 20, 2011 at 12:47 am

    #1410377

    This should do it...

    SELECT TOP 1 *

    FROM sys.dm_exec_sessions

    ORDER BY memory_usage DESC

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 20, 2011 at 2:17 am

    #1410380

    Here is a query that can check the query plans that exist in the cache and show you the queries that use most CPU. Take into account that if a query is no longer in the cache then you won’t see it. Also there could be cases of queries that never get into the cache.

    select top 10

    substring (SqlQuery.text, QueryStats.statement_start_offset /2 + 1, CASE WHEN statement_end_offset = -1 then datalength(SqlQuery.text) else QueryStats.statement_end_offset /2 - statement_start_offset/2 end) as QueryText

    ,total_worker_time as SumOfCPUTime, execution_count, cast (total_worker_time as float) / execution_count as AvgCPUTime

    from sys.dm_exec_query_stats QueryStats cross apply sys.dm_exec_sql_text (QueryStats.sql_handle) as SqlQuery

    order by total_worker_time desc

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • November 20, 2011 at 10:18 am

    #1410416

    Thanks a lot

    How to get the login name or user in the query.

  • November 20, 2011 at 11:30 am

    #1410418

    From exec_query_stats you can't. That DMV shows an aggregated summary of query statistics.

    From sys.dm_exec_sessions, they're columns in that DMV.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 20, 2011 at 1:55 pm

    #1410422

    Mvs2k11 (11/20/2011)

    Thanks a lot

    How to get the login name or user in the query.

    Did you even look at the query I previously posted? 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • November 21, 2011 at 9:43 am

    #1410858

    Thanks..I got the results..I want to report the source of users query which in the below results like user/login.

    QueryTextSumOfCPUTimeexecution_countAvgCPUTime

Viewing 7 posts - 1 through 6 (of 6 total)

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