I am using SQL Server 2012 and I want to get hold of the queries which are taking longer to execute along with the queries which are causing blocking on the server. When I look at the server for the running queries to identify any blocking I don't find any query.

I have setup extended events and getting only high level information from that. The tables which are getting timeouts but I need information which tells more about the queries which caused the timeouts.

Any suggestions???


You can make use of the dmv's sys.dm_exec_query_stats and sys.dm_exec_requests to analyse the resource consuming queries along with their duration for what long:

Use the query below to capture the same:

-- Execute the query inside target database
      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, 
      (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
      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
      qt.dbid = DB_ID()
      average_seconds DESC;

Moreover would suggest if you are using extended events, you can capture the same. For more on how to set up and use the same, please read this whitepaper from Jonathan Using SQL Server 2008 Extended Events which should get you going alon with here

Also, when the queries run , and what all process run at the background and to check if blocking is really out there along with any particular waits , would suggest you to use SP_WHOISACTIVE from How to Use sp_WhoIsActive to Find Slow SQL Server Queries

Also, if you prefer to use Profiler you can track and troubleshoot the same as explained in How to Identify Slow Running Queries with SQL Profiler

  • @MySQL DBA, you can mark it as an answer if you're question seems to be answered or mark as an Upvote if the information provided helps, just in case it will be helpful for the readers visiting the forum in search of such queries. Thanks! – KASQLDBA Aug 14 '15 at 7:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.