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
356 stars 101 forks source link

Detect abnormal gaps in data collection script outputs #91

Closed suresh-kandoth closed 1 year ago

suresh-kandoth commented 4 years ago

if a script is supposed to collect every 60 sec but only have data after a 5 min gap, highlight it for awareness - especially high cpu scenarios.

PiJoCoder commented 2 years ago

... and blocking too. We have to create an inventory of which scripts collect multiple snapshots and figure out how far apart the data is collected for them. We could then create the "highest common denominator" as a threshold and check if anything is beyond it and report it.
We also have to think about where to report this - Best Practices report or a new report - perhaps the Autotest that Gambit9009 is thinking about.

PiJoCoder commented 1 year ago

For further research - rule usp_PerfScriptsRunningLong in PerfStatsAnalysis.sql

PiJoCoder commented 1 year ago

... and blocking too. We have to create an inventory of which scripts collect multiple snapshots and figure out how far apart the data is collected for them. We could then create the "highest common denominator" as a threshold and check if anything is beyond it and report it. We also have to think about where to report this - Best Practices report or a new report - perhaps the Autotest that Gambit9009 is thinking about.

The only other snapshot based DMV was high CPU perfstats but the outputs in this file are not regular and depend only on queries found that have high CPU (above 60 sec). So not going to look at this. The only remaining snapshot-based DMV is perfstats (tbl_request table)