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


Categories: SQL Server, SQL Server Administration

Tags: , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: