| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2007-01-12 : 10:37:09
|
| We are working with a vendor to troubleshoot performance problems with their application. The vendor recently asked if our server contained multiple processors and if it did that could be the reason we are experiencing primary key violations. Their explanation is as follows:The errors are caused a result of the script not taking into account execution on a machine with multiple processors. Basically an additional ‘where’ clause is required to filter out the additional thread data that is identical in every aspect expect for the thread id value that is caused as a result of multiple processors.I've asked to see the before and after versions of the code because I can't picture what they are saying. Is what they are saying possible?Thanks, Dave |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-01-12 : 11:36:36
|
| Sounds like utter bullsh*t...but it's easy to test....most multiple cpus can be disabled (maybe depending on a reboot)...the expectation would be each CPU would be actioning a different piece of application code...and not the same code multiple times. |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-12 : 12:29:49
|
| Sounds like bad code written by developers that didn't know what they were doing.Any vendor that did not develop for the possibility of multiple processors on a SQL Server is to put it mildly, incompetent.And to answer your question, CPUs don't cause Primary Key Violations, but bad developers do.CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-12 : 13:38:33
|
| Seems to be rather an @@IDENTITY versus SCOPE_IDENTITY() problem.Or the uglySELECT 1 + MAX(Col1)FROM MyTablePeter LarssonHelsingborg, Sweden |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2007-01-12 : 13:55:19
|
| The vendor just clarified their statement when they sent me the code changes. They are referring to Microsoft's blocker script they asked me to run to detect blocking and waiting. Throughout the year we have experienced PK violations with their application and back in September I ran their version of the MS blocker script and the script spit out a number of PK violations. I thought their latest email was referring to the application-related issues and not their blocker script. They are indicating the MS blocker script's insert statement should be checking "where (blocked!=0 or waittype != 0x0000) and ecid = 0. I searched the Internet and found no indication the blocker script can cause a PK violation on a multiple proc server, but at least this makes more sense then what I thought they were originally indicating.Thanks, Dave |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-12 : 14:11:31
|
| They would be better of sending the SP throwing the PK violation error.Peter LarssonHelsingborg, Sweden |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2007-01-12 : 14:41:07
|
| I agree. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-12 : 14:46:56
|
quote:
Originally posted by Peso
Seems to be rather an @@IDENTITY versus SCOPE_IDENTITY() problem.Or the uglySELECT 1 + MAX(Col1)FROM MyTable
Definitely. More than likely, the second.- Jeff |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-13 : 08:34:17
|
| I would not work with a vendor having these problems.For me, it is a clear sign that they do not understand the basics of SQL Server (at least not in multi-user environment).Peter LarssonHelsingborg, Sweden |
|
|
|
MohammedU
Posting Yak Master
145 Posts |
Posted - 2007-01-13 : 19:29:12
|
| Is there any written explanation from vendor? What kind of application it is?MohammedU |
|
|
|
DBADave
Constraint Violating Yak Guru
366 Posts |
Posted - 2007-01-16 : 10:25:00
|
| They say the problem is with their version of the MS blocker script so I'm not too concerned. I decided not to bother troubleshooting their script since I could always replace it with the most recent version of Microsoft's script http://support.microsoft.com/kb/271509.I would rather focus on the CXPACKET waittype being reported by the script.Thanks, Dave |
|
|
|
|
Can Multiple CPUs Cause Primary Key Violations?