Skip to content

Reports via SQL Queries

Jump to bottom
JosephPilov-MSFT edited this page Dec 12, 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 longest-running 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))/(CASE WHEN SUM(b.Duration)/1000 = 0 THEN 1 ELSE SUM(b.Duration)/1000 END )))*100 WaitPercentage, 
		SUM(b.Reads) Reads, 
		COUNT(*) Executions, 
		(SUM(b.Duration)/1000)/ (CASE WHEN COUNT(*) = 0 THEN 1 ELSE COUNT(*) END) 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)

Replace <hash_id> with a valid HashID from ReadTrace.tblBatches that matches a query text (NormText) of your choice. You may get HashID values from the top longest-running queries by using Top 50 longest-running queries 

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 = <hash_id> 
ORDER BY duration DESC

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

Replace <hash_id> with a valid HashID from ReadTrace.tblBatches that matches a query text (NormText) of your choice. You may get HashID values from the top longest-running queries by using Top 50 longest-running queries 

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
BEGIN
   EXEC DataSet_WaitStats_WaitStatsTop5Categories
END

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

Replace <hash_id> with a valid HashID from ReadTrace.tblBatches that matches a query text (NormText) of your choice. You may get HashID values from the top longest-running queries by using Top 50 longest-running queries 

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 r.runtime between b.StartTime_local and EndTime_local
WHERE HashID = <hash_id>  -- change this value 
	AND task_state != 'running' AND task_state != 'runnable'

If you are using an older version of SQLNexus which did not create the StartTime_local and EndTime_local columns, use this query and you have to modify datediff to account for Xevents (UTC) times vs. local server times Also, replace <hash_id> with a valid HashID from ReadTrace.tblBatches that matches a query text (NormText) of your choice. You may get HashID values from the top longest-running queries by using Top 50 longest-running queries 

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  
WHERE HashID = <hash_id>  -- change this value 
	AND task_state != 'running' AND task_state != 'runnable'

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

Replace <hash_id> with a valid HashID from ReadTrace.tblBatches that matches a query text (NormText) of your choice. You may get HashID values from the top longest-running queries by using Top 50 longest-running queries 

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 with a valid HashID
      AND r.runtime between t.starttime AND t.endtime)
ORDER BY runtime asc

Aggregate waits AND the waiting queries from tbl_batches table (To get an idea where issue may be)

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

How many occurrences of a particular wait type was encountered?

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 sessions and queries

Find blocked sessions 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

Find all the statements that belong to a single batch (if statement events were collected)

Please replace with a valid BatchSeq from ReadTrace.tblBatches. You can find what query text corresponds to a batch by joining with ReadTrace.tblUniqueBatches and look at OrigText or NormText columns.

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> 
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 and normtext
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 @minruntime datetime, @maxruntime datetime, @cpu_count int 
SELECT @minruntime = MIN(runtime), @maxruntime = 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 = @minruntime) AS a
INNER JOIN  
	(SELECT * FROM  tbl_OS_WAIT_STATS b WHERE b.runtime =@maxruntime) 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) ) AS  t2
       ON t1.row = t2.row 
	   AND t1.[name]=t2.[name]
ORDER BY delta_spins DESC

Performance Comparison between two log collections (Slow and Fast for example).

Using SQL Nexus import two separate Pssdiag/SQL LogScout collections into two differenent databases. Then in the query below replace the database name DB_MO_Slow and DB_MO_Fast with your SQLNexus database names.

SELECT SlowRun_AvgDuration, FastRun_AvgDuration, 
  SlowRun_AvgDuration - FastRun_AvgDuration  AS 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 collection 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 
  AS [CPU from Queries as Percent of Total CPU Capacity]

Calculate the cumulative CPU usage of the top 100 queries as percent of CPU capacity

DECLARE @collection_dur1 int, @cpu_count1 int

--calculate the total collection 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 X queries
DECLARE @top_cpu_qrs decimal(10,4)

SELECT @top_cpu_qrs = 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 @top_cpu_qrs CPU_in_minutes
--What percentage of total CPU capacity was used by SQL Server
SELECT convert (decimal(10,4), (@top_cpu_qrs/(@cpu_count1 * @collection_dur1) ) )*100 PercentSQLCPU_of_TotalCPUCapacity

Pulling query plan from Pssdiag/SQLLogScout (if captured) for a specific statement

Replace <stmt_seq> with a value you pulled from ReadTrace.tblStatements

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 = <stmt_seq>
ORDER BY b.roworder asc

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

Statistics information about a particular database

SELECT Database_Id,Database_Name ,Object_Name ,object_id ,stats_id ,
       last_updated ,rows ,rows_sampled ,steps ,unfiltered_rows ,
	   modification_counter ,persisted_sample_percent
FROM dbo.tbl_dm_db_stats_properties
WHERE Database_Name = 'YourDBName'

SQL CPU Usage on the system

SELECT EventTime, system_idle_cpu, sql_cpu_utilization
FROM tbl_SQL_CPU_HEALTH cpu1
ORDER BY EventTime

