SQL Server Identify Long Running Queries

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;

 

Regards

Ritesh Medhe

Like us on FaceBook Follow us on Twitter

Join the fastest growing SQL Server group on FaceBook

   

5 Comments on “SQL Server Identify Long Running Queries”

  1. Hi Ritesh,

    Good article. Just a quick question. Is it possible for a Non_clustered index to create performance issues like a query running for indefinite time?

    In such a case how would i trouble shoot this issue?

    Thanks

    Balaji. V

  2. hi,

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 – The specified network name is no longer available.)
    getting this error but there is no error log.

    suggest help what to do in this situvation?

  3. Dinesh,

    Make sure the priority boost is disabled. It is an sp_configure option. I can also be changed in SSMS: Right click server==>properties==>processors==> boost sql priority.

    sp_configure ‘priority boost’

    I inherited a server with this turned on and I had a heck of a time resolving the communication link / tcp ip stack issues.

Leave a Reply

Your email address will not be published.