What SQL Statements Are Currently Executing?

  • 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
  • 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

  • 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

  • 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

  • Good work. Very useful script.

    Thanks!

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

  • 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

  • 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

  • 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

  • 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