Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ravi0435
Starting Member
47 Posts |
 Posted - 2009-08-19 : 13:13:51
|
| Error:Step Error Source: Microsoft Data Transformation Services (DTS) Package Step Error Description:The task reported failure on execution. (Microsoft OLE DB Provider for SQL Server (80040e14): Intra-query parallelism caused your server command (process ID #72) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1).) 1. Environment SQL Server 20002. DTS Package3. All Objects on 2000 boxI did some search to find the following, brief background - the DTS package calls SP's which have cursors in them obviously while processing records sql server would use parallelism and its causing deadlock running in to its own thread.1. Cause: A query that runs with parallelism enabled may experience an undetected deadlock with itself and never complete. This problem occurs because SQL Server detects a deadlock that involves the threads that are used by the same parallel query.2. Solution: When this problem occurs, microsoft recommends to resubmit the query and use an OPTION (MAXDOP 1) hint, or you can disable parallelism for the server.(The MAXDOP index option overrides the max degree of parallelism configuration option only for the query specifying this option)3. Concern: If we use MAXDOP 1 (which Suppresses parallel plan generation). The operation will be executed serially. And I am afraid as we do not know how long will the process run for and we don’t want it to run for too long running/interrupting in to another schduled job. My questions:1. Does my concern make sense? or it doesnt matter at all ? does anyone thingk though it executes serially - it will just take same amount of time - so just go with maxdop 1?2. Firstly - I dont have enough data to test on my test box, though i create some huge bunch there is no garantee that i will run across the scame problem i am facing on the production box as its the plan generation by the sql server, how should i test it?3. The Job runs daily and once/twice in a week the job errors out with this issue. Did anyone face this issue before? how to solve it?Any advice/suggestion would be appreciated.Thanks,RaviNothing much that i can do..!! |
|
|
|
|
|
Intra-query parallelism Error