August 20, 2024 at 4:04 pm
Hi,
We use DB server monitoring software that reports on database cache hit ratio using the perfmon counter: "Catalog Metadata(*)\Cache Hit Ratio"
Its in alarm at 80% currently.
I have always used the "Buffer Manager(*)\Cache Hit Ratio" counter to monitor the cache hit ratio in the past. This metric is currently at 100%.
I cannot seem to find any information about exactly what "Catalog Metadata(*)\Cache Hit Ratio" is actually counting. Can anyone advise what is meant by "catalog metadata" and why this is different to the good old buffer cache?
Thanks,
Newbie
August 20, 2024 at 7:43 pm
I have always used the "Buffer Manager(*)\Cache Hit Ratio" counter to monitor the cache hit ratio in the past. This metric is currently at 100%.
What actions have you taken in the past when Buffer Manager(*)\Cache Hit Ratio was high, or low? Do you think that this is a problem?
Shut off the alarm for Catalog Metadata(*)\Cache Hit Ratio. There is a reason why you can't find any real information about this. Capture the values, and look at them over time.
A good read:
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 20, 2024 at 8:02 pm
Hi Michael. Thanks for taking the time to reply. Do you have any links to articles that explain how or why the two counters differ? Or what the metadata catalog counter is actually counting? I think I will get some pushback when I speak with vendor support about this and I would prefer to go into that discussion with a better understanding of what that perfmon counter is reflecting.
thanks.
August 21, 2024 at 5:20 pm
Your google search will return the same results as mine. You will find blogs and articles about this that conflict. Brent Ozar and Paul Randal have written a lot of things on this subject.
This metric may have meant something when the servers were on spinning disks, and only had 10 MEGABYTES of memory. There are many more things to examine than those metrics.
From Jonathan's article that I linked to:
Many people continue to track the value of Buffer Cache Hit Ratio, as an “early-warning” of any potential memory issues on a SQL Server instance. What these tests prove, I hope, is that, in this regard, the value of BCHR is more or less meaningless. BCHR only responds to significant memory pressure in conjunction with I/O subsystem pressure, or possibly fragmentation i.e. under conditions that impedes page read-ahead to the point that SQL Server becomes much less effective at populating the data cache with the required pages, before the query processor actually requires them for use.
My question is what specific problem(s) are you trying to solve? I would be skeptical if these metrics have any bearing on a specific issue. If you are looking for a set of generic things to show a vendor, that is probably not a great approach. This really sounds like a solution in search of a problem.
Are your statistics being updated regularly?
Have you documented usage patterns? Such as many reads from 8-noon, and many writes from noon to 5?
Do you have missing, sub-optimal, or too many indexes?
Have you analyzed page splits, file growths, parallelism, table and index scans, key lookups, disk IO, worst queries, and so forth?
Stop using your monitoring software and try to determine what things may be issues without it.
Off the shelf monitoring may be based upon factors that are no longer relevant and are very generic. Does your monitoring show index fragmentation, and recommend reorg or rebuild based on a percentage of fragmentation? That is not what MS recommends, and in fact they specifically state do not do index maintenance based upon a percentage of fragmentation. As an example, the monitoring we are evaluating captures the buffer manager stats. In the documentation, they refer to an article that was written in 2011. THE SAME AUTHOR wrote a new article in 2016 that stated this no longer made sense.
You need to consider these things and try not to react to a "high number".
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
August 21, 2024 at 5:44 pm
Hello again Michael. Thanks again for taking the time to reply. The original post was to ask what this perfmon counter is used for. I had never come across it myself in 20+ years working with SQL Server. I'm not troubleshooting an issue. I want to know what the counter is counting at the OS level.
The reason I want this information is because our monitoring software uses this perfmon counter to alert on buffer cache hit ratio, and occasionally we get alerts for this very metric dropping below 100, even though the buffer manager cache hit ratio remains at 100%. I am in the process of querying this with the vendor who provide the monitoring software, and wanted to arm myself with the understanding of exactly what this perfmon counter is a reflection of. I was hoping to find a nice succinct MS article which would explain it, but there does not seem to be one.
There is a mention of it in the SQL 2022 documentation, but the description is a little vague for me: "The SQLServer:Catalog Metadata performance object provides counters for catalog metadata for SQL Server." https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-catalog-metadata-object?view=sql-server-ver16
In the absence of an article explaining what the counter is for, I was hoping that someone had some experience with this counter in their career and they could provide some answers. I'll keep checking back for a few days to see if such a person replies.
Thanks.
Newbie.
August 22, 2024 at 6:53 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply