First exection of the procedure on any given day is very slow.

Question

First exection of the procedure on any given day is very slow.

ChiragNS

One Orange Chip

Points: 26137
More actions
January 20, 2012 at 4:06 am

#251579

Hi
I am sure this question has been asked before but still ....
I am running a procedure that loads approx 3 million records. This procedure is run daily and its run 3 times one after the other for each year. Ex: EXEC proc1 @year = 2011 GO
EXEC proc1 @year = 2010 GO
EXEC proc1 @year = 2009 GO
When this procedure is run for the first time every day it takes a very long time to complete more than 1 hr and we generally stop the execution. We run this procedure almost immediately after stoping the first execution and it completes in a few mins. We execute the proc three times as show above and it completes in a few mins everytime
Edit: I must point out that the data in the tables that the proc refers to is loaded daily and Auto update statistics option is set to true.
"Keep Trying"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Robert Murphy UK1 Default port Points: 1485 More actions · Answer 1

This is because the first time the procedure is run it must first load the data from disk into SQL's buffer pool (memory), since you are loading the data everyday the table's data essentially gets removed from memory until you select some data from it again.

Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 2

Also you could be looking at a long compile time if the proc aged out of cache over night.

"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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

Grant Fritchey (1/20/2012)
Also you could be looking at a long compile time if the proc aged out of cache over night.

Will this result in such a huge performance degrade. it takes more than 1 hr first time and the next time just 7 mins.

Edit: Will executing the proc first with recompile help ?

exec proc1 @year = 2011 with recompile.

"Keep Trying"

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Robert Murphy UK1 (1/20/2012)
This is because the first time the procedure is run it must first load the data from disk into SQL's buffer pool (memory), since you are loading the data everyday the table's data essentially gets removed from memory until you select some data from it again.

My first execution loads the data for the year 2011. When i stop the first execution and run it again the data for 2011 may be in memory resulting in faster execution. But i execute the procedure for the year 2010 and 2009 right after that and this data may not be in memory. Even then the procedure executes in 6 - 7 mins for each year.

"Keep Trying"

Grant Fritchey SSC Guru Points: 398908 More actions · Answer 5

ChiragNS (1/20/2012)
Grant Fritchey (1/20/2012)
Also you could be looking at a long compile time if the proc aged out of cache over night.
Will this result in such a huge performance degrade. it takes more than 1 hr first time and the next time just 7 mins.
Edit: Will executing the proc first with recompile help ?
exec proc1 @year = 2011 with recompile.

NO! If the problem is compile time, setting it to recompile means it won't get stored in cache and that it will compile each and every time it runs. You sure don't want that happening.

Can it be that long? Yes. Is it likely to be that long? No, not usually. The longest compile time I've personally seen was on an 86 table join which took 3.5 minutes. However, I've heard of hour+ compile times, I just haven't seen it.

Without more information, we're just stabbing in the dark. What does the CPU & Disk & Memory look like during the initial execution? Are you seeing blocking? Do you have queues in memory, CPU or disk? What are the wait stats on the server? What are the wait stats on the process that's executing? Check into this stuff and you can start to narrow down what's going on.

Based on the fact that changing the data set doesn't seem to matter, but it's a first execution issue, I'm still leaning, a little, towards compile time, but without a lot more information, I'm just guessing.

"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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 6

Thanks Grant. I will try and get the required info but it will take time as this is a production issue and i do not have that kind of access.

"Keep Trying"

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 7

Also, if you can provide us DDLs, Sample Data, proc script and Execution Plan (actual), we can attempt to tune your query.