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
 Old Forums
 CLOSED - General SQL Server
 Frequent blocking occurences after SQL SP4
 Forum Locked
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 5

efelito
Constraint Violating Yak Guru

USA
478 Posts

Posted - 08/10/2005 :  14:00:03  Show Profile  Visit efelito's Homepage
Hey hawk... any updates on this yet. I just applied SP4 to my production boxes over the weekend and believe we're hitting this issue as well. Seriously considering lowering parallelism from 8 to 2 to see if things speed back up a little. Will be opening a ticket after I've had a chance to gather a little proof.

Jeff Banschbach, MCDBA
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 08/10/2005 :  23:16:11  Show Profile
We are planning to do more work on this with MS Support, but have not had an opportunity the past couple days.

As I've mentioned before our priority to resolve this is much lower now that we've reverted back to SP3a. So we are hoping to work with MS to find a resolution, but this is lower on our priority list than it is for some others here.

This is why I highly encorage everyone with the problem to initiate their own call.
Go to Top of Page

efelito
Constraint Violating Yak Guru

USA
478 Posts

Posted - 08/11/2005 :  09:30:25  Show Profile  Visit efelito's Homepage
Thanks for the update hawk... I'll be opening a ticket today. I'll keep every one posted on my progress.

Jeff Banschbach, MCDBA
Go to Top of Page

vaaccess
Starting Member

23 Posts

Posted - 08/11/2005 :  16:07:41  Show Profile
So, beyond this blocking performance problem with SP4, are there any other current issues with SP4?

I'm not opposed to giving SP4 a try (and opening a ticket since I know we have servers that will have problems based on what I've read), but want to know what to be aware of so I can test the known potential problems heavily. Does anyone know if the 4Gig Memory issue that someone told me about has been fixed with a hot fix, for instance?

Thanks,
Mike

Edited by - vaaccess on 08/11/2005 16:36:02
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36922 Posts

Posted - 08/11/2005 :  16:10:42  Show Profile  Visit tkizer's Homepage
The blocking problem isn't a problem. Please read through the thread. The problem is performance. I haven't seen answer as to why some servers experience it and others do not, so I'm not sure if MS knows yet.

Tara
Go to Top of Page

Thrasymachus
Constraint Violating Yak Guru

USA
483 Posts

Posted - 08/11/2005 :  16:22:09  Show Profile
however there appears to be a blocking problem with SP4 at least as far as INDEXDEFRAG is concerned.

http://www.dbforums.com/t1174282.html

====================================================
Regards,
Sean Roussy

Thank you, drive through

Edited by - Thrasymachus on 08/11/2005 16:22:24
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36922 Posts

Posted - 08/11/2005 :  16:24:49  Show Profile  Visit tkizer's Homepage
Thanks for the link Sean.

Tara
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 08/12/2005 :  17:40:03  Show Profile
quote:
Originally posted by vaaccess

Does anyone know if the 4Gig Memory issue that someone told me about has been fixed with a hot fix, for instance?



From the SP 4 page at Microsoft.com
quote:
Update: Microsoft has found an issue with the final build of SP4 that impacts customers who run SQL Server with AWE support enabled. This problem does not affect IA64 platforms. On x86 and x64 systems, the problem only impacts customers with more than 2 gigabytes (GB) of memory (available only with the Enterprise, Developer, and Evaluation editions) where AWE has been enabled and more than half of the total system memory is being allocated to a single SQL Server instance. Customers with this configuration should obtain and apply the hotfix specified in article 899761 of the Microsoft Knowledge Base after upgrading to SP4.


"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"
http://support.microsoft.com/default.aspx?kbid=899761
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 08/23/2005 :  18:14:34  Show Profile
MS Support has recommended the follow fix after looking at the specifics of our problem:
"FIX: A query that involves data that is the numeric data type may return incorrect results in SQL Server 2000 SP3 and in earlier SQL Server 2000 service packs"
http://support.microsoft.com/?id=899976
quote:
Performance difference

