Post reply

What SQL Statements Are Currently Executing?

  • December 15, 2009 at 1:14 am

    #1092378

    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

  • April 6, 2010 at 6:16 pm

    #1146911

    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

  • April 6, 2010 at 8:59 pm

    #1146960

    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

  • April 6, 2010 at 9:03 pm

    #1146962

    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

  • April 21, 2010 at 11:42 am

    #1155558

    Good work. Very useful script.

    Thanks!

  • September 27, 2010 at 4:33 pm

    #1228081

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

  • September 27, 2010 at 5:00 pm

    #1228090

    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

  • June 3, 2011 at 4:21 am

    #1333569

    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

  • June 3, 2011 at 9:45 am

    #1333771

    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

  • January 20, 2012 at 9:30 am

    #1436605

    nice script very handy...

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

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