SQL 2000 SP4 Problems

I just spent about 4 days troubleshooting some disturbing behavior on a new SQL server 2000 installation. I realize SQL 2000 is pretty long in the tooth so I'm not exactly blowing the lid off a shocking story but I thought it's worth mentioning anyway. We have a client that is running on some older software and hardware; Websphere 4 and SQL 2000. We decided to upgrade their database server from an old IBM X series to a DL580. After we got everything installed I started to run some load tests and found downright terrible performance; pages that took 3 seconds on the old database server were taking up to 40 seconds on the new one.

Actually, when we initially began testing the server wasn't even completing the tests. It would just stop and hang out with no SQL being issued and no threads in a running state. Since we were going from a quad processor machine to a quad proc, quad core machine we started messing around with parallelism settings, which allowed us to complete our initial tests. SQL 2000 is not particularly smart about using parallelism, so we theorized that the overhead associated with running a parallel querey on 16 processors was causing more problems than it was fixing. We found our best results with 4 processors used in parallelism, although the times were still abysmally bad.

As we approached the end of our ropes we started looking at differences between the servers a little more closely. The main differences were an OS upgrade to Windows 2003 Server, and the differences in hardware. We also noticed that when SQL was installed, it was upgraded to SP4, whereas the existing server was on SP3 (with the important security updates, of course). We removed SQL and reinstalled it with SP3 and the performance was night and day. Now the pages were loading in under 5 seconds every time, which probably means we are not running into parallelism issues any longer, either.

We just did this last night around 10:00pm so I haven't really dug into why this occured, but if we find out exactly what about SP4 caused us problems I'll post an update. Also, we noticed a pretty big performance detriment after installing the post-SP3 patches but nowhere near the difference between SP3 and SP4.