Strange counter value

Mikhail Firsov 1,881 Reputation points
2022-06-22T13:17:32.26+00:00

Hello!

As you know there's the Buffer Manager cache hit ratio counter that can be monitored both in Performance Monitor and in SQL Server itself:
213902-03.png

On my SQL Server instance it always displays 100, so, for example, I can create an alert that would fire up once this value falls below 90, as on the sceenshots above.

Since my SQL Server instance always displays the Buffer Manager cache hit ratio counter value = 100 I don't expect any alert triggerings and this is true for the Performance Monitor, but the same alert created in SQL Server starts producing email notifications right away:
213848-01.png

Would anyone please tell me why SQL Server alert displays the Buffer Manager cache hit ratio counter value = 1 while the Performance Monitor shows 100?

Thank you in advance,
Michael

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,433 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Phillips 17,741 Reputation points
    2022-06-22T14:42:52.76+00:00

    The value is divided by 100. 1.00 = 100%. You want to set it to .90 for an alert value.

    However, it is completely normal and expected for this value to be reset to 0% during certain maintenance processes. I never recommend to setup alerts on this value. This is only something you need to look at if you are experiencing a performance issue.

    0 comments No comments

  2. Seeya Xi-MSFT 16,571 Reputation points
    2022-06-23T06:47:29.837+00:00

    Hi @Mikhail Firsov ,

    Welcome to Microsoft Q&A!
    Agree with Tom. You can see the sentence in the email that COMMENT: SQL_ALERT_BufferManagerHitRatio < 90% which means the value you set at that time was 90%. Thus, like Tom said, you should set it to .90 for an alert value.
    Here is an official document: https://learn.microsoft.com/en-us/sql/ssms/agent/alerts?view=sql-server-ver16
    Read the part of Selecting a Performance Condition and there are several examples.

    Best regards,
    Seeya


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  3. Mikhail Firsov 1,881 Reputation points
    2022-06-23T07:20:00.75+00:00

    Thank you all for your replies!

    "The value is divided by 100. 1.00 = 100%" - I thought about it but

    1) I haven't found any documentation saying it is divided

    2) the value Performance Monitor is displaying is NOT divided - it displays 100, NOT 1

    ...so it looks like it is the SQL Server itself which modifies the Performance Monitor value from 100 to 1, but it must be completely nonsense...

    By the way, as you can see on the screenshots above the Performance Monitor alert is also configured to be <90 but it has NEVER fired!!!

    "I never recommend to setup alerts on this value. " - thank you for the suggestion!

    "Thus, like Tom said, you should set it to .90 for an alert value." - but why in SQL Server this must be 0.9 whilst in PerfMon 90???

    Regards,
    Michael

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.