Memory Pressure

  • Hi,

    We have Production server win 2003 with Sql server 2008 running on it.

    Ram --4GB.

    We have performance issue for a database which application is running very slow.

    Can any one clarify whether Page Life Expectancy value will effect the performance.?

    when i checked Page Life Expectancy with below command on the sql server insatnce the

    PLE value is 9 which is below 300.

    SELECT cntr_value AS [Page Life Expectancy]

    FROM sys.dm_os_performance_counters

    WHERE OBJECT_NAME = N'SQLServer:Buffer Manager'

    AND counter_name = N'Page life expectancy'

    Can any one calrify if we increase the Ram from 4 GB to 16 GB will it decrease the memroy pressure.

    Please suggest what will best suggestion to do........

    Many THanks,

  • Hi,

    Yes it shows that it might be insufficient on memory.

    but decisions cannot be taken on only one counter alone.

    you might also have to check counters like available bytes/sec, %usage for paging file.

  • Focus on consistency - is the PLE consistently low?

    Have you checked for missing indexes, updated statistics?

    Try and establish why there is memory pressure.

    Increasing physical memory will alleviate pressure , but you may just run into the same problems again

  • bala2 (10/30/2012)


    Hi,

    We have Production server win 2003 with Sql server 2008 running on it.

    Ram --4GB.

    We have performance issue for a database which application is running very slow.

    Can any one clarify whether Page Life Expectancy value will effect the performance.?

    when i checked Page Life Expectancy with below command on the sql server insatnce the

    PLE value is 9 which is below 300.

    SELECT cntr_value AS [Page Life Expectancy]

    FROM sys.dm_os_performance_counters

    WHERE OBJECT_NAME = N'SQLServer:Buffer Manager'

    AND counter_name = N'Page life expectancy'

    Can any one calrify if we increase the Ram from 4 GB to 16 GB will it decrease the memroy pressure.

    Please suggest what will best suggestion to do........

    Many THanks,

    the emphasis above is mine. the question on my mind is any thing else running on the server or just sql server. i would look at upgrading the ram if you are paging allot to disk.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Increasing RAM from 4 to 16GB will only help if a) you have an edition of windows/SQL Server that can support that much and b) if 32 bit you configure various things properly for the memory above 4GB to be used.

    Having said that, extra memory will still only REALLY help performance if you have either a small database that can sit in the larger memory or you have sufficient IO bandwidth to keep pages flowing into the buffer pool quickly. Almost all implementations I have come across in many years of consulting have a big win from quadrupling their RAM though!! 🙂

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

  • THanks for reply,

    update stastics has been updated upto date.

    i have checked fragmentation and are fine...

    Can you tell me how to find the missing indexes for high usage tables....

  • One method to use is to study the Execution Plans of the queries .Analyse the code - for example, is it forcing a table scan - where that may not be necessary.

  • menon.satyen (11/2/2012)


    Please go through the below link

    http://www.sql-server-performance.com/2009/identify-missing-indexes-using-sql-server-dmvs/%5B/quote%5D

    Careful with the missing index DMV's. it may suggest 2 indexes with slight differences that could be accomplished with one index. they are a good guide but not the be all end all of index planning.

    Here is a good article on index planning and gail has many more at the same blog.

    http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

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

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