June 13, 2008 at 12:28 pm
Hi,
We have sql server 2000 64 bit enterprize version on Itanium platform with 64 GB memory.
Current memory settings are: sql server uses fixed server memory. Sql server min memory and max memory are set to 59 GB leaving 5 GB for the operating system. Set working size set option is also enabled and set to 1.
However, one of the Microsoft consultants is pushing to change it back to 0.
Only info available on the msdn articles and other forums about this setting is:
'set min server memory' and 'max memory' to the same amout as fixed memory before you enable set working size set to 1.
We already have that.
I did not get any explanation from MS consultant as how changing 'set working size set' option to 0 will affect the memory management with fixed memory and same min/max memory setting on 64 bit version?
We have got downtime to restart the sql server tonight after changing the setting. While I agree that this counter should not have been enabled in the first place, I really do not know how it will affect when we change it back to 0.
Any idea? Any help? what will disabling the set working set size do on the server with fixed sql server memory same as min/max server memory (59 GB)? This is a very high transactional system and I do not want to screw up anything.
I really need help before we miss the downtime window to make a decision and restart the services.
Thanks,
Nikki:unsure:
June 13, 2008 at 1:58 pm
Hmm - don't have experience with Itanium but I think set working set is much the same as lock pages in memory. The idea was to fix memory to stop it being swapped/paged out.
so does you server grab all the memory on start up? If so then the set working set is probably a waste of time. If the memory usage is gradual then I'm not too sure.
x64 with sql 2005 doesn't allow you to grab all the memory at once wheras sql 2000 x32 does.
I'd check your lock pages in memory. do I take you have problems with your box?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
June 13, 2008 at 2:40 pm
The thing confuses me as if I change the working set size to 1 but I have fixed memory and min/max set the same as fixed memory, what good will it do?
yes, we have problem with the environment. We have SAP running on the same database as in-house application (bad bad decision). Not only that no one cared about archiving purging until I really started hammering them with emails as we just have < 20 % free space left on the server with the db size 6.5 TB. Over 150 tables have more than million and over 70 tables have more than 100 millions rows. These tables are used as OLTP tables. It's combination of everything bad-bad design, bad architecture. SQL server gets blame for it that is not as stable as Oracle environment. I do feel taht we have very powerful hardware ( 12 CPU, 64 GB memory ). It's just the combination of bad code, bad table design, bad database design and architecture can bring the server to it's knees.
We have ticket open with MS about stability issues and this is one suggestion from them to change it to dynamic memory and disable working set size. We always got recommendation to have it fixed memory as this is a mammoth environment and nothing normal settings apply to this giant. So, i am really nervous making this change.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply