Skip to content

Reports via SQL Queries

Jump to bottom
JosephPilov-msft edited this page Mar 7, 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 (Bottleneck Analysis)

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 on a bottleneck 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 Server

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 Server

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)/@cpus  spins_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

Comparison between two databases (slow and fast for example). Just replace the database name after the FROM.

select SlowRun_AvgDuration, FastRun_AvgDuration, SlowRun_AvgDuration - FastRun_AvgDuration  SlowRunMinusFastRun_Delta_AvgDuration, SlowRun_AvgCPU, 
FastRun_AvgCPU, SlowRun_AvgCPU - FastRun_AvgCPU SlowRunMinusFastRun_AvgCPU ,SlowRun_Executions, FastRun_Executions, NormText 
from (
	select top 100 sum(Duration)/1000 Duration, SUM(CPU) CPU, sum(Reads) Reads, COUNT(*) SlowRun_Executions, (sum(Duration)/1000)/COUNT(*) SlowRun_AvgDuration, sum(CPU)/count(*) SlowRun_AvgCPU /*, substring(NormText, 1, 120) NormText*/ , t.HashID
	from [DB_MO_Slow].ReadTrace.tblBatches t 
	join [DB_MO_Slow].ReadTrace.tblUniqueBatches u
	on t.HashID = u.HashID
	where u.normtext not like '%SP_MSFOREACHDB%' 
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_SQLDIAG%' 
	and u.NormText not like '%repl%'
	and u.NormText not like '%distribution%'
	and u.NormText not like 'EXECUTE MSDB.DBO.SP_SQLAGENT_LOG_JOBHISTORY%'
	and u.NormText not like 'EXECUTE MSDB.DBO.SP_SQLAGENT_GET_PERF_COUNTERS%'
	and u.NormText not like 'EXECUTE MSDB.DBO.SP_HELP_JOBSTEP%'
	and u.NormText not like 'DECLARE @DBNAME SYSNAME DECLARE @CMD NVARCHAR({##}) DECLARE DB_CURSOR CURSOR FOR SELECT NAME FROM MASTER.DBO.SYSDATABASES %'
	and u.NormText not like 'PRINT {STR} PRINT {STR} SELECT LEFT (NAME, {##}) AS NAME, DBID, CMPTLEVEL, CONVERT (INT, (SELECT SUM (CONVERT (BIGINT%'
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_TRACE%'
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_SET_BLK_THRESHOLD09%'
	and u.NormText not like '%SP_HELPDB%'
	and u.NormText not like '%SP_DIAG_TRACE_FLAG%'
	and u.NormText not like 'MASTER.DBO.XP_MSVER%'
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_CODE_RUNNER%'
	and u.NormText not like '%DBCC SQLPERF%'
	and u.NormText not like '%XP_MSVER%'
	and u.NormText not like '%DBCC TRACESTATUS%'
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_BLOCKER_PSS%'
	and u.NormText not like 'USE MASTER;DECLARE @ISREADONLY AS INT; SELECT @ISREADONLY = COUNT(*) FROM SYSDATABASES AS DATABASES%'
	and u.NormText not like '%TEMPDB.DBO.TRACEFLAGORIGINALSTATUS%'
	and u.NormText not like 'SELECT VALUE FROM MASTER.DBO.SYSCONFIGURES WHERE CONFIG%'
	and u.NormText not like '%PRINT {STR}%'
	and u.normtext not like '%SP_GET_DISTRIBUTOR%'
	and u.normtext not like '%MSGETVERSION%'
	and u.normtext not like '%SP_GET_DTSPACKAGE%'
	and u.normtext not like '%BACKUPSET%'
	and u.NormText not like '%#MSDBFILELIST%'
	and u.normtext not like '%SYSALTFILES%'
	and u.normtext not like '%SYSDATABASES%'
	and u.NormText not like '%SP_MSSQLDMO%'
	and u.NormText not like '%CREATE TABLE #ERRORLOG%'
	and u.normtext not like '%HASMEMORYSCRIBBLERISSUE%'
	and u.normtext not like '%SYSCURCONFIGS%'
	and u.normtext not like '%SP_PERF_STATS%'
	and u.normtext not like '%FN_TRACE_GETINFO%'
	and u.normtext not like '%##MAXNAMEWIDTH%'
	group by u.NormText, t.HashID
	order by duration desc
) slow 
join
(
	select top 100 sum(Duration)/1000 Duration, SUM(CPU) CPU, sum(Reads) Reads, COUNT(*) FastRun_Executions, (sum(Duration)/1000)/COUNT(*) FastRun_AvgDuration, sum(CPU)/count(*) FastRun_AvgCPU, substring(NormText, 1, 120) NormText , t.HashID
	from [DB_MO_Fast].ReadTrace.tblBatches t 
	join [DB_MO_Fast].ReadTrace.tblUniqueBatches u
	on t.HashID = u.HashID
	where u.normtext not like '%SP_MSFOREACHDB%' 
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_SQLDIAG%' 
	and u.NormText not like '%repl%'
	and u.NormText not like '%distribution%'
	and u.NormText not like 'EXECUTE MSDB.DBO.SP_SQLAGENT_LOG_JOBHISTORY%'
	and u.NormText not like 'EXECUTE MSDB.DBO.SP_SQLAGENT_GET_PERF_COUNTERS%'
	and u.NormText not like 'EXECUTE MSDB.DBO.SP_HELP_JOBSTEP%'
	and u.NormText not like 'DECLARE @DBNAME SYSNAME DECLARE @CMD NVARCHAR({##}) DECLARE DB_CURSOR CURSOR FOR SELECT NAME FROM MASTER.DBO.SYSDATABASES %'
	and u.NormText not like 'PRINT {STR} PRINT {STR} SELECT LEFT (NAME, {##}) AS NAME, DBID, CMPTLEVEL, CONVERT (INT, (SELECT SUM (CONVERT (BIGINT%'
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_TRACE%'
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_SET_BLK_THRESHOLD09%'
	and u.NormText not like '%SP_HELPDB%'
	and u.NormText not like '%SP_DIAG_TRACE_FLAG%'
	and u.NormText not like 'MASTER.DBO.XP_MSVER%'
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_CODE_RUNNER%'
	and u.NormText not like '%DBCC SQLPERF%'
	and u.NormText not like '%XP_MSVER%'
	and u.NormText not like '%DBCC TRACESTATUS%'
	and u.NormText not like 'EXEC TEMPDB.DBO.SP_BLOCKER_PSS%'
	and u.NormText not like 'USE MASTER;DECLARE @ISREADONLY AS INT; SELECT @ISREADONLY = COUNT(*) FROM SYSDATABASES AS DATABASES%'
	and u.NormText not like '%TEMPDB.DBO.TRACEFLAGORIGINALSTATUS%'
	and u.NormText not like 'SELECT VALUE FROM MASTER.DBO.SYSCONFIGURES WHERE CONFIG%'
	and u.NormText not like '%PRINT {STR}%'
	and u.normtext not like '%SP_GET_DISTRIBUTOR%'
	and u.normtext not like '%MSGETVERSION%'
	and u.normtext not like '%SP_GET_DTSPACKAGE%'
	and u.normtext not like '%BACKUPSET%'
	and u.NormText not like '%#MSDBFILELIST%'
	and u.normtext not like '%SYSALTFILES%'
	and u.normtext not like '%SYSDATABASES%'
	and u.NormText not like '%SP_MSSQLDMO%'
	and u.NormText not like '%CREATE TABLE #ERRORLOG%'
	and u.normtext not like '%HASMEMORYSCRIBBLERISSUE%'
	and u.normtext not like '%SYSCURCONFIGS%'
	and u.normtext not like '%SP_PERF_STATS%'
	and u.normtext not like '%FN_TRACE_GETINFO%'
	and u.normtext not like '%##MAXNAMEWIDTH%'
	group by u.NormText, t.HashID
	order by Duration desc
) fast
on slow.hashid = fast.hashid
order by SlowRunMinusFastRun_Delta_AvgDuration asc

