Post reply

queries slows down after installing sp4

  • 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.

    🙂

  • February 25, 2010 at 11:24 pm

    #1125369

    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)
    Intro to Tally Tables and Functions

  • February 26, 2010 at 7:05 am

    #1125555

    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.

    🙂

  • March 5, 2010 at 11:59 am

    #1129374

    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?

  • March 5, 2010 at 12:50 pm

    #1129411

    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!

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

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