Queries Causing Most User Wait Time

Question

Post reply

Queries Causing Most User Wait Time

Johnny B

SSCrazy

Points: 2965
More actions
February 17, 2016 at 11:12 am

#324919

Hi,
I'm exploring SolarWinds DPA tool which is supposed to examine RDBMS from the application perspective. This means, "what is the database and virtual layer doing" to effect the user experience while using an actual application.
Does anyone know a query to return results found in this graph?
I'm interested in either a DMV, TSQL or and Extended Event.
Thank you in advance
John
SQL 2012 Standard VPS Windows 2012 Server Standard

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125523 More actions · Answer 1

I'm not seeing the image.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Johnny B SSCrazy Points: 2965 More actions · Answer 2

What if you right click and open image in a new tab. I tested that and it seems to work.

SQL 2012 Standard VPS Windows 2012 Server Standard

Johnny B SSCrazy Points: 2965 More actions · Answer 3

https://www.dropbox.com/s/dl6j41aeg94t5tx/LongRunningQuery.JPG?dl=0

SQL 2012 Standard VPS Windows 2012 Server Standard

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

They're probably polling sys.dm_os_waiting_tasks, or have an extended event session on wait_completed

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johnny B SSCrazy Points: 2965 More actions · Answer 5

Thanks Gila,

Might you know how to link the SQL being executed with sys.dm_os_waiting_tasks ?

John

SQL 2012 Standard VPS Windows 2012 Server Standard

Eric M Russell SSC Guru Points: 125523 More actions · Answer 6

The DMV sys.dm_exec_query_stats returns returns an assortment of aggregated measurements for each cached execution plan. The column sql_handle is a unique hash key.

https://msdn.microsoft.com/en-us/library/ms189741(v=sql.110).aspx

It can be joined with sys.dm_exec_sql_text on sql_handle to get the sql statement text, and you can order by a measurement columns and get TOP X statements. Below is an example query excerpted from the above MSDN article. You'll need to retrofit this to include whatever measures are of interest, there are many to choose from.

SELECT TOP 5 query_stats.query_hash AS "Query Hash",

SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",

MIN(query_stats.statement_text) AS "Statement Text"

FROM

(SELECT QS.*,

SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,

((CASE statement_end_offset

WHEN -1 THEN DATALENGTH(ST.text)

ELSE QS.statement_end_offset END

- QS.statement_start_offset)/2) + 1) AS statement_text

FROM sys.dm_exec_query_stats AS QS

CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats

GROUP BY query_stats.query_hash

ORDER BY 2 DESC;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho