| Author |
 Topic  |
|
efelito
Constraint Violating Yak Guru
USA
478 Posts |
 Posted - 08/10/2005 : 14:00:03
|
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
|
 |
|
|
hawk
Starting Member
25 Posts |
Posted - 08/10/2005 : 23:16:11
|
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. |
|
|
|
efelito
Constraint Violating Yak Guru
USA
478 Posts |
Posted - 08/11/2005 : 09:30:25
|
Thanks for the update hawk... I'll be opening a ticket today. I'll keep every one posted on my progress.
Jeff Banschbach, MCDBA
|
|
|
|
vaaccess
Starting Member
23 Posts |
Posted - 08/11/2005 : 16:07:41
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 08/11/2005 : 16:10:42
|
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 |
|
|
|
Thrasymachus
Constraint Violating Yak Guru
USA
483 Posts |
Posted - 08/11/2005 : 16:22:09
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 08/11/2005 : 16:24:49
|
Thanks for the link Sean.
Tara |
|
|
|
hawk
Starting Member
25 Posts |
Posted - 08/12/2005 : 17:40:03
|
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 |
|
|
|
hawk
Starting Member
25 Posts |
Posted - 08/23/2005 : 18:14:34
|
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. |
|
|
|
eyechart
Flowing Fount of Yak Knowledge
USA
3575 Posts |
Posted - 08/23/2005 : 21:19:15
|
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 |
|
|
|
hawk
Starting Member
25 Posts |
Posted - 09/06/2005 : 13:49:30
|
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 |
|
|
|
efelito
Constraint Violating Yak Guru
USA
478 Posts |
Posted - 09/06/2005 : 22:01:25
|
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
|
|
|
|
hawk
Starting Member
25 Posts |
Posted - 09/07/2005 : 20:24:20
|
| 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. |
|
|
|
veeru
Starting Member
USA
1 Posts |
Posted - 09/09/2005 : 17:29:58
|
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 |
|
|
|
hawk
Starting Member
25 Posts |
Posted - 09/09/2005 : 18:51:09
|
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. |
|
|
|
iapetus
Starting Member
USA
15 Posts |
Posted - 02/16/2006 : 18:40:47
|
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
|
|
|
|
Tazz602
Starting Member
USA
11 Posts |
Posted - 03/02/2006 : 12:56:46
|
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 |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 03/02/2006 : 14:15:47
|
"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 |
|
|
|
Tazz602
Starting Member
USA
11 Posts |
Posted - 03/02/2006 : 16:14:26
|
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"? |
|
|
|
iapetus
Starting Member
USA
15 Posts |
Posted - 03/02/2006 : 17:31:15
|
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 |
|
|
|
Topic |
|
Frequent blocking occurences after SQL SP4
Forum Locked
