Post reply

Memory pressure

  • July 5, 2011 at 2:54 pm

    #253216

    We are using SQL server 2005 Standard edition 64 bits on Windows 2003 server. RAM 12 gb.

    This is a designated SQL server

    This early morning, we got a couple of errors in error log and event viewer:

    EventID:17890

    A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 25656, committed (KB): 11937160, memory utilization: 0%

    During this time, sql server runs slow, user connection timed out.

    We have to restart sql server then it gets better.

    I guess this is related with memory pressure.

    What should we do then?

    Thanks

  • July 5, 2011 at 2:56 pm

    #1348600

    have you given the SQL service account the 'lock pages in memory' right?

    ---------------------------------------------------------------------

  • July 5, 2011 at 2:58 pm

    #1348601

    No, what I did just now is to setup the max memory for sql server to 9 gb, leave 3 gb to OS, will that help?

    I didn't set up for lock memory, for I understand that only works in Enterprise edition.

    Thanks

  • July 6, 2011 at 6:54 am

    #1348891

    support for locked pages in memory was supplied in a later CU

    http://blogs.msdn.com/b/psssql/archive/2009/05/19/an-update-for-standard-sku-support-for-locked-pages.aspx

    for background see

    http://blogs.msdn.com/b/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

    ---------------------------------------------------------------------

  • July 7, 2011 at 11:51 am

    #1349905

    1) you should ALWAYS have a max server memory set in sql server. 9 of 12 is ok for a start, but may need to be lower if you see paging.

    2) hard flushes like that are often the result of bugs in drivers, firmware, os, etc. Make sure everything you have is patched to the hilt. Be especially aware that win2003 had bugs that could cause this.

    3) be VERY careful using locked pages. you can wind up with a complete server hang, which is much worse than getting a bpool flush. See here for details: http://support.microsoft.com/kb/918483

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

  • July 7, 2011 at 12:35 pm

    #1349934

    Thanks, can you explain a little bit more about :

    9 of 12 is ok for a start, but may need to be lower if you see paging.

    Thanks

  • July 7, 2011 at 1:28 pm

    #1349973

    sqlfriends (7/7/2011)

    Thanks, can you explain a little bit more about :

    9 of 12 is ok for a start, but may need to be lower if you see paging.

    Thanks

    You said you set max memory to 9GB and server has 12GB. If you see lots of regular paging or other indications of memory pressure (there are lots of different things there) then you may need to drop to 8.5, 8GB, etc.

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

  • July 7, 2011 at 1:50 pm

    #1350000

    Thanks, so do you mean more paging or memory pressure means OS need more RAM space?

  • July 7, 2011 at 2:01 pm

    #1350012

    Since your using 64 bit SQL Server you actually do not need to enable lock pages in memory to avoid paging , mainly since sql will be able to address all the memory as it is. by Setting this option you forcing sql to hold data in RAM which might not be a good idea. Any Bulk operations going on ?

    Jayanth Kurup[/url]

  • July 7, 2011 at 2:42 pm

    #1350048

    3) be VERY careful using locked pages. you can wind up with a complete server hang, which is much worse than getting a bpool flush. See here for details: http://support.microsoft.com/kb/918483

    I presume you mean to make sure max memory is set correctly.

    ---------------------------------------------------------------------

  • July 7, 2011 at 2:47 pm

    #1350051

    Jayanth_Kurup (7/7/2011)

    Since your using 64 bit SQL Server you actually do not need to enable lock pages in memory to avoid paging , mainly since sql will be able to address all the memory as it is. by Setting this option you forcing sql to hold data in RAM which might not be a good idea. Any Bulk operations going on ?

    I think you may well need to, as we may be seeing here.

    Lock pages in memory prevents other processes flushing SQL memory, it does not prevent SQL cycling the contents of its own memory as data ages. Having data in memory is usually a good thing!

    ---------------------------------------------------------------------

  • July 7, 2011 at 3:03 pm

    #1350056

    TheSQLGuru (7/7/2011)

    sqlfriends (7/7/2011)

    Thanks, can you explain a little bit more about :

    9 of 12 is ok for a start, but may need to be lower if you see paging.

    Thanks

    You said you set max memory to 9GB and server has 12GB. If you see lots of regular paging or other indications of memory pressure (there are lots of different things there) then you may need to drop to 8.5, 8GB, etc.

    I don't understand the first part of that statement.

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

  • July 7, 2011 at 3:05 pm

    #1350059

    I don't think there is a bulk operation.

    But I will keep monitoring to see if it happens next time.

    It happened at 3 am after transaciton log backup is done.

  • July 8, 2011 at 1:50 am

    #1350179

    I agree having data in memory is a good thing but we need to be careful of the type of system for an OLTP system this works great and usually manged very well internally , however the same setup could cause a lot of trouble when dealing with a DataMart setup where paging is a good thing.

    http://msdn.microsoft.com/en-us/library/ms187499.aspx

    In a heavily loaded system under memory pressure, queries with merge join, sort and bitmap in the query plan can drop the bitmap when the queries do not get the minimum required memory for the bitmap. This can affect the query performance and if the sorting process can not fit in memory, it can increase the usage of worktables in tempdb database, causing tempdb to grow. To resolve this problem add physical memory or tune the queries to use a different and faster query plan. For tuning information, see Optimizing tempdb Performance and How to: Tune a Database.

    The above scenario would become frequent with Lock pages in memory

    Jayanth Kurup[/url]

  • July 8, 2011 at 2:02 am

    #1350187

    Jayanth_Kurup (7/7/2011)

    Since your using 64 bit SQL Server you actually do not need to enable lock pages in memory to avoid paging , mainly since sql will be able to address all the memory as it is.

    Locked pages has nothing to do with addressing memory. It's the AWE setting that's not required on 64-bit because SQL can directly address all the memory.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 24 total)

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