December 11, 2007 at 1:52 pm
My production server is on Standard Edition with 10GB allocated to SQL Server. The performance on the server suffers because it continually dumps its buffer cache by writing it to the swap file on disk and then reads until it is full afterwards in an endless cycle. The reason for this is because Std Edition cannot lock pages in memory even if the service running SQL has that permission as stated in http://support.microsoft.com/kb/918483.
The problem is that the owner of the company won't buy licenses for enterprise edition because he thinks the issue is a bug in SQL Server. What I need is clear documentation, preferably from Microsoft, detailing this issue and stating either that this is by design or that it is a feature of Enterpise Edition in order to convince him that we need Enterprise Edition. If you could please point me to articles, whitepapers, or blogs detailing this I would most appreciate it.
December 11, 2007 at 10:09 pm
I'd open a case with PSS, let them provide you documentation. If it's a bug, as your boss says, they'll log it and won't charge you.
December 12, 2007 at 3:28 am
I assume this must be a 64bit server ? How much physical memory is on the box and what patch level is sql server at?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 12, 2007 at 8:25 am
Yes, it is 64 bit and has 12.5gb total, 10 for SQL and 2.5 for the OS. the build is 9.00.3054.00(X64), which is SP2 with a patch a believe, and the O/S is 3790 sp2. The issue appears to be by design (Microsoft's Design), but I can't find any documentation online where Microsoft specifically admits this or denies it.
December 12, 2007 at 11:44 am
Is this on an HP box? I have SQL Server 2005 Std 64-bit on a 16 GB dedicated server - it was getting hammered with swapping all of memory out and then reading it back in.
There are/were some bugs involving HP's ILO monitoring (we disabled ours) - this resolved our issue.... I wouldn't think the 'lock pages' option would be needed unless other apps are pressuring the server for memory...
December 12, 2007 at 3:49 pm
We have cognos on another server, and everytime a cube builds, which is throughout the day, we experience this problem. Cognos has to read the contents of the entire fact table remotely, and the remote server experiences this problem during this process. I have inquired about whether the server is an HP.
December 13, 2007 at 2:50 am
I had the same sort of problem with 64bit ent. I guess you have, but the critical thing with 64bit is to make sure you've set maximum memory correctly otherwise it does seem to see the page file as part of memory.
I believe there are some problems with certain types of HP storage arrays that can't be used with SQL Server - it may have been mentioned in a thread about dba's and sans. I can't imagine how a particular storage box can't work with a database - seems to be a real strange one to me.
As for your cube building, yes I do find some of the BI tools pretty cr*p - can you not do incremental builds, or better still move to sql2005 analysis server?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 13, 2007 at 9:34 am
Max/Min memory are both set to 10GB. I tried many variations on the memory settings, but the problem either stayed the same or got worse. Since I am not attached to operations here, but rather development, I have to ask operations about the HP hardware. No reply yet. As for the SSAS - yes, switching to it would be among my top recomendations, but unfortunately when the demos were done here (way before my time) cognos could build build time dimensions and degenerate dimensions much easier than AS2000. With AS2000 at the time there was no notion of degenerate (fact) dimensions, and you had to completely build your own dimensions (which is not really a bad thing). The people here were enamored with the way cognos automatically builds the time dimension - which is roughly equivelent to the current SSAS2007 Server type time dimension. In fact the cogons cubes we use all come from one table and the diminsions are all degenerate. This seems much easier to folks here, and maybe it is up front, but I have gotten way off topic.
With Ent Edition SQL Server can manage it's own memory space via "Lock Pages in Memory" and this ability is not available with Std Edition. I just wish Microsoft would admit that this was intentional and then we could better decide what to do and move on. Actually I wish they would admit it was intentional, and communicate whether they have plans to address this in SP3 SS2005 and/or SS2008 Std Edition. We need to know in order to make decisions that could cost up to $100,000 or more in licensing fees and more in hardware. This is a small company of 28 people and we don't have premier support with Microsoft as an open channel for these types of questions.
Your help and comments are much appreciated - and any further help on where/how I can get help will be doubly appreciated!
December 13, 2007 at 10:15 am
1) Definitely fix the iLO bug if the machine is HP.
2) Also investigate the Large File Copy Bug in Windows 2003. It will also lead to cache flush. I once watched SQL RAM go from 9.2GB to 0 instantaneously. 🙂
3) Lock Pages In Memory isn't a panacea. It can cause SEVERE issues with a variety of things. Consider this: SOMETHING (OS, other application, etc) needs lots of RAM. Makes a request to the OS - and the OS says NO, you can't have physical RAM because SQL Server is being greedy and won't give up any they are taking - so you have to go play on the disk instead. Whatever is asking for RAM will now be the dog - and if it is the OS??
4) You should have some spread between min and max memory settings for SQL Server. Something about how the internal memory management benefits but I can't recall the details.
5) 10GB out of 12.5 may not be leaving enough for other processes. Have you monitored the server for paging in steady state?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 14, 2007 at 5:38 am
I'm not sure you need to worry about setting min memory, in fact I might be inclined to leave it at 0. I've not used std 64bit so it's a bit tricky.
I would advise making sure you patch the o/s to sp2+ , I do seem to remember there are some issues in the o/s.
You might want to check the o/s settings, make sure visual effects is set for best performance, I set processor to background services and memory to programs, not system cache - now I'm pretty sure there was a kb about being careful of having the memory setting to system cache, which is why I don't, but for the life of me I can't find it ( so if anyone else can confirm/correct my settings ) and I fix the size of the page file.
you might want to be wary of DEP btw - I usually only set this to windows and services.
and of course if you have av software on your box you might want to consider disabling it ( av software can do some really bad things to a sql server )
other than that it's looking into the storage side of things which can be a bit strange on HP kit. The only issues I ever had were with HP storage.
You might also want to disable non essential services and check what diagnostics have been installed, Tivoli I find is really bad for paging - it seems to refuse to use physical memory.
I'd also allocate an extra gb of memory to the o/s - I'd also monitor and watch tempdb very closely.
May I suggest you get an eval of Idrera diagnostic manager and see what it has to show - I find it an invaluable tool.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 14, 2007 at 9:00 am
The server and storage array is in fact HP, and they are iLO is in fact enabled. I was surprised to find this out from my operations team since I have sent them the kb articles referencing this as a possible issue.
In addition, we are using Tivoli for our backup strategy. Your post is interesting because I do see many occurrences where memory dumps coincide with the backups.
Thanks for the feedback and I will try to put pressure on my operations team to disable the iLO and/or apply the patch. However, I am not really sure what to do about the Tivoli backups if that is contributing to the problem.
Anyway - thanks!
December 14, 2007 at 10:29 am
I just can't understand why anyone would want to use Tivoli to do sql backups - I guess you're probably backing up direct to tape? Suggest that sql backups to disk and then stream out to tape is just so much better.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 14, 2007 at 10:56 am
The server and storage array is in fact HP, and they are iLO is in fact enabled. I was surprised to find this out from my operations team since I have sent them the kb articles referencing this as a possible issue.
In addition, we are using Tivoli for our backup strategy. Your post is interesting because I do see many occurrences where memory dumps coincide with the backups.
Thanks for the feedback and I will try to put pressure on my operations team to disable the iLO and/or apply the patch. However, I am not really sure what to do about the Tivoli backups if that is contributing to the problem.
Anyway - thanks!
December 15, 2007 at 5:22 am
not sure if the -g startup applies to 64bit sql, you might want to check.
Backups giving problems - well until a couple of years ago I never thought a backup could give problems ( on a reasonable server ) but I found a couple of issues where the backups take for ever, usually direct to tape, one thing to make absolutely certain is that t-log and db backups don't overlap - I've seen aweful problems with this, especially with lite speed ( I don't mean this in a bad way btw. it's just been on systems where there's frequent log backups where the frequency is less than the time it takes to do the full backup - or to tape backups where the backup speed is so poor it takes say 12 hours ) You might also make sure your backups are sequential ( probably are if to tape ).
Can't remember the details of the file copy bug - wasn't that fixed in w2k3 sp2 ? would appreciate a link and refresh on that one - I know when I was building 64bit boxes I highlighted a number of o/s issues but sadly i lost my notes. ( currently I build to latest patch level )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 15, 2007 at 9:18 am
http://support.microsoft.com/kb/920739
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply