SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 sp4 - indexdefrag blocking and HUGE perf problems
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

barrys
Starting Member

4 Posts

Posted - 02/13/2006 :  14:53:57  Show Profile  Reply with Quote
quote:
Originally posted by jen

A standby server is always critical to have, especially for those 24x7 systems.


I was wondering the same thing. It sounds like you guys really have your stuff together and probably had the hardware available for an almost identical standy/swappable machine.

Easiest thing you could have done was to build the standby machine to sp3. Restore a full backup of the main db and then keep it up to date via differential backups or logs. At some point the standby will only be a few minutes behind the main db.

From there you are looking at a total of 15 minutes downtime:
1. Stop access to the main db.
2. Take a final differential or log backup.
3. Restore that final piece on the standby.
4. Point app to that standby server (easiest way is to change the host file if the app is server based)
5. That's it.

Any reason that would NOT work?

Barry
Go to Top of Page

ThreePea
Yak Posting Veteran

USA
83 Posts

Posted - 02/15/2006 :  13:12:52  Show Profile  Visit ThreePea's Homepage  Reply with Quote
quote:

From there you are looking at a total of 15 minutes downtime:
1. Stop access to the main db.
2. Take a final differential or log backup.
3. Restore that final piece on the standby.
4. Point app to that standby server (easiest way is to change the host file if the app is server based)
5. That's it.

Any reason that would NOT work?
Barry



No, sounds like that work, although in step #3 I think you would have to remember to restore without recovery, otherwise you'd have to start from the initial full backup.

Couple things, though. We did not have the hardware available to set up something even close to our production box, though I think that will be changing shortly, simply due to this situation. If we did, we certainly would have done something like this.

And two, the steps would be somewhat different if you are using a centralized storage solution like a SAN, which we are.

Thanks.

-- 3P
Go to Top of Page

Tazz602
Starting Member

USA
11 Posts

Posted - 02/22/2006 :  10:09:53  Show Profile  Reply with Quote
Saw this thread today and wanted to share another bad SP4 experience -
I wish I had looked for more user experiences before I applied the service pack.

SQL2000 Standard on SP3a, Dell poweredge 4600, 4Gig, 2 hyperthreaded p4 processors, Windows 2K - SP4 with all updates - HT enabled, SQL sees four processors and works wonderful, quick responsive, etc.

I applied SQL SP4 - the next day performance went into the tank as soon as we had volume - SQL server response was slow, sluggish, even connecting to Enterprise manager, production web app that connected to the DB was locking and getting blocking all across the server after a certain threshhold of agents on the system in a production environment.

Reverted back to SP3a in a couple hours - no problems - DB and app hums along as before.

Couple quantifiable measurements -

1. Our network administrator watches all the servers and on that one in particular - after installing SP4, the CPU usage went through the roof - from an average 10-15% to 60% across all "four" processors.

2. We have a sample 80 query test pulled from profiler data that we run occassionally to test performance. Run on SP3, before and after the revert, that query returns in less than 2 seconds the first time and less than that after - which it should if SQL is caching properly. While on SP4 the same 80 queries take about 6 seconds to return, and the same time with each additional run.

I'm not sure where the issue is, the posts about the page locking seems to be on target, especially with the blocking issues seen, I saw the hotfix for SQL enterprise because the server will not recognize more than 2G of memory in servers with AWE but we are on Standard - I tried it anyway and no change.

Anyway - that is just information - we are staying off SP4 for now, or will test more thoroughly before we apply another SP.

Mike
Go to Top of Page

lesnic
Starting Member

Ireland
8 Posts

Posted - 05/10/2006 :  12:55:21  Show Profile  Reply with Quote
We have experienced problems since moving to sp4. We have a clustered 64-bit system running build 2187 and are suddenly seeing deadlocks between a process inserting rows and a process reading rows from this table. One spid is taking shared page locks and the other is deadlocking after it has taken an IX lock on a page and requesting an IX lock on another page which has a shared lock from the first process. The first process is requesting further shared locks on the page which has the IX lock.
Up to sp3 we have NEVER had a deadlock at page level as lock escalation is disabled.
Can anybody please help or at least tell me that locking behaviour has changed in sp4?




Les
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 05/10/2006 :  13:37:27  Show Profile  Reply with Quote
quote:
Originally posted by lesnic

We have experienced problems since moving to sp4. We have a clustered 64-bit system running build 2187 and are suddenly seeing deadlocks between a process inserting rows and a process reading rows from this table. One spid is taking shared page locks and the other is deadlocking after it has taken an IX lock on a page and requesting an IX lock on another page which has a shared lock from the first process. The first process is requesting further shared locks on the page which has the IX lock.
Up to sp3 we have NEVER had a deadlock at page level as lock escalation is disabled.
Can anybody please help or at least tell me that locking behaviour has changed in sp4?

Les



You should probably start this as a separate thread.


-ec
Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 05/25/2006 :  09:31:09  Show Profile  Reply with Quote
Since support stops for SP3a July 2006, I would like to know what Microsoft is doing about issues with SP4.
Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 05/25/2006 :  10:34:58  Show Profile  Reply with Quote
Paul Randal any thoughts?
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 05/25/2006 :  11:33:33  Show Profile  Reply with Quote
quote:
Originally posted by cgunner

Paul Randal any thoughts?




start a new thread.



-ec
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 05/25/2006 :  14:59:48  Show Profile  Reply with Quote
That new thread is now over at:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66737
Go to Top of Page

lamarshj
Starting Member

Canada
2 Posts

Posted - 09/05/2006 :  15:39:21  Show Profile  Visit lamarshj's Homepage  Reply with Quote
After a long tussle with Microsoft Support, we may have an answer for our SP4 woes. Not to blur the issue, here's the response from support and it basically has to do with using Int types to match your integer types rather than declaring Numeric types to avoid full scan conversions. You can turn on the old optimizer as well

*******************************************************************
Hello John,

Based on our last communication, it appears that we have reached a resolution for your issue.

Problem – Customer was experiencing performance degradation of his application when he migrated from SQL Server 2000 SP3 and SQL Server 2000 SP4.

Environment – SQL Server 2000 SP4

Root Cause (if known) – KB899976, http://support.microsoft.com/?id=899976, references a Query Optimizer fix that involves data of the numeric data type and returning incorrect results when you compare columns of different precision or scale. The KB article goes into much greater detail.

Many of your id columns in your database are of the type numeric (8,0) and you are passing in parameters that are integers. In SP3, this allowed an index seek, SP4 forces a table/index scan creating a slightly longer running query.

Resolution –

You can fix this issue in one of two ways:

1. Modify your queries so that you pass equivalent data types into your parameterized queries.

2. Apply the trace flag, 9059 to the server to revert SQL Server Query Optimizer to SP3.

Aparna and I have tested the trace flag in our reproduction environment and found that SP4 runs comparable to SP3. Please apply trace flag 9059 to your SP4 server and let me know how it works for you. If you use DBCC TRACEON, please remember to enable it server-wide by using the -1 parameter. E.g. DBCC TRACEON(9059, -1).

As a side note: Normally when we see this type of issue occur it is for a single query that run for hours instead of minutes. We know exactly where to look, because it is a specific query. Also, when we run PSSDiag against these types of cases we can clearly see what is going on because of the magnitude. In your case, you queries are running in milliseconds and because PSSDiag is configured by default to capture data every 5 seconds, we never really saw any “real” blocking. I believe that even if we had the capture set to every second, we still would not have seen significant blocking.

Another disadvantage I experienced, was not knowing exactly what the offending process was. There were two instances that caught my attention and ultimately led me to this solution. It’s not very often that you are required to optimize a query that runs for 15 milliseconds.

I will follow up with you Friday, August 11, 2006 to ensure that your issue is resolved to your expectations. If you need to contact me in the interim, please use my contact information that is listed below.

Best Regards,

Dan Avsec | Developer Support Engineer, SQL Data Developer Support | Office - 469.775.7163 | Dan.Avsec@Microsoft.com | Mon - Fri 6 am to 3 pm CST


JohnLamarsh
Go to Top of Page

skywalkerdb
Starting Member

Argentina
1 Posts

Posted - 02/27/2010 :  19:48:26  Show Profile  Reply with Quote
hello! i know that last post on this topic was some years ago, but i'm having a really bad performance slow after applying sp4 on a sql server 2000. we tested it for some months on a testing server and everything was OK. But some days ago, we installed it on our main production server and performance was so bad. Is there any solution to this problem or the only thing to do is go back to sp3a (and that is uninstalling sql server and installing it again...)
We would like to move to sql 2008 but we are not ready yet, so we have to solve this problem.

any help is REALLY welcome.

thanks in advance!
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 02/28/2010 :  00:57:36  Show Profile  Reply with Quote
Make sure you have done a recent rebuild on all indexes, and UPDATE STATISTICS WITH FULLSCAN (so you are starting with a level playing field. That may not cause all SProc to recompile in SQL2000, so you may want to stop/start SQL service, or force recompile on all objects)

Have you tried trace flag 9059?

The mostly likely cause (as I remember it) was where a JOIN or WHERE clause used an implicit cast - e.g.

WHERE MyNumericColumn = '123'

or

WHERE MyStringColumn = 123

(obviously that would be using parameters in practice) and it produced a really bad query plan under SP4.

I would suggest running SQL Profiler looking for slow queries so you can find what is taking the time and then check the Query Plan to see what has changed, or maybe just to highlight where putting in an Explicit cast will solve the problem.
Go to Top of Page

ThreePea
Yak Posting Veteran

USA
83 Posts

Posted - 03/01/2010 :  09:32:45  Show Profile  Visit ThreePea's Homepage  Reply with Quote
quote:
Originally posted by skywalkerdbIs there any solution to this problem or the only thing to do is go back to sp3a (and that is uninstalling sql server and installing it again...)
We would like to move to sql 2008 but we are not ready yet, so we have to solve this problem.


Well, as the original poster, I can say there was no quick fix for us. Make sure you read my last post on 01/17/2006 in this thread for our solution and an overview of our theory on what caused the problems.

As Kristen suggested, a full dbreindex on all your tables is a good place to start. This gave us some breathing room that was desparately needed, but the only real fix for us was to move forward to 2005 at the time.

Good luck.

3P


==================================================
Tolerance is the last virtue of an immoral society. -- G.K. Chesterton
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000