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