ekant_alone (9/13/2013)
I have a job which runs a store procedure. The proc is something likeBEGIN
STEP 1) Archive records into Table A..... (around 15K records)
from TABLE B
STEP 2) Delete some records from the table B
STEP 3) Insert new records into Table B
STEP 4) Update table B
END
This proc takes about 20 mins to run.
Jobs kicks at 7:15 and as soon as job starts i can not access records from TABLE B deleted in STEP 2. I know step 1 takes about 10 mins . My assumption was that SP has sequential processing and not parallel processing. Is it possible that it can run statement 2 along with step 1. I am confused about how it gets executed. I cant see execution plan and getting permission is like 10 day process.
1) Try sp_whoisactive as soon as you kick off the sproc to look for locking blocking. GREAT free resource found on sqlblog.com. You may not have permission to run this either.
2) The statements in a proc do not EVER execute at the same time. They are serial as you expect.
3) Can you see the ESTIMATED execution plan? That could be good enough to identify that you are doing a nasty table scan or something silly to delete just a few rows or for other parts of the sproc.
4) The WITH (ROWLOCK) hint could be helpful. See Books Online for info.
5) I question the order of steps 3 and 4. Assuming your INSERT already has all the correct information for every row, doing the INSERT before the UPDATE simply puts more rows in the table to subsequently be UPDATED. Not efficient coding practices (although rather common at clients I go to!).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service