Skip to content

Reports via SQL Queries

Jump to bottom
Pradeep Madheshiya edited this page Feb 23, 2022 · 36 revisions

Purpose

This page provides queries that you can run directly against the SQL Nexus database to get useful information from your performance data. These queries mimic some of the GUI reports but mostly expand on them or provide more advanced analysis scenarios.

Queries

Below are the most common troubleshooting queries you can use in analyzing the SQL Nexus data:

Top 50 queries by duration (aggregate stats)

select top 50 sum(b.Duration)/1000 Duration_ms, SUM(b.    CPU) CPU_ms, 
	sum(b.Duration)/1000 - sum(b.CPU) WaitTime_ms, convert(decimal(8,2),
	(((sum(b.duration)/1000.00) - sum(b.cpu))/(SUM(b.duration)/1000))*100) waitpercentage, 
	sum(b.Reads) Reads, COUNT(*) Executions, (sum(b.Duration)/1000)/COUNT(*) AvgDuration, 
	sum(b.CPU)/count(*) AvgCPU, substring(ub.NormText, 1, 100) NormText , b.HashID
from ReadTrace.tblBatches b 
	join ReadTrace.tblUniqueBatches ub
	on b.HashID = ub.HashID
	group by ub.NormText, b.HashID
	order by Duration_ms desc

Stats for a specific query (based on HashID pulled form above)

select top 20000 b.Session, b.Duration/1000 Duration, 
	(b.CPU) CPU, (b.Reads) Reads, b.attnseq, b.starttime, 
	b.endtime,   substring(u.OrigText, 1, 1000) OrigText, batchseq 
from ReadTrace.tblBatches b
	join ReadTrace.tblUniqueBatches u
	on b.HashID = u.HashID
	join ReadTrace.tblConnections c
	on b.ConnSeq = c.ConnSeq and b.session = c.session 
	where b.HashID = 5155459154697299715
	order by duration desc

What is the overall Pssdiag/SQLLogScout data collection time - in traces

SELECT Min(tb.StartTime) AS CollectionStartTime, 
       max(tb.EndTime)   AS  CollectionEndTime, 
       DATEDIFF(MINUTE, min(tb.starttime), max(tb.EndTime)) AS CollectionDuration_min
FROM ReadTrace.tblBatches tb

Get the overall waits on server

IF OBJECT_ID ('DataSet_WaitStats_WaitStatsTop5Categories') IS NOT NULL AND OBJECT_ID ('tbl_OS_WAIT_STATS') IS NOT NULL
   EXEC DataSet_WaitStats_WaitStatsTop5Categories

Find any waits for a particular query (using the HashID of query)

select runtime,ecid,blocking_session_id, task_state, wait_type, 
	wait_duration_ms, wait_resource, tran_name, command, request_status 
from tbl_REQUESTS r join ReadTrace.tblBatches b
	on r.session_id = b.Session
	and dateadd(hh, -2, r.runtime) between b.starttime and b.endtime  --have to modify datediff to account for Xevents times vs. server times
where HashID = <hash_id>  -- change this value as per your query returning
	and task_state != 'running' and task_state != 'runnable'

Find any waits for a particular query (using top 20 waits for a particular query)

select runtime,ecid,blocking_session_id, task_state, wait_type, 
	wait_duration_ms, wait_resource, tran_name, command, request_status 
from tbl_REQUESTS r 
	where session_id in (
	select distinct top 20 t.session from ReadTrace.tblBatches t
	where HashID = <hash_id> -- change this value as per your query returning
	and r.runtime between t.starttime and t.endtime)
order by runtime asc

Aggregate waits and the waiting queries from table tbl_batches (To get an idea where is the issue)

select count(*) occurrences, sum(r.wait_duration_ms) WaitDensity_ms, r.wait_type, 
     q.procname, q.stmt_text 
from tbl_REQUESTS r
     join tbl_NOTABLEACTIVEQUERIES q
     on r.session_id = q.session_id
     and r.runtime = q.runtime
where wait_type is not null 
     and wait_type not in ('SP_SERVER_DIAGNOSTICS_SLEEP', 'backupio', 'BROKER_RECEIVE_WAITFOR')
group by r.wait_type, q.procname, q.stmt_text
order by WaitDensity_ms desc

Waits aggregated by wait resource and wait type

select COUNT(*) occurrences, wait_resource, wait_type, 
	max(wait_duration_ms) maxWaitMs
from tbl_REQUESTS 
where wait_type is not null
group by wait_resource, wait_type
order by occurrences desc

Counts per wait type

select count(*) occurrences, wait_type 
from tbl_REQUESTS r
where wait_type is not null
group by wait_type
order by occurrences Desc

Find head blockers and their queries

select runtime, head_blocker_session_id, head_blocker_proc_name,stmt_text as head_blocker_stmt, 
        blocked_task_count, tot_wait_duration_ms as blocked_total_wait_dur_ms, 
	avg_wait_duration_ms as blocked_avg_wait_dur_ms 
from tbl_HEADBLOCKERSUMMARY
order by runtime

Find all blocked spids throughout entire PSSDIAG/SQLLogScout collection and the queries they are running

select * from tbl_REQUESTS r join tbl_NOTABLEACTIVEQUERIES q
	on r.session_id = q.session_id
	and r.runtime = q.runtime
