Find slow running queries on MS SQL Server

Source

Below query will helps you find the queries which have taken a lot of time in milliseconds. To confirm that you have query performance issues on your SQL Server instance start by examining queries by their execution time.

SELECT
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1,
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '),
      1, 512) AS statement_text
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

ITCanopy