Home > SQLServer > SQL Server – Finding currently running queries

SQL Server – Finding currently running queries


You can use sys.dm_exec_requests DMV to list all requests. This view also contains a hash map of the SQL text – sql_handle, which can be passed to DMF sys.dm_exec_sql_text to get the query text:

SELECT      REQUEST.session_id, REQUEST.start_time, QUERY.text

FROM        sys.dm_exec_requests REQUEST

CROSS APPLY

            sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY

Result Set:

session_id start_time               text

———- ————             ——————

54         2011-08-10 20:28:17.840  SELECT REQUEST.session_id, REQUEST.start_time,
                      QUERY.text
                     
FROM   sys.dm_exec_requests REQUEST
                     
CROSS APPLY

                                    sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY

56         2011-08-10 20:27:02.380  USE SqlAndMe SELECT ProductID, Name
                                                               FROM   ProductList

 

(2 row(s) affected)

 

In case you are trying to find the query which is causing blocking, you can find the blocking session id by using sp_who or sp_who3.

Once you locate the blocking session id, it can be used to filter above results:

SELECT      REQUEST.session_id, REQUEST.start_time, QUERY.text

FROM        sys.dm_exec_requests REQUEST

CROSS APPLY

            sys.dm_exec_sql_text(REQUEST.sql_handle) QUERY

WHERE REQUEST.session_id = 58

Result Set:

session_id start_time               text

———- ————             ——————

58         2011-08-10 20:39:17.773  UPDATE ProductList  SET  Name = 'Bearing Ball'

(1 row(s) affected)

 

Hope This Helps!

Vishal

  1. Nickie
    11.08.2011 02:17 | #1

    I am novice in sql. i trying to get table like this :
    Any ways i can do this ?
    thanks

    Relation hhc101 hhc166 hhc201 total
    1 1435 18 1 1455
    2 430 2 0 434
    3 1121 0 0 1124
    Grand total 2986 20 1

    my original table is
    GrpNum MemberNum Relation
    HHC101 397 3
    HHC101 663 1
    HHC101 707 2
    HHC101 903 3
    HHC166 1206 3
    hhc166 1326 2
    HHC166 1800 1
    HHC201 1839 3
    HHC201 1951 1
    HHC101 2098 1
    HHC101 2146 3

  2. Vishal
    11.08.2011 08:52 | #2

    You can use PIVOT to get the desired result, check the post on PIVOT for more information:

    SELECT Relation,
    COALESCE([HHC101],0) AS [HHC101],
    COALESCE([HHC166],0) AS [HHC166],
    COALESCE([HHC201],0) AS [HHC201]
    FROM #TempTable
    PIVOT
    (
    SUM(MemberNum)
    FOR GrpNum IN([HHC101], [HHC166], [HHC201])
    ) AS Members

    However, this still does not give you Totals. To generate the Totals, you should use GROUPING SETS, you will need to format the output in your front-end application:

    SELECT COALESCE(GrpNum, 'Total'), COALESCE(Relation, 'Total'), SUM(MemberNum)
    FROM #TempTable
    GROUP BY GROUPING SETS ((GrpNum), (Relation), ())

    GROUPING SETS : https://sqlandme.com/2011/07/12/sql-server-tsql-group-by-grouping-sets/
    PIVOT : https://sqlandme.com/2011/04/20/tsql-transpose-data-using-using-pivot-and-unpivot/

  3. Vishal
    11.08.2011 09:19 | #3

    I think this should give all data as required:

    WITH MyCTE (GrpNum, Relation, MemberNum)
    AS
    (
    SELECT COALESCE(GrpNum, 'Total'), COALESCE(Relation, 'Total'), SUM(MemberNum)
    FROM #TempTable
    GROUP BY GROUPING SETS ((GrpNum, Relation), (GrpNum), (Relation), ())
    )
    SELECT Relation,
    COALESCE([HHC101],0) AS [HHC101],
    COALESCE([HHC166],0) AS [HHC166],
    COALESCE([HHC201],0) AS [HHC201],
    COALESCE([Total],0) AS [Total]
    FROM MyCTE
    PIVOT
    (
    MAX(MemberNum)
    FOR GrpNum IN([HHC101],[HHC166],[HHC201],[Total])
    ) AS Members

  4. Kevin Robinson
    06.03.2012 02:33 | #4

    Thanks for share this excellent post with us is really interesting, keep up the good work

  5. Golu Singh
    23.03.2012 03:43 | #5

    Very nice and informative article that beautifully elaborate the basics of Select query command using with ado.net. Some other articles also explained very well on executing select query in sql server using ado.net…
    http://mindstick.com/Blog/172/Executing%20select%20query%20in%20SqlServer%20using%20ADO%20NET
    http://msdn.microsoft.com/en-us/library/aa259187%28v=sql.80%29.aspx

  1. No trackbacks yet.