sql server identify long running queries
It is always a big challenge to maintain application’s performance optimum.
Increasing data on regular basis is often the cause for performance degrades. In most of the cases end users report the slowness in the application, only then DBA’s/Developers jump in and begin the optimization exercise.
Ideally, DBA’s/Developers supporting the application should be the first in identifying the performance problems and should proactively optimize the faulty/poor code.
Well to acheive this there should be a way to identify the queries taking long time. SQL server has been evolving greatly. SQL server 2005 and above are shipped with very helpful DMV (Dynamic management views). These views expose performance related statistics.
Here is the query using couple of such DMV’s .
This query returns top 10(configurable) slow performing queries.
Either tune the query or analyze it in SQL DTA (database tuning advisor) and follow the recommendations.
-- Execute the query inside target database SELECT TOP 10 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds, qs.total_elapsed_time / 1000000.0 AS total_seconds, qs.execution_count, SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query, o.name AS object_name, DB_NAME(qt.dbid) AS database_name 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 WHERE qt.dbid = DB_ID() ORDER BY average_seconds DESC;