CPU used by SQL Server queries as percentage of total CPU capacity

declare @collection_dur int, @cpu_count int, @cpu_usedby_sql decimal(10,4)

--calculate the total colleciton duration
select @collection_dur = DATEDIFF(MINUTE, min(tb.starttime), max(tb.EndTime)) 
from ReadTrace.tblBatches tb

--get the count of CPUs
select @cpu_count = cpu_count from tbl_SYSINFO

--calculate the total CPU used by SQL Server in all completed batches
select @cpu_usedby_sql= SUM(cpu)/60000  from ReadTrace.tblBatches

--What percentage of total CPU capacity was used by SQL Server
select convert (decimal(10,4), (@cpu_usedby_sql/(@cpu_count * @collection_dur) ) )*100 PercentSQLCPU_of_TotalCPUCapacity

Calculate the CPU usage by the top 6 queries

declare @collection_dur1 int, @cpu_count1 int

--calculate the total colleciton duration
select @collection_dur1 = DATEDIFF(MINUTE, min(tb.starttime), max(tb.EndTime)) 
from ReadTrace.tblBatches tb

--get the count of CPUs
select @cpu_count1 = cpu_count from tbl_SYSINFO


--calculate CPU used by top 6 queries
declare @top6_cpu decimal(10,4)
select @top6_cpu = SUM(cpu)/60000.00  from  
(select top 100  sum(Duration)/1000 Duration, SUM(CPU) CPU, sum(Reads) Reads, COUNT(*) Executions, (sum(Duration)/1000)/COUNT(*) AvgDuration, sum(CPU)/count(*) AvgCPU, substring(NormText, 1, 100) NormText 
from ReadTrace.tblBatches t 
	join ReadTrace.tblUniqueBatches u
	on t.HashID = u.HashID
group by u.NormText
order by CPU desc
) as t
select @top6_cpu CPU_min
--What percentage of total CPU capacity was used by SQL Server
select convert (decimal(10,4), (@top6_cpu/(@cpu_count1 * @collection_dur1) ) )*100 PercentSQLCPU_of_TotalCPUCapacity

Pulling query plans from PSSDIAG - if captured - for a particular

SELECT a.[StmtSeq]
      ,b.[EstimateRows]
      ,ROUND(b.[Rows]/(b.[Executes]+0.000000000001),3) as [RowsPerExec]
      ,b.[Rows]
      ,b.[Executes]
      ,c.[StmtText]
      ,c.[StmtID]
      ,c.[NodeID]
      ,c.[Parent]
      ,c.[PhysicalOp]
      ,c.[LogicalOp]
      ,c.[Argument]
      ,c.[DefinedValues]
      ,b.[EstimateRows]
      ,c.[EstimateIO]
      ,c.[EstimateCPU]
      ,c.[AvgRowSize]
      ,c.[TotalSubtreeCost]
      ,c.[OutputList]
      ,c.[Warnings]
      ,c.[Type]
      ,c.[Parallel]
      ,b.[EstimateExecutes]
from readtrace.tblPlans a 
join readtrace.tblPlanRows b on a.seq = b.seq
join readtrace.tblUniquePlanRows c on a.PlanHashId = c.PlanHashId and b.RowOrder = c.RowOrder
where a.stmtseq = 4901976
order by b.roworder asc

--Transforming XML Plan to Legacy
exec msdb.dbo.[usp_TransformShowplanXMLToLegacyShowplan] N'<paste your XML showplan here>'

Clone this wiki locally