August 14, 2017 at 9:50 am
Hi,
we have sp that use dm_os_performance_counters and collect and store " buffer cache hit ratio " every 5 min.
problem starts when dm_os_performance_counters return no values , one of the reasons SSRS installation mess up counters and
following operation has to be done to correct it
https://blogs.msdn.microsoft.com/sqlserverfaq/2009/03/17/what-should-we-do-when-the-sql-server-performance-counters-are-missing/
1.uload counters
2.load counters
3. restart sql service
Since restarting sql service on production is last action we plan to do ,we looking for another source of getting values of buffer cache hit ratio.
Any suggestion?
Yes, I realize that 0 values returned by dm_os_performance_counters indicator that SQL server need to be administered,
but since reference to those counters used only for " buffer cache hit ratio " it would be hard to convince management to reboot server at day time
we have 500+ hosts with multiple sql instances installed, so we want to avoid any additional "noise" because if for any reasons "dm_os_performance_counters" fail return 0 values
Thank you
August 16, 2017 at 4:17 am
You can also collect values directly from windows performance monitor: https://www.red-gate.com/simple-talk/sql/performance/collecting-performance-data-into-a-sql-server-table/
August 16, 2017 at 7:55 am
tazzman - Wednesday, August 16, 2017 4:17 AMYou can also collect values directly from windows performance monitor: https://www.red-gate.com/simple-talk/sql/performance/collecting-performance-data-into-a-sql-server-table/
It could probably be used as a test to make sure it's an issue with the counters, which seems highly likely.
If the counters didn't load for whatever reason, they would be missing from both PerfMon and sys.dm_os_performance_counters since both use the server counters to get the data.
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply