Identify long running queries by using T-SQL

Two ways to find out the current running query:

First Query

SELECT s.session_id,
       Db_name(r.database_id)                    AS dbname,
       s.login_name,
       r.blocking_session_id                     AS blkby,
       r.open_transaction_count,
       r.status,
       s.host_name,
       s.program_name,
       r.command,
       (SELECT Substring(TEXT, statement_start_offset / 2, (
                       CASE
                       WHEN statement_end_offset = -1 THEN Len(
                       CONVERT(NVARCHAR(MAX), TEXT)) * 2
                       ELSE statement_end_offset
                       END  statement_start_offset ) / 2 )
         FROM   sys.dm_exec_sql_text(sql_handle)) AS querytext
FROM   sys.dm_exec_sessions s
       JOIN sys.dm_exec_requests r
         ON s.session_id = r.session_id
WHERE  r.session_id = 57 -- SPID


Second Query

DECLARE
        @stmt_start INT,
        @stmt_end INT,
        @sql_handle BINARY(20)

SET @spid = 247 -- SPID SELECT
             @stmt_start = CASE stmt_start
                             WHEN 0 THEN 0
                             ELSE stmt_start / 2
                            END,
             @stmt_end   = CASE stmt_end
                            WHEN -1 THEN -1
                            ELSE stmt_end / 2
                           END
FROM  MASTER.dbo.sysprocesses
WHERE spid = @spid
ORDER BY ecid

SELECT
    Substring(TEXT, Coalesce(Nullif(@stmt_start, 0), 1),
                       CASE @stmt_end
                          WHEN -1 THEN Datalength(TEXT)
                          ELSE ( @stmt_end - @stmt_start )
                       END)
FROM



::fn_get_sql(@sql_handle) Find out who are the TOP 10 most expensive running queries in the history.

USE Master
GO

SELECT
           s.execution_count  AS executioncount, 
           s.max_elapsed_time AS maxelapsedtime, 
           Isnull(s.total_elapsed_time / s.execution_count, 0) 
                              AS avgelapsedtime, 
           s.creation_time    AS logcreatedon, 
           Isnull(s.execution_count / Datediff(s, s.creation_time, Getdate()), 0)     AS frequencypersec
FROM sys.dm_exec_query_stats s 
 CROSS APPLY sys.Dm_exec_sql_text(s.sql_handle) t
ORDER BY s.max_elapsed_time DESC ::fn_get_sql(@sql_handle)