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 |
|
mwjdavidson
Aged Yak Warrior
735 Posts |
 Posted - 2007-04-22 : 15:14:55
|
| Hi, I'm getting some unexpected behaviour from my SSIS packages when targeting tables that are being referenced by Indexed Views. There's two separate issues:1. When writing into a pair of tables with a SuperType / SubType relationship concurrently with a pair of SS destinations I'm getting deadlocks between the two. Removing the index on the view that references both of these fixes the problem.2. Much odder, I'm getting some extremely long waits (10 times longer than the whole package should take to run!) from an SS Destination adapter even when there's no data in the flow for it to bulk insert. Again, removing the indexed views that reference the destination table fixes the problem.The views aren't mine, and (apparently) are required by the reporting app (BO), so removing them isn't really an option. I realise that there's quite a lot of overhead to maintaining indexed views, but unfortunately, the project is on a very tight timeline, so I can't look into it in as much detail as I'd like.I was wondering if anyone's experienced any similar issues, or would have any ideas as to where to start investigating? Thanks a lotMark |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-04-22 : 16:21:27
|
| It's the sort of thing I woud expect considering the views have to be maintained which will extend any transactions and affect other objects.I take it you are using multiple connections - using just one should help with the deadlocks and might make it faster.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-04-23 : 05:04:03
|
| Thanks for the reply Nigel. Both of the destinations share the same connection manager, but the RetainSameConnection property is set to false (the default). Are you suggesting I set this to true?ThanksMark |
|
|
|
|
|
|
SQL Server Destination Adapter + Indexed Views