Performance counter updating error
I launched the tool (Admin Tools – Performance Monitor), selected Performance Monitor under the Monitoring Tools folder, then clicked the [ ] icon on the toolbar to add counters.Under the Available Counters list, I found the Sandboxed Code Process Pool counters and added some of them to the Added counters list.Also, some of the counters have multiple instances – e.g.Log File(S) Used Size (KB) has an instance for each database on the SQL Server instance In total there are 405 different counters The information that is usually overlooked, but that requires attention is the cntr_types column.These counters collect the last observed value This counter value is used as the denominator for further calculation. For this example, the counter shows that Buffer Cache Hit Ratio is 2,135%, which is not possible To calculate Buffer Cache Hit Ratio, we’ll use the counter value of the Buffer Cache Hit Ratio counter and the Buffer Cache Hit Ratio Base value show in in the example for the PERF_LARGE_RAW_BASE counter type above The counters of this type are: Buffer Cache Hit Ratio, Log Cache Hit Ratio, Worktables From Cache Ratio, Cache Hit Ratio, CPU usage %, and Rem Req Cache Hit Ratio The cntr_types column value for the PERF_COUNTER_BULK_COUNT counter type is 272696576.The counters of this type are only used to calculate other counters available via the view All counters that belong to this counter type have the word base in their names, so it’s a clear indication that this is not a counter that provides useful info, it’s just a base value for further calculations These counters are: Buffer Cache Hit Ratio Base, Log Cache Hit Ratio Base, Average Latch Wait Time Base, Cache Hit Ratio Base, CPU usage % base, and more The cntr_types column value for the PERF_AVERAGE_BULK counter type is 1073874176. To calculate the current value of the counter, you have to monitor the PERF_AVERAGE_BULK and its corresponding PERF_LARGE_RAW_BASE counter, take two samples of each at the same time, and use these values for the calculation The formula for the current metric value is: In this example, we’ll use Average Wait Time (ms) and Average Wait Time Base values. The value these counters show is cumulative, it’s accumulated values since the last SQL Server instance restart, so to get their real value sampling is required, the same as for the PERF_AVERAGE_BULK counter type However, here is important to know how long the sample period is.That’s why it’s necessary to understand all the columns the view returns, especially the counter type The columns returned by the view are: , e.g. Each category has multiple counters – there are Free Memory (KB), Lock Memory (KB), Memory Grants Pending, Target Server Memory (KB), Total Server Memory (KB), and many more in the Memory Manager category Some counters are repeated in different categories, e.g.Cache Hit Ratio appears in the Plan Cache, Cursor Manager by Type, and Catalog Metadata categories.
Keep in mind that the time needed for creating a custom monitoring solution may be more expensive than a third party tool Ty to be able to use the view for SQL Server performance monitoring and troubleshooting, it is necessary to understand the existing counter types, what each type represents, and how to calculate the current value.It’s available in all SQL Server editions and gives the results instantly.With additional coding, you can periodically query the view and store captured data in repository table(s).They were so prevalent they were clogging up the logs and making it impossible to diagnose the other issues we were looking at.(See screenshow below) To try to resolve the issue, I opened performance monitor on the Share Point server and look at the counters myself.