February 1, 2012 at 6:19 pm
I have a really wired issue doing on. One of the Stored Procedure takes about 5 mins to execute the first time in the morning(for the first user who is trying to run it), after that it takes less than 3 secs.
I suspected that the execution plan is getting changed during the over night jobs so I disabled everything that would cause plan recompile. However, it still does it. Please let me know what else could be causing this issue as I am out of options. I will be happy to post any information required for suggestion. I used the profiler to catch the first slowness.
February 2, 2012 at 5:49 am
Data changes can lead to statistics changes which can cause a recompile. If you have a nightly load or something like that you'll see recompiles. Also, plans can simply age out of cache. Also, it might not be the compile time on the query. It might be caching data to memory, again, after the memory was flushed 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
February 2, 2012 at 6:37 am
We see this kind of thing all the time. In our case, it's not a dramatic as in yours, but we'll have procedures that take 1min to run the first time, and then 4-5 seconds after that. For us, it's the indexes not being loaded into memory. One thing that you can try to see if this might be the case is run the following:
CHECKPOINT
DBCC DROPCLEANBUFFERS
and then re-run your procedure. From what I understand, if it takes a long time after running these, that's an indication that it's having to re-load indexes.
February 2, 2012 at 6:41 am
Recurs1on (2/2/2012)
We see this kind of thing all the time. In our case, it's not a dramatic as in yours, but we'll have procedures that take 1min to run the first time, and then 4-5 seconds after that. For us, it's the indexes not being loaded into memory. One thing that you can try to see if this might be the case is run the following:
CHECKPOINT
DBCC DROPCLEANBUFFERS
and then re-run your procedure. From what I understand, if it takes a long time after running these, that's an indication that it's having to re-load indexes.
It's not re-loading indexes per-say, but recompiling your plan and caching data out to memory for that query.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 3, 2012 at 2:23 pm
I am having a similar issue with several stored procedures. I am doing a large data load at night. Is there a way of running these procedures after the large data load so it is in memory cache? And, then keeping it in cache for the entire day? Or, is this something we have to deal with?
February 3, 2012 at 3:31 pm
Thanks for the replies guys. We do not have any nightly job which would change the data during the night. I tried to do the DBCC Freeprodcache but it loaded fast right after it. This just happens in the morning. Is there a way to force the Procedure to use a certain queryplan?
February 3, 2012 at 3:36 pm
Yes, but it can be messy. Depending on how far you want to take it, here are some links:
February 4, 2012 at 8:34 pm
Thanks Recurs1on. I will give that a try.
February 5, 2012 at 4:35 am
Note that those may well increase your compile time, not decrease it. They're used when non-optimal plans are generated (for whatever reason), they are not there to allow you to bypass the optimiser, they don't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 8:27 am
Tempdb12 (2/1/2012)
I have a really wired issue doing on. One of the Stored Procedure takes about 5 mins to execute the first time in the morning(for the first user who is trying to run it), after that it takes less than 3 secs.I suspected that the execution plan is getting changed during the over night jobs so I disabled everything that would cause plan recompile. However, it still does it. Please let me know what else could be causing this issue as I am out of options. I will be happy to post any information required for suggestion. I used the profiler to catch the first slowness.
I doubt this is a compiliation issue. I bet it is simply having to lift up data off the physical disks and into the buffer pool. After the first run the data is already in RAM and the execution is MUCH faster.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 6, 2012 at 9:51 am
When I do an sp_recompile 'spname' and then run the stored procedure, it takes about 2 minutes to run. If I execute it immediately after that, it takes 2 seconds. Is that the sql execution plan creating taking the 2 minutes, or sql server pulling data off the drive and dropping it into memory?
February 6, 2012 at 10:15 am
Both occur during that period. To see if it's the exec plan, remove it from cache and then just ask for an estimated plan. However long that takes is your compile time. Everything else is moving data off the disk and into cache.
"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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply