What SQL Statements Are Currently Executing?

  • BudaCli

    Hall of Fame

    Points: 3378

    Nice article I'd agree that it is my first time seeing CROSS APPLY as well.

    What you don't know won't hurt you but what you know will make you plan to know better
  • SQLRNNR

    SSC Guru

    Points: 281243

    Nice and relevant article. I would have liked to have seen it not use a deprecated object though.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • ianstirk

    Ten Centuries

    Points: 1310

    Hi Jason,

    I'm glad you liked the article.

    There is an updated version of this script, without the deprecated objects, given in the book I am writing "SQL Server DMVs in Action". You can get the first chapter free here: http://www.manning.com/stirk

    Thanks

    Ian

  • SQLRNNR

    SSC Guru

    Points: 281243

    ianstirk (4/6/2010)


    Hi Jason,

    I'm glad you liked the article.

    There is an updated version of this script, without the deprecated objects, given in the book I am writing "SQL Server DMVs in Action". You can get the first chapter free here: http://www.manning.com/stirk

    Thanks

    Ian

    That's cool.

    I took the liberty of creating a script similar in nature that I use in place of sp_who2.

    I will be posting that script to the web soon.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • mEmENT0m0RI

    SSC Veteran

    Points: 254

    Good work. Very useful script.

    Thanks!

  • David.Lavers

    Grasshopper

    Points: 13

    When executed on a local machine, why doesn't this stored procedure find itself running?

  • SQLRNNR

    SSC Guru

    Points: 281243

    David.Lavers (9/27/2010)


    When executed on a local machine, why doesn't this stored procedure find itself running?

    The script excludes itself from the result set.

    where s.is_user_process=1 AND s.session_Id NOT IN (@@SPID)

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Preet_S

    SSCrazy

    Points: 2124

    ianstirk (4/6/2010)


    Hi Jason,

    I'm glad you liked the article.

    There is an updated version of this script, without the deprecated objects, given in the book I am writing "SQL Server DMVs in Action". You can get the first chapter free here: http://www.manning.com/stirk

    Thanks

    Ian

    Hi Ian

    Will you post the updated script here, please ?

    Cheers

    Preet

  • ianstirk

    Ten Centuries

    Points: 1310

    Hi Preet,

    below is a version that uses only DMVs/DMFs, from section 5.9.1 of the book (http://www.amazon.com/SQL-Server-DMVs-Action-Management/dp/1935182730)

    Thanks

    Ian

    SELECT

    es.session_id, es.host_name, es.login_name

    , er.status, DB_NAME(database_id) AS DatabaseName

    , SUBSTRING (qt.text,(er.statement_start_offset/2) + 1,

    ((CASE WHEN er.statement_end_offset = -1

    THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

    ELSE er.statement_end_offset

    END - er.statement_start_offset)/2) + 1) AS [Individual Query]

    , qt.text AS [Parent Query]

    , es.program_name, er.start_time, qp.query_plan

    , er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads

    , er.blocking_session_id, er.open_transaction_count, er.last_wait_type

    , er.percent_complete

    FROM sys.dm_exec_requests AS er

    INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id

    CROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt

    CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp

    WHERE es.is_user_process=1

    AND es.session_Id NOT IN (@@SPID)

    ORDER BY es.session_id

  • logicinside22

    SSCrazy Eights

    Points: 9617

    nice script very handy...

    Aim to inspire rather than to teach.
    SQL Server DBA

Viewing 10 posts - 31 through 40 (of 40 total)

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