How to identify longest running queries in SQL Server?

There are two ways to identify slow running queries

1)      Profiler (By using duration of the query)

2)      sys.dm_exec_query_stats, sys.dm_exec_sql_text and sys.dm_exec_requests DMVs etc

3)      DBCC OPENTRAN

Example 1-

SELECT  creation_time

        ,last_execution_time

        ,total_physical_reads

        ,total_logical_reads

        ,total_logical_writes

        , execution_count

        , total_worker_time

        , total_elapsed_time

        , total_elapsed_time / execution_count avg_elapsed_time

        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,

         ((CASE statement_end_offset

          WHEN -1 THEN DATALENGTH(st.text)

          ELSE qs.statement_end_offset END

            - qs.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

ORDER BY total_elapsed_time / execution_count DESC;

Example 2-

                        select *, sql_handle from sys.dm_exec_requests

                 select  * from sys.dm_exec_sql_text (0x020000004D4F6005A3E8119F3DD3297095832ABE63E312F2)

Example 3

SELECT

    [ds_tst].[session_id],

    [ds_es].[login_name] AS [Login Name],

    DB_NAME (ds_tdt.database_id) AS [Database],

    [ds_tdt].[database_transaction_begin_time] AS [Begin Time],

    [ds_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],

    [ds_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],

    [ds_est].text AS [Last T-SQL Text],

    [ds_eqp].[query_plan] AS [Last Plan]

FROM

    sys.dm_tran_database_transactions [ds_tdt]

JOIN

    sys.dm_tran_session_transactions [ds_tst]

ON

    [ds_tst].[transaction_id] = [ds_tdt].[transaction_id]

JOIN

    sys.[dm_exec_sessions] [ds_es]

ON

    [ds_es].[session_id] = [ds_tst].[session_id]

JOIN

    sys.dm_exec_connections [ds_ec]

ON

    [ds_ec].[session_id] = [ds_tst].[session_id]

LEFT OUTER JOIN

    sys.dm_exec_requests [ds_er]

ON

    [ds_er].[session_id] = [ds_tst].[session_id]

CROSS APPLY

    sys.dm_exec_sql_text ([ds_ec].[most_recent_sql_handle]) AS [ds_est]

OUTER APPLY

    sys.dm_exec_query_plan ([ds_er].[plan_handle]) AS [ds_eqp]

ORDER BY     [Begin Time] ASC;

4)      DBCC OPENTRAN

Use this to clean buffer data for testing purpose

Dbcc Dropcleanbuffers

BEGIN TRAN
UPDATE Person.Address SET AddressLine2 = '' WHERE AddressID = 112

Now open another connection in SQL and execute following script.

DBCC OPENTRAN

Result: - Using SPID can get Query-

Transaction information for database 'AdventureWorks2012'.

Oldest active transaction:

 SPID (server process ID): 57

 UID (user ID) : -1

 Name : user_transaction

 LSN : (163:324:1)

 Start time : Jan 16 2014 4:34:58:813PM

 SID : 0x0105000000000005150000001650720d0cca921474e68766e8030000

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Please commnet if you know any other way to get or  identify longest running queries in SQL Server.- Jainendra Verma