June 30, 2011 at 1:49 am
We have a stored procedure having a delete statement based on few parameters. This stored procedure is run parallely for those different sent of parameters. We have set very low rowcount say 10k. Still we get huge blockings while having parallel delete runs. Any solution ?
June 30, 2011 at 2:20 am
Total Records ?
Using simple Delete statement like
Delete from tab1 ? or some other procedure to delete the data ? I am asking the T-SQL in the SP ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 30, 2011 at 2:29 am
Total Records ?
TABLEA around 8000000 and TABLEB around 20000.
Using simple Delete statement like
NO.
Delete from tab1 ? or some other procedure to delete the data ? I am asking the T-SQL in the SP ?
DELETE TOP 100 A
FROM TBALEA A INNER JOIN TBALEB B ON A.COL1 = B.COL1 AND A.COL2 = B.COL2
WHERE A.COL1 = @COL1
AND B.COL2 = @COL2
Its running parallely 5-10 times with rowcount of 20k.
June 30, 2011 at 3:47 am
Any Suggestions ?
June 30, 2011 at 10:35 am
do you have index on these columns
A.Col1
B.Col1
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 30, 2011 at 10:57 am
Yes, I have
June 30, 2011 at 9:31 pm
Hi,
Can you check your exec plan, even if there are indexes it may be possible that it is doing an index scan. In such a case the lock is escalated to table level and the blocking would be obvious.
Also if u have the blocking details(can be obtained by running profiler), you can find out if the block is page level or a key lock and work from there.
Regards - Yasub
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply