Updates are locking and blocking users

Hi,

We use SQL2005 for a while now and we came accross a blocking issue.
When subsidiaries are resetting their data (UPDATE MainTable set
Value=0 WHERE xxxxxxxxx), we always encounter blocking. Important
users are updating 30.000 records, the table is 19.000.000 records
big.

Thanks to snapshot isolation, users running SELECT queries are not
blocked but users who want to update data are blocked by those big
users when they run their updates.
As the database is mirrored, we think about updating the mirror
asynchronously.
We think about enabling the Row Lock only on the main table indexes.
Seems to be a lot of waiting time for CXPACKET, so maybe desabling
parallelism would be an option?

Top 20 waiting tasks list (displayed below) shows locking info but I'm
unable to diagnose the problem.
Any help to interpret those figures would be appreciated.
count wait
time max wait signal wait
LAZYWRITER_SLEEP 3794665 278067046 4318953 144000
MISCELLANEOUS 1335169 146813062 10343 116046
DBMIRRORING_CMD 345918 139348968 1828625 34640
SQLTRACE_BUFFER_FLUSH 17466 69868468 8015 93
CXPACKET 3332211 49159093 51187
1634890
LCK_M_IS 94 27735531
1990875 31
BACKUPBUFFER 6953458 8089500 2046
129984
ASYNC_IO_COMPLETION 737 6518390 6430078 109
PAGEIOLATCH_EX 789941 5501734 2406
4015
BACKUPIO 2397876 5316578 5000 27718
ASYNC_NETWORK_IO 607127 4582968 2000 21265
PAGEIOLATCH_SH 504585 4481000 1515
7625
LCK_M_IX 274 1789984 1556515 0
SLEEP_TASK 1581447 1220656 1093
437781
SOS_SCHEDULER_YIELD 3672811 839421 390 838265
DBMIRROR_DBM_EVENT 77762 776078 18312 2000
DBMIRROR_SEND 41591 590421 1296 6265


Thanks for any help you could bring.

Olivier
.

Relevant Pages