Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: Perfmon Memory Performance with Multiple SQL Instances

    Hi Guys

    I'm trying to analyze whether we the Server is achieving optimal memory Performance with.

    MS Server 2003 Enterprise R2 64Bit Edition (SP2)
    12GB Ram
    &
    MS SQL Server 2005 Standard 64Bit Edition SP3

    There are SIX installed Instances of SQL

    4 CPU's with Auto Set processor & I/O affinity mask for all

    Split 21 GB OS Swap File

    Pages are Locked (min & max are same) with different amounts given to each instance. Service account has permission to do this etc

    I am Capturing Perfmon Data over 2 weeks and want to analyze if each instance has been allocated the correct amount. I'm not particularly interested in I/O Stats at the moment.

    Naturaly I'll be looking at instance specific Page Life expectancy & Buffer Cache Hit Ratio % etc

    But has anyone got any thoughts on any other useful memory counters and what to analyze to reach the best conclusion?


    I have one week of graphs already and can post more Piccys on request


    (Attached is one weeks Page Life Expectancy in milliseconds)

    GW
    Attached Thumbnails Attached Thumbnails PLE1.jpg  
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Another thing you can look at are the Total Server Memory, and Target Server Memory counters. These may not be significant, though, as Min Server Memory, and Max Server Memory configuration parameters are set to the same thing, though.

    The one counter I see there has a minimum Page Life Expectancy of 2000. Normally the counter is measured in seconds, so if this is milliseconds, then there is a problem. The usual target value for this is 300 seconds or more.

    One last place to look, although it is not definitive. The Disk counters should be able to show you evidence of "buffer churn", if they are high. This could also be indicate backups, or other large file moves, so you will have to temper judgment on those counters. Especially if you show these to non-technical people.

  3. #3
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    GW,
    It looks like you have most of the correct memory counters. As far as analysis and knowing the correct range of values for each counter I like to use the PAL tool (Performance Analysis of Logs (PAL) Tool), which outputs a nice report which shows graphs, min\max values, and definitions for each counter.

    I hope this helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •