May 5, 2004 at 2:08 pm
I've read BOL about this option and it states that it should be set off if you dynamically manage memory. What are the ramifications if this is set to on? We have something causing an issue on our SQL 2K, sp3 production server. The jobs that we run are running longer and we think it may be possible the switch was accidently turned on prior to a recent reboot. Anyone have any thoughts on this switch?
Terry
May 6, 2004 at 1:06 am
We had a similar problem where we had different values in Min and Max Memory settings (eg Dynamically managed) and Set Working Set Size was set. I concluded that these settings were considered "inconsistent". According to my investigation if you have the SET WORKING SET SIZE set to 1 then you MUST have the Min and Max settings the same. If you don't this option "will not work correctly". Unfortunately no further details were given regarding what this meant.
May 6, 2004 at 3:42 pm
Setting SET WORKING SET SIZE to 1 forces the OS (Windows NT 4.0, Windows 2000, and Windows Server 2003) not to swap out SQL Server pages even if they can be used by other processes when SQL Server is idle.
Before setting SET WORKING SET SIZE to 1, set the amount of fixed memory you want SQL Server to use by setting both min server memory and max server memory to the same value.
SET WORKING SET SIZE is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change SET WORKING SET SIZE only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.
SUGGESTION
I suggest to run your SQL Server with dynamic memory allocation first, and if you are not satisfied with its performance then you can change the settings.
Do not set SET WORKING SET SIZE to 1 if you are allowing SQL Server to use memory dynamically.
Regards
May 19, 2004 at 9:10 am
Is anybody running with this set to ON?
Terry
July 6, 2005 at 2:32 pm
We do have three servers (production) set with one
name minimum maximum config_value run_value
-------------------------------------------------------------------------
set working set size 0 1 1 1
max serv memory (MB) 4 2147483647 2816 2816
min server memory (MB) 0 2147483647 2816 2816
But one surprising finding from the error logs
2005-04-20 23:20:16.76 kernel Working Set size set to -1407057920 bytes.
Out of the three servers one server have some performance issues. A week back we had UMS scheduler hung errors.
2005-06-24 12:23:55.92 ods The Scheduler 0 appears to be hung. PSS 0x2BF59130, EC 0x2BF592E0, UMS Context 0x00E052D0
Any ideas on this ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply