Friday, January 27, 2012
Long running queries
One of the big challenges of a DBA is to maintain application’s performance.
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.
-- 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
, SUBSTRING (qt.text, qs.statement_start_offset / 2,
WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
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;