SQL 2005 64 bit Standard edition paging memory

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.

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.

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...

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

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

Hope that can help you.

Good luck.

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

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