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
                           ,  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;


No comments:

Post a Comment