How to get list of the longest queries in SQL Server

microsoft_sql_server_icon (1)

If you have problem with your database server performance and you do not know where to start your investigation you can use below query to have some clue.

SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count / 1000000.0 AS AverageSecond
    ,qs.total_elapsed_time / 1000000.0 AS TotalSecond
    ,qt.text AS Query
    ,o.name AS OBJECTName
    ,DB_NAME(qt.dbid) DatabaseName
FROM 
    sys.dm_exec_query_stats qs
        CROSS APPLY
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
        LEFT OUTER JOIN 
    sys.objects o ON qt.objectid = o.object_id
ORDER BY AverageSecond DESC