Post reply

SQL 2008 Performance Counters gone MAD - Ample RAM but Page Life expectancy and Buffer counters are not happy

  • April 13, 2011 at 2:50 am

    #389263

    Hi All, my first post here so be gentle

    I got a Prod Physical SQL Server with 24 Core's and 64GB Free RAM !!!

    but the PerfMon counters seem to indicate it's having RAM trouble, yet there is ample free RAM

    buffer hit ratio is around 90-94 mark, but not above 97%

    Almost next to no writes, but heck of a lot of reads

    can any of the SQL guru's tell me where i should be looking at next, so solve this issue... the system is not Slow... but it a lot slow for what it should be

    OS - WIndows 2008

    DB - SQL 2008 SP1

    DB's, there only 1 is used for ERP system by around 30 users

  • April 13, 2011 at 5:56 am

    #1310957

    On first glance, it looks like you're swapping stuff out of memory a lot. I'd focus where I usually find myself focusing, the queries. Use a server-side trace to get information about the queries being run or query the cache to pull the latest queries out there and figure out which queries are running the longest or are most frequently called. Also, focus on which queries are moving the most data, because you really are swapping lots of data around there. Then figure out why. Are there things that can be done to the code or can you improve things with indexing?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 13, 2011 at 6:06 am

    #1310962

    Check your 'min server memory' and 'max server memory' to see that they were not set to some arbitrary value. Also, if you are on 64 bit, verify the "lock pages in memory' right is granted to the service account.

  • April 13, 2011 at 10:07 am

    #1311154

    There are MANY unanswered questions here. Chief among them:

    1) what is max memory setting for sql server?

    2) what else is running on the server?

    3) what is the underlying IO subsystem?

    4) how much total data is 'active' - i.e. being used regularly by the database apps/users?

    Oh, and beware setting lock pages in memory! If you have improper setup on sql server you can cause bad stuff to happen. See here: http://support.microsoft.com/kb/918483

    "If you assign this user right on systems that are configured incorrectly, the system may become unstable or experience a performance decrease of the whole system. "

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • April 13, 2011 at 6:20 pm

    #1311391

    Thanks for kind reply guys

    i will check the MAX and Min setting

    this is dedicated SQL Server only used to host EPR System DB "basically live and Test DB"

    No other App are active or hosted on this system

    Drive setup is

    C: - OS and SQL Program

    D: - SQL data and Page file

    E: - Log file

    F; - SQL Temp DB

    all RAID 10 Arrays and 15RPM drives

    i will research in to lock pages in memory setting as well

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

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