Detect abnormal gaps in data collection script outputs

[suresh-kandoth]

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]

... 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]

For further research - rule usp_PerfScriptsRunningLong in PerfStatsAnalysis.sql

[PiJoCoder]

... 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)