I actually built some "Custom" Monitoring tool to track information from the DMV Tables and store them in some kind of DataWarehouse I'm taking snapshots each 5minutes.
the data I'm retrieving is:
- File Latency (ldf and mdf)
- File Sizes and use
- Log Growths and Flushes
- Database CPU used
- Database Buffer MB used
- SQL CPU / IDLE PROCESS / OTHER PROCESSES
- RAM (TARGET/USED)
- Page Life Expectancy
- Cache Hit Ratio
- User Connections / Database Connections
- General Locks/DeadLocks/Waits information (Network... Io Latch...)
In this part, I'm a bit lost... I would like to be able to detect problematic queries on my server and to do so I need to store historical information as the information on some of the DMVs is cumulative so if I want to some averages like for example execution_count I need to snapshot them. I don't know what Key KPIS and threshold should a query reach to be considered a problematic query...
Also, I don't know if I should monitor more KPIs in order to detect CPU pressure/ RAM pressure / Disk pressure.
Any help on improving it will be appreciated!