Special thanks to Jatin for sharing this with us!!
Description
If the buffer manager is doing a lot of work flushing out pages that have aged out and reading in new pages from storage, this metric will show as high. Even if you have low latencies at the disk layer, it can cause significant wait and degradation from a performance aspect.
Metric Definition
To create the custom metric, click on Options -> Custom Resource Metrics and configure the metric similar to this:
- Database Versions: 9.0 and above
- Display Name: Buffer Manager Page Reads/Sec
- Description: You can use the one above or create one tailored to your needs
- Category: We recommend memory
- Units: Reads/Sec
- Metric Type: Rate
- Frequency: 60
- Timeout: 20
SQL Statement
SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE (rtrim(ltrim(counter_name))) LIKE 'Page reads/sec'
