SQL 2005 64 bit Standard edition paging memory

darrenecentral
darrenecentral used Ask the Experts™
on
We are running SQL 2005 STANDARD edition on a 64 bit Clustered box.

I've been dealing with this problem for several months, and have yet to find a solution on this..any help is appreciated.  As I've seen in other posts, I agree that this is a bug of the Standard Edition.


Total of 16gigs of memory
Max_server_memory is throttled to 14 gigs

We are running NO other applications on this box, other than a couple of services, etc&nothing that consumes memory.

Here are my questions:

During high traffic, we are seeing this informational message often in the Application log:

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

 Why, if the instances arent even close to the max server memory setting, and the OS has plenty of memory to work with (usually 7-9 gigs), is SQL Server dumping memory back to the OS?  We are DEFINITELY seeing performance degradation during the moment of memory release.

 We are thinking of bringing min_server_memory up close to the max, if not equal&but first I am working with some counters during high traffic to see how much memory we are utilizing at any given time&.is min_server_memory = max_server_memory a good idea?

 Note:  We cant lock pages in memory on SQL due to the fact that we dont have enterprise edition.


Thanks!


 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I don't know if isn't an hardware problem (memory modules?).
If it's a cluster move the group to another node and see if the problem persist.

Good luck.

Author

Commented:
We are working on stress testing our memory as well as analyzing all hardware logs with the vendor....however, we've seen this behavior on both nodes in the past.

We monitored paging % counters, and as a result increased the size of the paging file...will see if that helps at least in the number of times we are paging out.
Hi,

   Did you added the 3GB switch to the boot.ini file?  This is make use of AWE for addressing windows memory.

   Next, actually setting min_server_memory = max_server_memory isn't a problem if your server often take that much of memory to work with anyway.  SQL Server will take and reserve those memory for itself.  Though there's one concern, that is it may be efficient for the process to run during the time SQL Server is not running high.  It's because for each process, it has to look up the memory table in your SQL Server (which is huge), and then point / allocated the address for the process and this takes time.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Thanks for the response...but...
a) I'm running a 64 bit server...AWE isn't applicable.
b) we have set min close to max (8 gigs min, 12 gigs max),not equal...but it doesn't matter...when the OS requests the memory, it seems to be ignoring the min memory setting and is dumping whatever it wants.  In contrast, if we simply restart the sql service the memory starts at 12 gigs.
c) I find it hard to believe that a larger memory space would cause inefficiencies on a 64 bit system.  arguably perhaps if all the queries were ad hoc (which they aren't), but even then, I'd be very surprised.

We are working with the server vendor to ensure drivers are up to date....next step will be to contact microsoft...I've tried everything I can in terms of settings...I still can't figure out, based on perf counters, why the memory is paging out when it is...

Author

Commented:
Thanks for the response...but...
a) I'm running a 64 bit server...AWE isn't applicable.
b) we have set min close to max (8 gigs min, 12 gigs max),not equal...but it doesn't matter...when the OS requests the memory, it seems to be ignoring the min memory setting and is dumping whatever it wants.  In contrast, if we simply restart the sql service the memory starts at 12 gigs.
c) I find it hard to believe that a larger memory space would cause inefficiencies on a 64 bit system.  arguably perhaps if all the queries were ad hoc (which they aren't), but even then, I'd be very surprised.

We are working with the server vendor to ensure drivers are up to date....next step will be to contact microsoft...I've tried everything I can in terms of settings...I still can't figure out, based on perf counters, why the memory is paging out when it is...
It is generally agreed that its best to set a max server memory value to something less than the total memory available in a machine. However, its doubtful that one-third the total memory value would be the best configuration in your situation.
In the majority of cases, youre better off configuring each instance to use most of the memory available on each node. Lets say that server  has 16GB of RAM. Setting max server memory to something like 14GB.

Please have a look on the page as it is recent modified. I thought I've seen reference recently to problems with SQL2005 not releasing memory when under pressure, and this was possibly due to the Lock Pages In Memory option. SQL Server 2005 does respond to memory pressure both when lock pages in memory is enabled and disabled. However there is a caveat that you need to be aware of - when OS hits memory pressure, depending on the state of the machine and applications,  it could be really slow - attempting to free up the resources. This means that SQL Server might not get enough CPU cycles to respond to memory pressure quickly and hence causing  other applications and possibly OS to return OOM errors. In case when lock pages in memory is not enabled it is possible that OS will page out SQL Server. This situation might result in inappropriate SQL Server performance.

Please have look on the Blogs
http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
http://support.microsoft.com/kb/918483/en-US

Hope it will help out for the issue.
Thanks,
Rajib

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I've found this article:
http://msdn2.microsoft.com/en-US/library/ms190730.aspx

Hope that can help you.

Good luck.

Author

Commented:
Thank you for your responses.

I cannot lock memory in pages due to the fact that we are running Standard Edtion, not enterprise
<<snip from http://support.microsoft.com/kb/918483/en-US>>
Note SQL Server 2005 Enterprise Edition is the only edition that is designed to use lock pages in memory.
(this note is NOT mentioned in books online and is an oversight on Microsoft's part).

rk_india1, I WISH I had the problem of sql server NOT releasing memory, because I can easily determine what the OS needs (no other applications are running on the machien) and ensure that memory stays available..I have the exact opposite problem..SQL is releasing memory and causing massive performance problems....I've played around with the memory settings from leaving ~2 GB to 4 GB available for the OS over the course of last week, with no positive change.

The salvao blogs were some of the most helpful, but again with the standard edition, I'm limited.  I also found:
    http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx

which has some good ideas, also not helpful for my situation.

I'm opening a ticket with Microsoft today..I'll post my findings here later on.

Thanks

Author

Commented:
After working on this issue for several months, and working with Microsoft for about 2 weeks, Microsoft came back with an undocumented and as yet not fully regression tested hotfix relating to a Broadcom network adapter: Bxvbda.sys. Along with the hotfix, we had to make a handful of minor registry changes.


From what we can tell, this has solved our memory dumping problem.  Since installing the hotfix last week, we have not seen any errors related to this problem.

If anyone would like this hotfix (to use at your own risk, you can email me at darren@ecentral.com

When asked for documentation on this fix, Microsoft could only supply me with the following links.
http://support.microsoft.com/kb/936594
Although this one doesnt specifically describe the working set problem we were seeing, there are obviously a whole bunch of other issues that this bug causes, and may explain some other symptoms were were seeing as well&time will tell.

I found this to be an Interesting link from that one:
http://support.microsoft.com/kb/904946/


This was the key link that started us down the path, in terms of the symptoms we were seeing.
http://support.microsoft.com/kb/918483
PAQed with points refunded (500)

Computer101
EE Admin

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial