Perfmon ... memory problem or red herring ?!

  • Hi

    running an hour long perfmon trace with 5 sec intervals Im looking at (amongst other things) memory : pages/sec and buffer cache hit ratio. The pages per sec is an average of 9 peaking at 3495. Might suggest a memory problem .. but the buffer hit ratio is constantly at 90% which I interpret as SQL using buffer memory efficiently etc...

    Am I misreading this ? what should be my next step to confirm either way ?

    many thanks

    Simon

  • According to Grant Fritchey's book SQL Server 2008 Query Performance Tuning Distilled, I know you are on 2005, but I think it still applies, if your average Pages/Sec < 50 then you are likely all right. I'm not an expert, but since I just read that chapter in the book, I figured I through out Grant's 2 cents.

    Also according to the book you may want to look at Available Bytes to make sure it isn't too low (yes this is the generic term used).

    Edit: added link to book.

  • Was it a small number of peaks? It could be explained by, for example, a backup running. Note that this counter isn't SQL-specific so other apps could cause the spikes.

    Are you specifically looking for memory problems, or just at server performance in general?

  • matt stockham (4/22/2009)


    Was it a small number of peaks? It could be explained by, for example, a backup running. Note that this counter isn't SQL-specific so other apps could cause the spikes.

    Are you specifically looking for memory problems, or just at server performance in general?

    Hi Matt

    Initally looking at sever performance as a starting point and was debating whether I needed to drill down into memory problems based upon those results

    Yes the page/secs high points are spikes.... .. but as far as Im aware sql is the only app running on this box .. having said that there was a profile trace running on the box at the same time

    ~simon

  • I saw your other post afterwards, and you should follow Grant's advice in creating baselines. Between perfmon stats, wait stats and DMVs there is a wealth of information that can be gathered.

    For general purpose monitoring I copy/reset wait stats daily and monitor a dozen or so perfmon stats at 10 minute intervals. These include:

    memory\available mb

    memory\pages/sec

    paging file\%usage

    physical disk\avg disk sec/read

    physical disk\avg disk sec/write

    processor\% processor time

    SQLServer:buffer manager\buffer cache hit ratio

    SQLServer:buffer manager\page life expectancy

    SQLServer:SQL Statistics\Auto-Param Attempts/sec

    SQLServer:SQL Statistics\Batch Requests/sec

    SQLServer:SQL Statistics\SQL Compilations/sec

    SQLServer:SQL Statistics\SQL Re-Compilations/sec

    I also monitor network stats and mirroring stats, plus a few others that are useful for reporting to management (user connections etc). Your requirements may differ based on your workload etc.

    Baselining is vital as without it you won't know what is normal, or even acceptably abnormal. You should also be aware that many stats can be misleading taken in isolation, for example disk stats may be higher than expected because memory is in short supply.

Viewing 5 posts - 1 through 4 (of 4 total)

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