SQL SP4 versus SP3a

[Grunners]

All,

In my continuing quest for speed (anything faster than a racing snail) I've come across some odd information.

A friend with a much larger SQL DB than myself, but also an 8 processor server as oposed to my dual Xeon, has been having speed related issues as well. After a couple of weeks talking to Dell and Microsoft they advised him to roll back from SP4 to SP3a.

Obviously there are many more factors in play here but it seems to have completely solved any speed related issues that he was having.

I'm about to launch myself at the Microsoft site to see if I can find out more, but is this common knowledge? Could SP4 be the root of all evil?!?

Any comments most welcome.

Some links I've just been sent:

http://blogs.msdn.com/mssqlisv/archive/2006/05/18/601287.aspx Details changes in SP4 behavior

http://support.microsoft.com/kb/888799/ A list of all the fixes for SQL2000 SP4 (rather worrying!)

http://support.microsoft.com/?id=811891 SQL Server only uses 2 GB of memory even though the AWE option is enabled (Might help?)

http://support.microsoft.com/kb/899761/ FIX: Not all memory is available when AWE is enabled on a computer that is running a 32-bit version of SQL Server 2000 SP4. Again, may help with memory useage and performance.

 

[SQL_Hell]

Hi grunners

This worries me a bit as I have just been convincing my boss that all our main production servers need updating to SP4.

however I have updated about 12 sql servers to SP4 now and have not noticed any performance differences. Did microsoft give any reasoning why you should roll back to SP3?

 

[Grunners]

Hell,

I kind of got the information second hand and what the guy told me was that Msoft had said SP4 'could' have some issues with 'flaky' VB programs.

However - and this will probably be good news for you - we have 3 SQL databases from outside vendors. Two have said it would make no difference whatsoever if we reverted to SP3a but the one we were having problems with didn't want to go that route. It's their's if any that's a flaky VB app'.

Even with Msoft being Msoft a full service pack you would think would have been properly tested, it's SQL server after all, not Win ME!

In the end i decided to leave it as SP4 and set about ripping up the database itself. After cutting about 80% of it out it went like a rocket. I ran the same DB on a server with SP3a and noticed no difference at all. So hopefully problem solved. It is now a tiny database however at 300Mb so if yours are larger (I know they will be by a mile) then it might warrant further investigation.

HTH

 

[SQL_Hell]

Hey,

Thanks for the info, I am less worried now
The database I am most concerned about is a slightly bigger than 300mb, it is 41GB and on an active passive cluster . It sounds like you did a good job deleting vast quantities of data, I just wish I could do the same here.

For all those updating sql servers to SP4, I think it is a good idea to make an image of your server's sql SP3 installation, that way it will be much easier to roll back if you do run into problems. otherwise you have to re-install and configure SQL server which is a potential nightmare.