Post reply

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

  • 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"

  • January 20, 2012 at 4:27 am

    #1436364

    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

  • January 20, 2012 at 5:02 am

    #1436377

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 20, 2012 at 5:43 am

    #1436403

    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"

  • January 20, 2012 at 5:46 am

    #1436405

    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"

  • January 20, 2012 at 6:09 am

    #1436422

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • January 20, 2012 at 7:56 am

    #1436510

    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"

  • January 20, 2012 at 8:13 am

    #1436524

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

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

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