After you install SQL Server 2000 Service Pack 4 (SP4), SQL Server may not choose an index seek if you compare numeric columns or numeric constants that are of different precision or scale. Queries of this kind may run significantly slower in SQL Server 2000 SP4 than in SQL Server 2000 SP3. We recommend that you modify queries or schema so that the data type, the precision, and the scale are the same when comparisons are performed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:


For a work around see the section that says "Trace flag to revert to the behavior in SQL Server 2000 SP3"

Basically using Trace Flag 9059 is supposed to resolve the problem. I'm going to try it, but may not be able to verify for weeks if I don't get a chance to verify tomorrow. So I suggest anyone having SP4 performance issues try this and report your results here.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 08/23/2005 :  21:19:15  Show Profile
quote:
Originally posted by hawk

MS Support has recommended the follow fix after looking at the specifics of our problem:
"FIX: A query that involves data that is the numeric data type may return incorrect results in SQL Server 2000 SP3 and in earlier SQL Server 2000 service packs"
http://support.microsoft.com/?id=899976
quote:
Performance difference

After you install SQL Server 2000 Service Pack 4 (SP4), SQL Server may not choose an index seek if you compare numeric columns or numeric constants that are of different precision or scale. Queries of this kind may run significantly slower in SQL Server 2000 SP4 than in SQL Server 2000 SP3. We recommend that you modify queries or schema so that the data type, the precision, and the scale are the same when comparisons are performed. For more information, click the following article number to view the article in the Microsoft Knowledge Base:


For a work around see the section that says "Trace flag to revert to the behavior in SQL Server 2000 SP3"

Basically using Trace Flag 9059 is supposed to resolve the problem. I'm going to try it, but may not be able to verify for weeks if I don't get a chance to verify tomorrow. So I suggest anyone having SP4 performance issues try this and report your results here.



thanks for the update. Let us know if this fixes your performance issue.



-ec
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 09/06/2005 :  13:49:30  Show Profile
Seems it has fixed the issues we had. We've been using CAST or Convert to ensure joined numeric datatypes are the same size as well. Just using the trace flag setting for now just in case we missed anything.

We've also noticed that we still get the issue when joining varchar to numeric fields, although there is implicit conversion. So for those cases we use CAST to set them to the same datatype and things run much better.

Edited by - hawk on 09/06/2005 13:50:35
Go to Top of Page

efelito
Constraint Violating Yak Guru

USA
478 Posts

Posted - 09/06/2005 :  22:01:25  Show Profile  Visit efelito's Homepage
Thanks for the update hawk. I was headed down different paths because we just don't have any numeric joins other than ints, but we do join some varchar to numeric fields. We'll give the trace flag a try sometime this week.

Jeff Banschbach, MCDBA
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 09/07/2005 :  20:24:20  Show Profile
For varchar to numeric joins the trace flag did not help. It only helped with numeric joins of different sizes. The only way we were able to improve performance on varchar to numeric joins was to use CAST to ensure the join was on the same data type.
Go to Top of Page

veeru
Starting Member

USA
1 Posts

Posted - 09/09/2005 :  17:29:58  Show Profile  Visit veeru's Homepage  Send veeru an AOL message  Send veeru a Yahoo! Message
So what happened to the actual problem and How did we solve the issue of Self Blocking SPIDs?

I was born Dumb , Education Made matters Worse
Go to Top of Page

hawk
Starting Member

25 Posts

Posted - 09/09/2005 :  18:51:09  Show Profile
Blocking spids is not an issue. That as was explained in other online postings that is a normal thing being shown now with SP4. The only issue is performance related and now we know the causes of that as well.

We all thought the Blocking SPIDs and performance were one issue but it's 2 seperate matters. Don't worry about the self blocking SPIDs. Only make sure you're joining on like data types when it involves numerics.
Go to Top of Page

iapetus
Starting Member

USA
15 Posts

Posted - 02/16/2006 :  18:40:47  Show Profile
Here's an even dumber question.

Apparently, this has always been an issue; it is just that until SP4, you only risked returning INCORRECT DATA! Now, at least, with the SP4 performance hit, the DBA is at least aware of the issue.

It is called DOMAIN MATCHING. I only wish that SQL Server would have generated a compile time error: "Hey, stupid; you have an improperly matched DOMAIN. You might loose some data." Which is exactly the error message you get if you tried to CAST any data type to a type smaller than the one you are casting to.

Sincerely,


Anthony Thomas
Go to Top of Page

Tazz602
Starting Member

USA
11 Posts

Posted - 03/02/2006 :  12:56:46  Show Profile
I apologize for keeping this thread going - but I think the explanation from MS on the indexes and data types seems to be a little off.

This is what I am seeing from the Query execution path - two queries:

Select dispo_code_id_app from lead, borrower where
lead.msa_id = borrower.msa_id and lead.msa_id = 1687523 ;

Select dispo_code_id_app from lead, borrower where
lead.msa_id = borrower.msa_id and lead.msa_id = '1687523' ;

msa_id is an identity field in lead, same data type in both tables, lead and borrower. I've also run the same queries on tables without identity fields but with numeric primary keys and get the same results.

Under SP3 and SP4 the query execution path is the same - both use the primary key indexes without issue. From the MS explanation I would assume the statment using the single quotes to be an issue since it was passing a varchar value to a numeric primary key. But all seems OK for this statement.

BUT - if I modify the query and remove the second table:

Select dispo_code_id_app from lead where
lead.msa_id = 1687523 ;

Select dispo_code_id_app from lead where
lead.msa_id = '1687523' ;


Under SP3 - both queries use the primary key index with no problem, under SP4 - the first one, where the value is numeric, or I would assume it to be - it ignores the primary key completely, does a table scan and the reads increase 100x whereas the second statment, using the single quotes and passing the variable as a char - it uses the primary key index without issue just like before. Even if I force the value to be a varchar using the CAST statement, it still uses the primary key index. BUT, if I do CAST the numeric version and force it to be exactly the same numeric datatype, it uses the index fine. That seems to me to be backwards from the MS "fix" in SP4 (http://support.microsoft.com/?id=899976) in that it allows different datatypes to work fine.

Does that seem odd to anyone?? Or should it behave like that? It's numeric to numeric - why would we have to CAST it to be exactly the same and why would sending it as a VARCHAR against a numeric field work OK if they were trying to fix inconsistencies?

As a follow-up - I have two paths here to fix my application code, change all the references BACK to using single quotes whenever accessing a record by the primary key - OR - using CAST in every "where" cause to match the datatype and scope. The reads and query path analytics are identical regardless. Any suggestions??



Edited by - Tazz602 on 03/02/2006 13:48:07
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 03/02/2006 :  14:15:47  Show Profile
"Any suggestions??

Do you need SP4 for some other reason?

Edit: Does SQL2K5 behave like this too? 'Coz if so the changes are going to have to be made anyway ... and if not maybe skip SP4 and go to SQL 2K5 instead

Kristen

Edited by - Kristen on 03/02/2006 14:17:14
Go to Top of Page

Tazz602
Starting Member

USA
11 Posts

Posted - 03/02/2006 :  16:14:26  Show Profile
I was hoping to take advantage of some of the updates for replication esp. the enhancements to handle identity columns - but if I have to stay at SP3 I will. I cannot think of upgrading until after our production peak in July.

Can anyone confirm if the same behavior exists in 2K5?? Or was this a worse "bug" introduced to fix a minor "bug"?
Go to Top of Page

iapetus
Starting Member

USA
15 Posts

Posted - 03/02/2006 :  17:31:15  Show Profile
You've got to be kidding me: data type matching is NOT a bug. The fact that it slipped through before was the bug. If you coded sloppy to that, then you've gotten what you've paid for.

Either properly type your calls, or recast them. If you recast to an improper type, then you'll get real error messages.

You can't expect anyone to make "bugs" backward compatable.

For me, I hope MS left this new, "proper" handling in place, or better yet, give you a syntax error if you try to match improper data types.


Anthony Thomas
Go to Top of Page
Page: of 5 Previous Topic Topic Next Topic  
Previous Page | Next Page
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.44 seconds. Powered By: Snitz Forums 2000