[Note!] Be aware that in some cases this data may not be accurate AS it is being sourced FROM sys.dm_os_ring_buffers. The DMVs has been deprecated AND sometimes not reporting correct results for CPU FROM SQL Server.

Loaded modules inside SQL Server (non-Microsoft)

--loaded modules
SELECT name,*
FROM [dbo].[tbl_dm_os_loaded_modules]
WHERE company NOT LIKE '%Microsoft%'

Hash warnings analysis: queries that produced hash warnings

SELECT ts.subclass_name AS [Hash Warning type]
             ,count(a.seq) AS [# Hash Warning events]
             ,substring(c.Origtext,1,3000) AS [Query Text]
FROM readtrace.tblInterestingEvents a
       INNER JOIN readtrace.tblBatches b ON a.Session = b.Session AND a.DBID = b.DBID 
	     AND a.Seq BETWEEN b.StartSeq AND b.EndSeq
       INNER JOIN readtrace.tblUniqueBatches c ON b.HashID = c.HashID
       INNER JOIN sys.trace_events te ON te.trace_event_id = a.EventID
       LEFT OUTER JOIN sys.trace_subclass_values ts 
	     ON ts.trace_event_id = te.trace_event_id 
	       AND ts.subclass_value=a.EventSubclass
WHERE te.name = 'Hash Warning'
GROUP BY substring(c.Origtext,1,3000)
             ,ts.subclass_name
ORDER BY [# Hash Warning events] DESC

Sort warnings analysis: queries that produced sort warnings

SELECT ts.subclass_name AS [Sort Warning type]
             ,count(a.seq) AS [# Sort Warning events]
             ,substring(c.Origtext,1,3000) AS [Query Text]
FROM readtrace.tblInterestingEvents a
       INNER JOIN readtrace.tblBatches b ON a.Session = b.Session 
	     AND a.DBID = b.DBID 
	     AND a.Seq BETWEEN b.StartSeq AND b.EndSeq
       INNER JOIN readtrace.tblUniqueBatches c ON b.HashID = c.HashID
       INNER JOIN sys.trace_events te ON te.trace_event_id = a.EventID
       LEFT OUTER JOIN sys.trace_subclass_values ts 
	     ON ts.trace_event_id = te.trace_event_id 
		   AND ts.subclass_value=a.EventSubclass
WHERE te.name = 'Sort Warnings'
GROUP BY substring(c.Origtext,1,3000)
             ,ts.subclass_name
ORDER BY [# Sort Warning events] DESC

Cross Join analysis: batches that encountered Missing Join Predicate errors

SELECT  count(a.seq) AS [# Cross Join Warning events]
        ,substring(c.Origtext,1,3000) AS [Query Text]
FROM readtrace.tblInterestingEvents a
       INNER JOIN readtrace.tblBatches b ON a.Session = b.Session 
         AND a.DBID = b.DBID 
         AND a.Seq BETWEEN b.StartSeq AND b.EndSeq
       INNER JOIN readtrace.tblUniqueBatches c ON b.HashID = c.HashID
       INNER JOIN sys.trace_events te ON te.trace_event_id = a.EventID
       LEFT OUTER JOIN sys.trace_subclass_values ts 
	     ON ts.trace_event_id = te.trace_event_id 
		   AND ts.subclass_value=a.EventSubclass
WHERE te.name = 'Missing Join Predicate'
GROUP BY substring(c.Origtext,1,3000)
ORDER BY [# Cross Join Warning events] DESC

Queries that encountered various Errors

SELECT        SUBSTRING(a.TextData, 1,50)  [Exception message]
             ,SUBSTRING(c.Origtext,1,3000) AS [Query Text]
FROM readtrace.tblInterestingEvents a
       INNER JOIN readtrace.tblBatches b ON a.Session = b.Session  
	     AND a.DBID = b.DBID  
		 AND a.Seq BETWEEN b.StartSeq  AND b.EndSeq
       INNER JOIN readtrace.tblUniqueBatches c ON b.HashID = c.HashID
       INNER JOIN sys.trace_events te ON te.trace_event_id = a.EventID
       LEFT OUTER JOIN sys.trace_subclass_values ts 
	     ON ts.trace_event_id = te.trace_event_id  
	       AND ts.subclass_value=a.EventSubclass
WHERE te.name = 'Exception'
  AND isnull(a.Error,0) not in ( 208 -- Missing objects
                                 ,7969  -- No active open transactions (DBCC OPENTRAN)
                                 ,2812  -- Could not find Stored Procedure
                                 ,2714   -- Object already exists
             )

Missing Indexes on the system

These are missing index since the start of SQL Server

IF OBJECT_ID ('tbl_MissingIndexes') IS NOT NULL
BEGIN
	DECLARE @max_datetime DATETIME
	SELECT @max_datetime = MAX(runtime) FROM tbl_MissingIndexes

	SELECT TOP 30 create_index_statement, improvement_measure, user_seeks, user_scans, runtime, object_id 
	FROM tbl_MissingIndexes
	WHERE runtime = @max_datetime
	ORDER BY improvement_measure DESC
END
Clone this wiki locally