Post reply

Is this the indication of Memory pressure?

  • August 24, 2009 at 4:07 pm

    #225407

    Hi,

    We have a SQL Server 2005 EE x64 instance with SP3 on Windows 2003 EE x64, which stores Share point databases. We have 16 GB of RAM. .

    Initially I have set the Max memory to 12 GB. Now the Total server memory and Target server memory are equal to 12 GB. After some days I have changed the Max memory setting to 14GB leaving 2 GB to OS. But still the Max server memory and Target server memory are equal to 14 GB. Lock Pages in Memory is not set

    Why this sql instance taking all the allocated Max memory? Is this the indication of Memory pressure? We are using Spot light for Monitoring.

  • August 25, 2009 at 11:10 am

    #1044367

    Could you please tell me how to diagnose this issue..

  • August 25, 2009 at 11:43 am

    #1044384

    This is an indication of NO memory pressure.

    SQL Will happily keep taking what the OS will give it.

    I think 2GB on a 64 bit system is a little stingy to leave the OS. I'd bump it back up to 3-4 gb.

    Better to err on the side of caution because when the OS starts starving for memory Everything is affected.

    Take a look in spotlight at your page life expectency in SQL Server. That is a far better indicator of memory pressure within the SQL Server buffer pool than almost any other counter. Buffer Cache Hit ratio is also a very good indicator.

    ~BOT

    Craig Outcalt


    Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
    My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

  • August 26, 2009 at 2:14 am

    #1044743

    Are you facing any performance problems.

    "Keep Trying"

  • August 26, 2009 at 4:58 am

    #1044770

    Do you have any performance issues or or you just concerned about the settings?

    --------------------------------------------------------------------------------------
    [highlight]Recommended Articles on How to help us help you and[/highlight]
    [highlight]solve commonly asked questions[/highlight]

    Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
    Managing Transaction Logs by Gail Shaw[/url]
    How to post Performance problems by Gail Shaw[/url]
    Help, my database is corrupt. Now what? by Gail Shaw[/url]

  • August 26, 2009 at 5:59 am

    #1044781

    select * from sysperfinfo where counter_name like '%page life expectancy%'

    A generalization is that if it is above 300, it is unlikely that you have memory pressure

    Edit: use master

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • August 26, 2009 at 3:36 pm

    #1045210

    Do you have any performance issues or or you just concerned about the settings?

    I did not see any performance issues so far as the server is in Pre-Production. But I want to make sure there is NO Memory pressure on the server. Some times, while running DBCC checkdb & running full backups, I can see very high Avg Disk Queue Length (like 90, 120..)

    But I read in SSC, that if the Storage is on SAN, then Perfmon counters readings are no use. Then how can we get the exact results for Disk reads, writes and Memory readings?

  • August 26, 2009 at 4:39 pm

    #1045256

    If you just want to eliminate your fears ... then at this point i would suggest going through the monitoring scripts given in this white paper

    go with a structured approach of checking if exists - Memory pressure and I/O pressure

    -------------------------------------------------
    -Amit
    Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]

Viewing 8 posts - 1 through 7 (of 7 total)

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