where blocking_session_id <> 0
order by r.rownum

If statement events were collected, find all the statements that belong to a single batch

select sum(cpu) CPU, sum(Duration/1000.0) Duration , count(*) Occurrences, ub.NormText
from ReadTrace.tblStatements b join ReadTrace.tblUniqueStatements ub
	on b.HashID = ub.HashID
where b.BatchSeq = <BatchSeq ID> -- Please put here Batch Sequence ID
group by NormText
order by Duration Desc

Find all the batches executed by a particular application

select * from ReadTrace.tblBatches b 
			  join ReadTrace.tblConnections c
			  on b.ConnSeq = c.ConnSeq and b.session = c.session 
where c.ApplicationName = 'sqlcmd'

Find Application Names

This script finds the top 100 long running queries for which the CPU time is less than 80% of Duration , meaning there was a wait. Then it finds what wait types are responsible for this wait and summarizes the total wait_time by wait type for that query. Feel free to change something else or change the 80% to a smaller percent as these are arbitrary choices. The latter would mean that if say 50% was chosen, then out of the total duration the query ran on the CPU only 50% of the time, and the rest it waited for something.

with BatchesData (Session, starttime, endtime, hashid, cpu,duration, CpuPercentOfDuration, NormText)
as 
(
	select Session, starttime, endtime, b.hashid, cpu,duration, (CPU/Duration)/1000 CpuPercentOfDuration, NormText
	from ReadTrace.tblBatches b join ReadTrace.tblUniqueBatches ub
	           on b.HashID = ub.HashID
	where duration !=0
)
select top 100 max(wait_duration_ms) MaxWaitDuration, r.wait_type, t.NormText--aggreate the duration per wait_type
from tbl_REQUESTS r
       join BatchesData t
       on r.runtime between t.starttime and t.endtime
       and r.session_id = t.Session
where t.cpupercentofduration < 0.80               -- where CPU is less than 80% of duration
and task_state != 'running' and task_state != 'runnable' 
group by wait_type, NormText
order by MaxWaitDuration desc

Find the waits for SQL Sever 2005+

declare @minruntime2005 datetime, @maxruntime2005 datetime, @cpu_count int 
select @minruntime2005 = min(runtime), @maxruntime2005 = MAX(runtime) from tbl_OS_WAIT_STATS
select @cpu_count = PropertyValue from tbl_ServerProperties where PropertyName = 'cpu_count'

select a.[wait_type], (b.[wait_time_ms]-a.[wait_time_ms]) TotalWait_ms_AcrossAllCPUs, 
	DATEDIFF(SECOND,a.runtime,b.runtime) PSSDIAGCollectionTimeMin, (b.[wait_time_ms]-a.[wait_time_ms])/(DATEDIFF(SECOND,a.runtime,b.runtime)*@cpu_count) WaitTime_ms_per_second_per_cpu,
	case when a.[wait_type] in 
	('cxpacket', 'XE_DISPATCHER_WAIT', 'XE_TIMER_EVENT', 'REQUEST_FOR_DEADLOCK_SEARCH','WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 
	'SLEEP_TASK', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'SLEEP_SYSTEMTASK', 'PREEMPTIVE_XE_DISPATCHER', 'SP_SERVER_DIAGNOSTICS_SLEEP', 'LAZYWRITER_SLEEP'
	) 
	then 'IGNORE' End as Ignorable
from 
	(select * from tbl_OS_WAIT_STATS a where a.runtime = @minruntime2005) as a
	inner join 
	(select * from tbl_OS_WAIT_STATS b where b.runtime =@maxruntime2005) as b
on a.[wait_type] = b.[wait_type]
order by TotalWait_ms_AcrossAllCPUs desc

Performance stats by app name

select sum(TotalDuration) Duration_ms, sum(TotalCPU) CPU_ms, sum(TotalReads) Reads, AppName 
from  ReadTrace.tblBatchPartialAggs b inner join
	  ReadTrace.tblUniqueAppNames a on a.iID = b.AppNameID
group by AppName
order by Duration_ms desc

Find SPINLOCKS for SQL 2005+ versions

declare @cpus int
select @cpus = PropertyValue from tbl_ServerProperties
where PropertyName = 'cpu_count'

select  
	t2.[name] as spinlock_name,  cast(cast(t2.spins as float) - cast(t1.spins as float) as bigint) delta_spins,  
	cast (cast(t2.Backoffs as float) - cast (t1.Backoffs as float) as bigint) delta_backoff, 
	DATEDIFF(MI,t1.runtime,t2.runtime) delta_minuntes,
	(cast(cast(t2.spins as float) - cast(t1.spins as float) as bigint) )/DATEDIFF(millisecond,t1.runtime,t2.runtime)/@cpusspins_per_millisecond_per_CPU
from 
	(select row_number () over ( partition by [name]  order by    runtime) row, *  from [tbl_SPINLOCKSTATS] where runtime in (select min(runtime) 
         from tbl_spinlockstats) ) t1
join 
	(select row_number () over ( partition by [name]  order by    runtime) row, *  from [tbl_SPINLOCKSTATS]  where runtime in (select max(runtime) 
         from tbl_spinlockstats) ) t2
         on t1.row = t2.row and t1.[name]=t2.[name]
order by delta_spins desc

Clone this wiki locally