June 22, 2014 at 2:34 am
There is stored proc which pull 4500000 records daily n fill one heap . I break that sp and with the help of loop I pass 10000 records like batch process . The first cycle goes well taking approx 1 min BUT as it works for another 10000 it slows down. I have seen first exec plan its is good. But no idea why it goes slow when it pick next 10000 n next
LIKE
first 10000 , time taken 1 min
second 10000 2 minutes , 3rd 10000 4 minutes and so on
I have tried nolock, Explicit begin tran, index adjustment n creation. trace flag 860
my guess is it is not releasing memory n temp with every SP call
ANY HELP.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 22, 2014 at 3:43 am
Can you post the SP code ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 23, 2014 at 6:06 am
Please, not NOLOCK. That just makes it possible to get bad data.
Without seeing the query and the query plan, it's impossible to say for certain. It's entirely possible that the plan created is good for the first 10,000 rows, but not for the next 10,000, depending on how the query is written. You could be seeing contention. You could be seeing I/O issues. There's just no way to be sure.
What are the waits you're seeing while the query runs? That can point you towards resource issues. There are just too many things it could be and not enough information to narrow it down at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply