[Bug] Deadlock: Wait Type CXPACKET with CLUSTERED INDEXES

Our site has experienced a dead lock issue with a query that uses parallel
processing. Anyone know how to report this to Microsoft?
When running a load for one of our datamarts we have a query that uses 12
threads of execution, we have several queries like this but only 1 which
locks up. Half way to 3/4 of the way through execution many of threads stop
and are waiting with a type CXPACKET, CPU utilizations grinds to halt and the
query never returns. It ran this entire weekend, Friday 11:30 PM to Monday
9:00 AM.
Our workaround, believe it or not, was to change the primary key's on a
couple lookup tables from CLUSTERED to NON-CLUSTERED. This fixed the problem
and still allows us to use parallel execution and we did not have to resort
to OPTION (MAXDOP 1)
There is clearly some kind of bug with SQL Server and the creation and
execution of the query plan. How do I get a solid core dump and log of the
server and submit a bug report to MS so they can fix this.
AdamAdam,
If you are up-to-date on service packs and not just encountering
http://support.microsoft.com/kb/293232/, the best way to report this is
probably to open a case with Microsoft Product Support (PSS). This
doesn't sound like the kind of problem that can easily be distilled down
to a reproducible Query Analyzer script.
If the problem is caused by a bug, there's a good chance the support
incident will be free, but whether or not there will be a fix in the
short term is hard to say. But changing indexes to nonclustered isn't a
very convenient workaround, and PSS might be able to find a better
workaround. There are occasional newsgroup reports of this situation,
and you can browse recent threads here to see if there is a better
workaround:
http://groups-beta.google.com/groups?q=CXPACKET+sqlserver&start=0&scoring=d&hl=en&
Steve Kass
Drew University
Adam wrote:
>Our site has experienced a dead lock issue with a query that uses parallel
>processing. Anyone know how to report this to Microsoft?
>When running a load for one of our datamarts we have a query that uses 12
>threads of execution, we have several queries like this but only 1 which
>locks up. Half way to 3/4 of the way through execution many of threads stop
>and are waiting with a type CXPACKET, CPU utilizations grinds to halt and the
>query never returns. It ran this entire weekend, Friday 11:30 PM to Monday
>9:00 AM.
>Our workaround, believe it or not, was to change the primary key's on a
>couple lookup tables from CLUSTERED to NON-CLUSTERED. This fixed the problem
>and still allows us to use parallel execution and we did not have to resort
>to OPTION (MAXDOP 1)
>There is clearly some kind of bug with SQL Server and the creation and
>execution of the query plan. How do I get a solid core dump and log of the
>server and submit a bug report to MS so they can fix this.
>Adam
>|||Thanks Steve, I will go the PSS route.
"Steve Kass" wrote:
> Adam,
> If you are up-to-date on service packs and not just encountering
> http://support.microsoft.com/kb/293232/, the best way to report this is
> probably to open a case with Microsoft Product Support (PSS). This
> doesn't sound like the kind of problem that can easily be distilled down
> to a reproducible Query Analyzer script.
> If the problem is caused by a bug, there's a good chance the support
> incident will be free, but whether or not there will be a fix in the
> short term is hard to say. But changing indexes to nonclustered isn't a
> very convenient workaround, and PSS might be able to find a better
> workaround. There are occasional newsgroup reports of this situation,
> and you can browse recent threads here to see if there is a better
> workaround:
> http://groups-beta.google.com/groups?q=CXPACKET+sqlserver&start=0&scoring=d&hl=en&
>
> Steve Kass
> Drew University
> Adam wrote:
> >Our site has experienced a dead lock issue with a query that uses parallel
> >processing. Anyone know how to report this to Microsoft?
> >
> >When running a load for one of our datamarts we have a query that uses 12
> >threads of execution, we have several queries like this but only 1 which
> >locks up. Half way to 3/4 of the way through execution many of threads stop
> >and are waiting with a type CXPACKET, CPU utilizations grinds to halt and the
> >query never returns. It ran this entire weekend, Friday 11:30 PM to Monday
> >9:00 AM.
> >
> >Our workaround, believe it or not, was to change the primary key's on a
> >couple lookup tables from CLUSTERED to NON-CLUSTERED. This fixed the problem
> >and still allows us to use parallel execution and we did not have to resort
> >to OPTION (MAXDOP 1)
> >
> >There is clearly some kind of bug with SQL Server and the creation and
> >execution of the query plan. How do I get a solid core dump and log of the
> >server and submit a bug report to MS so they can fix this.
> >
> >Adam
> >
> >
>|||On Mon, 20 Dec 2004 12:59:05 -0800, "Adam"
<Adam@.discussions.microsoft.com> wrote:
>Our workaround, believe it or not, was to change the primary key's on a
>couple lookup tables from CLUSTERED to NON-CLUSTERED. This fixed the problem
>and still allows us to use parallel execution and we did not have to resort
>to OPTION (MAXDOP 1)
I believe it.
You don't happen to use any little lock hints in your query, do you?
I recently had some queries hang up because the programmer had
helpfully specified tablockx, and this turned out to be
counterproductive.
J.