search

microsoft / SqlNexus

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQL LogScout, SQLDiag or PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.
MIT License
348 stars 99 forks source link

Disk IO Analysis Rule is creating false warnings for single spike #257

Closed hacitandogan closed 10 months ago

hacitandogan commented 11 months ago
image

Checking the [dbo].[usp_IOAnalysis] this is due to following portion of the code where we are checking for average to be bigger than threshold for ~1 minute of interval but since there is only one row above the threshold it is returning that peak moment as average as well;

SELECT @prolonged_avg_sec_transfer = AVG(counterValue), @drive = InstanceName
FROM #tmp WHERE CounterDateTime BETWEEN (@T_CounterDateTime - '00:00:30') AND (@T_CounterDateTime + '00:00:30') GROUP BY ObjectName, CounterName, InstanceName
HAVING AVG(counterValue) >= @IO_threshold

image

image

I believe this will be more solid if we make sure there are at least more than 1 rows to calculate the average ;

SELECT @prolonged_avg_sec_transfer = AVG(counterValue), @drive = InstanceName
FROM #tmp WHERE CounterDateTime BETWEEN (@T_CounterDateTime - '00:00:30') AND (@T_CounterDateTime + '00:00:30') GROUP BY ObjectName, CounterName, InstanceName
HAVING AVG(counterValue) >= @IO_threshold AND COUNT(1) >1

hacitandogan commented 10 months ago

PR committed and this is fixed.