What SQL Statements Are Currently Executing?

  • Adam Gojdas

    SSCommitted

    Points: 1766

    You will need 'VIEW SERVER STATE' permission to run the query that is in the proc.

  • Raj Gujar

    SSC Veteran

    Points: 213

    This was a good one , Helped me quite a bit.

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Very nice article and very useful query.

    Just out of curiosity, why did you decide to make it a procedure instead of a view?

    Naturally you would lose that order by, but since you may wish to see it ordered by other things such as User or Status anyway it seems the view would be more flexible.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • ianstirk

    Ten Centuries

    Points: 1310

    Hi,

    I’m glad you liked the article, it’s quite interesting to see what is happening with SQL Server and SQL.

    I hadn’t really thought about creating a view, I typically create utilities as procedures by default, but maybe the advantages of permissioning and sorting make a view more appropriate…

    Thanks

    Ian

  • Adam Gojdas

    SSCommitted

    Points: 1766

    I am planning on using a bit of a modified version that will filter to a specific DB. As a developer I am in need of just seeing what is on the DB I am working on. Here is a crack at a revision to the code to allow this.

    IF OBJECT_ID(N'[dbo].[dba_WhatSQLIsExecuting]') IS NULL

    BEGIN

    EXEC ('CREATE PROCEDURE

    [dbo].[dba_WhatSQLIsExecuting]

    AS BEGIN SELECT ''STUB'' END');

    END;

    GO

    ALTER PROC [dbo].[dba_WhatSQLIsExecuting]

    --Inputs

    @specificDB nvarchar(128) = NULL

    AS

    /*--------------------------------------------------------------------

    Purpose: Shows what individual SQL statements are currently executing.

    ----------------------------------------------------------------------

    Parameters: None.

    Revision History:

    24/07/2008 Ian_Stirk@yahoo.com Initial version

    28/10/2008 added filter for a specific DB

    Example Usage:

    To get data from all DBs on that instance

    1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting

    To get data from a specific DB

    2. DECLARE @thisDB nvarchar(128); SET @thisDB = DB_NAME();

    exec YourServerName.master.dbo.dba_WhatSQLIsExecuting @thisDB

    ---------------------------------------------------------------------*/

    BEGIN

    DECLARE @thisSPID int;

    SET @thisSPID = @@SPID;

    -- Do not lock anything, and do not get held up by any locks.

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    -- What SQL Statements Are Currently Running?

    SELECT

    session_Id [SPID]

    ,ecid [ecid]

    ,DB_NAME(sp.dbid) [Database]

    ,nt_username [User]

    ,er.status [Status]

    ,wait_type [Wait]

    ,SUBSTRING (qt.text,

    er.statement_start_offset/2,

    (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)

    [Individual Query]

    ,qt.text [Parent Query]

    ,program_name [Program]

    ,Hostname

    ,nt_domain

    ,start_time

    FROM sys.dm_exec_requests [er]

    INNER JOIN

    sys.sysprocesses [sp]

    ON er.session_id = sp.spid

    CROSS APPLY

    sys.dm_exec_sql_text(er.sql_handle) [qt]

    WHERE session_Id > 50 -- Ignore system spids.

    AND session_Id NOT IN (@thisSPID) -- Ignore this current statement.

    --when a specific DB is supplied filter for it, ohterwise get all DBs

    AND CASE WHEN @specificDB IS NULL THEN N'' ELSE DB_NAME(sp.dbid) END

    = CASE WHEN @specificDB IS NULL THEN N'' ELSE @specificDB END

    ORDER BY session_Id, ecid

    END

  • CR8NK

    Old Hand

    Points: 309

    Great code, thanks!

    I would also propose creating a view for this instead of a proc. It fits in with the DMVs provided by SQL and allows for easier joining to other system data and extensibility (ORDER BY, Filtering by DB, etc.)

    thanks,

    Creighton

  • Anipaul

    SSC-Insane

    Points: 24681

    Very useful and relevant article ...:)

  • Troy Gatchell-386101

    SSC Rookie

    Points: 45

    If you want to use this query going forward you should join on sys.dm_exec_sessions instead of sys.sysprocesses. This is a backwards compatibility view and is scheduled for deprecation. I wrote a similar article about 2 years ago for SSWUG (Custom Scripts to get DMV results fast). It shows you how to pull the query plan for currently executing requests as well for further debug.

    For more info use: sys.dm_exec_query_stats which has a plan_handle to do a cross apply on sys.dm_exec_query_plan to get the XML format of the execution plan.

    You can look all this up in books online.

  • Babu-563807

    Grasshopper

    Points: 17

    It really helped me.

    Thank you very much 🙂

    Regards, Babu

  • chandresh_kumar

    Newbie

    Points: 5

    Very interesting and knowledge increasing article.

    Thanks.

    Chandresh Kumar Chhatlani

    3 PA 46

    Prabhat Nagar

    Sector-5, Hiran Magari

    UDAIPUR (Rajasthan) - India

    313002

    http://chandreshkumar.wetpaint.com

  • Rod at work

    SSC-Dedicated

    Points: 33401

    I've just read this article, copied the code and created the SP in my master database. Using the code I created the SP dba_WhatSQLIsExecuting, and ran it. However, nothing showed up. Is it because I ran it, in the master database? Do I have to be in one of our user databases in order for something to show up?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Kev Riley

    SSCrazy Eights

    Points: 9026

    No it's not because you ran it in the master db - it will report activity across all databases.

    It could be that nothing was actually running at that point. Try running over and over to see if you get anything.

    Alternatively remove the line that ignores the current statement - then at least you should see yourself.

  • Steven Hanley

    SSC Rookie

    Points: 48

    If I'm wondering what's running, I usually want a little extra information about those queries. Here's a very similiar version with some of the extra information I like to see:

    set transaction isolation level read uncommitted

    select s.[host_name]

    , s.login_name

    -- , s.is_user_process

    , s.program_name

    , r.command

    , r.status

    , s.session_id

    , r.blocking_session_id

    -- , r.request_id

    , datediff( ms, r.start_time, getDate() ) as age_ms

    , r.total_elapsed_time

    , r.cpu_time

    , r.percent_complete

    , r.row_count

    , r.granted_query_memory

    , r.logical_reads

    , txt.[text] as query_text

    from sys.dm_exec_requests as r

    join sys.dm_exec_sessions as s on (s.session_id=r.session_id)

    cross apply sys.dm_exec_sql_text( r.sql_handle ) as txt

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

    order by datediff( ms, r.start_time, getDate()) desc

  • Rod at work

    SSC-Dedicated

    Points: 33401

    kevriley (12/11/2009)


    No it's not because you ran it in the master db - it will report activity across all databases.

    It could be that nothing was actually running at that point. Try running over and over to see if you get anything.

    Alternatively remove the line that ignores the current statement - then at least you should see yourself.

    OK, I did as you suggested, and at first didn't see any activity. But it is Friday, not all of our users are here, and so I decided to get into one of our apps and run a Crystal Report there that I know is a hog. Sure enough, that SP started showing me the line being executed, what database, etc. Cool. I like it!

    Kindest Regards, Rod Connect with me on LinkedIn.

  • JStiney

    Say Hey Kid

    Points: 671

    Thanks for the script. I can see that it will be useful.

    This is the kind of script that I will create as a .sql file in a regular network directory for sql scripts. That way you can execute it on any one of your servers just by pulling it up in Management Studio and you can easily change it as needed and either save the changes or not. If you make it a stored proc or a view, then you are limited to one server or you have to create and maintain it in many places.

Viewing 15 posts - 16 through 30 (of 40 total)

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