SQL Performance Alerts

  • When running Performance monitor for a period of 24 hrs I get the following values for SQL Server Buffer Manager:Buffer cache hit ratio (Scale 1.000)

    Average 99.715

    Min 99.635

    Max 99.756

    This I am happy with. However if I set an alert in SQL Server if the counter falls below 90 on enabling I get the following alert message.

    "The SQL Server performance counter 'Buffer cache hit ratio' (instance 'N/A') of object 'SQL Server:Buffer Manager' is now below the threshold of 90 (the current value is 1.00)"

    Is this a Bug? or is SQL Server using a different scale.

    Current Configuration: SQL Server 2000 SP2 on Advance Server 2000.

  • Looks like a different scale.

    Im curious, why would you want a perf alert on this? I agree its worth checking, but not real time.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • I know I am running out of RAM and Disk space and would like to keep an eye on things. I agree its a bit over the top but If Scale is the issue then I assume this may affect other counters and someone out there may have an alert incorrectly set.

  • Totally agree that the scale issue is worth discussing. Not sure that you'll see the affects of not enough ram in buffer cache, it tends to stay in high 90's even when you start hitting the swap file hard.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Is SQL Server the only memory-intensive application on the system?

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • The Server is specificaly used for SQL Server.

    Current mem values:-

    sqlservr.exe MEM 419,344K VM Size 421,548

    Next Nearest executables

    sqlmangr.exe MEM 3,996K VM Size 1,436K

    msmdsrv.exe MEM 1,280K VM Size 92,948K

    No other application are running.

  • Dose anyone else get this alert message or am I alone?

  • I may monitor it long term, but I don't have an alert set up on it. At the times the alert fires, what else is going on? Is there a large backup job? Maybe a maintenance job? While there aren't any memory intensive systems right now, something is causing SQL Server to shuffle memory out of the buffer cache (unless it's a bug, which it very well might be).

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • Backup and maintenance are run during the evening. I am more inclined to think that this is a bug, if I set an alert with System Monitor it triggers only if I set the limit to trigger below 100.

    'Manager\Bufer cache hit ratio has tripped its alert threashold. The counter value of 99.8180779423148 is under the limit value of 100.'

  • Do the maintenance jobs run about the same time as you are seeing the drop in buffer cache?

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.truthsolutions.com/

    K. Brian Kelley
    @kbriankelley

  • As far as I can tell there is no drop in Buffer Cache.

    To Clarify:

    System Monitor shows a constant Buffer Cache of around 99.8

    An alert set on System monitor is not triggered (set to below 99).

    An alert set in SQL Server for same value is triggered whenever it is enabled.

    I have replication running on 2 tables but no other maintanance jobs.

  • Not sure why you want Buffer Cache hit perf alerts when you state.

    quote:


    I know I am running out of RAM and Disk space and would like to keep an eye on things.


    You can set up an alert for Resources on Hard Drive and Memory. Buffer only let's you know if the page you are looking for was not in memory and had to be retrieved from disk which really does not always mean a memory issue but can be impacted if so.

    "Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

  • Hi, I was actualy going to set up 2 or 3 alerts, Buffer Cache was the first one I chose but it surprised me that it got triggered.

  • Sorry about asking such a beginners question, but does anyone know how you can monitor disk space on SQL Server?  I want to set up an alert when there is only 1 GB left for a particular database and can't seem to find anything to help with this.  Any suggestions is much appreciated!!!  Thanks!

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply