November 5, 2013 at 8:38 am
I have a stored procedure that runs every hour from 5 AM to 10 PM to check the pending orders that needs to be shipped.
The update stats job and rebuilding the indexes based on defragmentation runs at 2 AM.
When this SP runs after that job at 5AM(which is the first time run on every day) it's taking around 50-65 seconds. But after that its taking around 10 secs(6 AM to 10 PM).
Is there a way I can make it to run even on it's first run of the day.
Thanks.
November 5, 2013 at 8:43 am
It will be creating an execution plan during the first execution.
November 5, 2013 at 9:03 am
sql_novice_2007 (11/5/2013)
I have a stored procedure that runs every hour from 5 AM to 10 PM to check the pending orders that needs to be shipped.The update stats job and rebuilding the indexes based on defragmentation runs at 2 AM.
When this SP runs after that job at 5AM(which is the first time run on every day) it's taking around 50-65 seconds. But after that its taking around 10 secs(6 AM to 10 PM).
Is there a way I can make it to run even on it's first run of the day.
Thanks.
Maybe it does not have to do anything with your index rebuild script. However your proc may need to process a lot more data when it first runs (10pm to 5am). Could that be the reason?
November 5, 2013 at 10:00 am
The Result is always less than 1000 records. I guess its not the issue with the number of records it process/return.
It takes more time between 2 AM(after the job ran) and it's first execution of the day which is 9 AM in this case.
Thanks.
November 5, 2013 at 11:01 am
Anything else running on the server at that time? Blocking on underlying tables?
November 6, 2013 at 2:25 am
If you are in fact rebuilding indexes, this will update the statistics automatically. Forcing sprocs that to recompile on the first time thety are executed. Hence it takes longer the first time.
It needs to calculate a decent root to the data and creates an execution plan which is then used for the any further executions of the sproc.
November 6, 2013 at 4:25 am
you need to compare exec plan in both the scenarios.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 6, 2013 at 10:26 pm
Try sp_recompile the stored procedure with SET FMTONLY right after the index maintenance job.
November 7, 2013 at 5:01 am
I don't believe there is a problem this is the normal behaviour of SQL.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply