queries slows down after installing sp4

Question

queries slows down after installing sp4

SkyMac

Mr or Mrs. 500

Points: 539
More actions
February 25, 2010 at 5:04 pm

#139129

hi, i'm new to sql server, we have a sql server 2000 (it was sp3) and a couple of days ago, we installed sp4, but we are having BIG problems with a lot of queries really slowing down, or being blocked by itself. For example, SPID 105 blocked by SPID 105. I installed sp4 on some servers before but never had this problem. Is there any way to solve this issue? maybe a hotfix or something? i don't want to revert to sp3a.... that would be the last solution. In case that i do have to revert to sp3, is the only way to do it reinstalling sql server? detach everything, install sql server, atach everything again....
well
thanks for your help.
🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

I'm not sure what to tell you about this. Many people had the same problem. While it's true that sp4 made a lot of security updates, sp3A fixed the Slammer Worm problem and, because of the "slow code" problem, I won't install sp4 for people unless there's something in particular that sp4 fixes for them.

The first thing I'd do is update all the stats and see if that fixes the problem. If not, you'll need to find the slow running queries and see if they're experiencing what is known as "parameter sniffing" and fix that problem for those queries. Lookup "SQL Server parameter sniffing" or just "parameter sniffing" on Google to learn more about that. It's just a bit too extensive to post how to fix it here.

As a sidebar, it's not always possible because of budget and lack of hardware, but you should never install a CU, Hotfix, or SP on a production server first. If possible, it's best if you install it on an identical copy of the production server and test it for breaks and performance problems before you install it on production. The poor slobs (like me) that were using things like sp_MakeWeb task ran into huge problems because they changed the permissions from something pretty casual to the sproc having to run under a user with "SA" privs. Like I said, it's not always possible but you should always test CU's, HotFixes, and SP's on a copy of the production server first.

Personally, I don't know how to revert back to sp3A because I've never done it. We've either gone through the slow sprocs and fixed them or just stayed at sp3A. Hopefully, someone else on this fine forum will be able to help in that area.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SkyMac Mr or Mrs. 500 Points: 539 More actions · Answer 2

thanks! i did install it on a test-server and then on a production server that was not so important and everything was OK. Yesterday i installed it on the main production server and it's slowing everything down.

The only thing i can think to solve this is installing another instance of sql server with sp3 and if everything is OK, uninstall the "bad" one with sp4.

Nobody else had this problem? any help is good.

🙂

tavery_scigaj SSC Enthusiast Points: 104 More actions · Answer 3

We have updated dozens of sql 2000 servers with SP4 and hot fix 2273. So far 2 have had this problem and we can't figure out why. We have reindexed and updated the stats. The servers having the problem have large databases and several large databases on one sql instance.

We are being required to do this upgrade because of the security fix that is in hot fix 2273.

Many of our sql servers are upgrading to 2005 or 2008. But for those that are stuck on 2000 for some reason, we are getting worried about putting this on more servers and causing more issues.

Can we still get support from Micrsoft on this?

SkyMac Mr or Mrs. 500 Points: 539 More actions · Answer 4

well, it appears to be working better for us. We have no locks like before (it was a terrible), and the server is working better now. All we did was:

1) dbreindex to ALL tables and ALL indexes

2) update statistics WITH FULLSCAN

3) apply trace flag 9059 (with -1, all server).

4) modify some queries that had functions on it. Took out the functions and calculated the values (or dates) outside the query (specially functions on select).

5) i dont know if this one helped but someone told me so we did it anyway, search about "Parameter Sniffing" it's pretty simple to do.

untill now, everything is working fine, and we have no troubles.

good luck!