February 25, 2014 at 9:42 am
stored proc is taking arround 2 minutes for first time, next execution it takes a second.
If i try same proc after 1 hr the issue remains same. it wil take time for first execution
i try to capture execution plan and IO stats
long run IO stats has some physical reads compared with other
Parameters are same any one have insight whats going on here
February 25, 2014 at 9:55 am
It sounds like other queries are foring the object of memory. SQL will have to pull from disk next time you run. Repeat the query a second later and it is still in the buffer and runs fast.
February 25, 2014 at 10:03 am
yup its working fine after first run.
After one hr or so if you try to execute same it will take over couple of minuts
February 25, 2014 at 10:23 am
For the first time, it takes more time bcs of the compiling. For the subsequent runs, it uses the stored plan in the memory.
After 1 hr if the same thing repeats, most likely it's getting recompiled. Check and see if you are experiencing any memory pressure.
--
SQLBuddy
February 25, 2014 at 11:59 am
i can replicate this issue in multiple environments
February 25, 2014 at 12:05 pm
start up Performance Monitor
add SqlServer:Buffer Manager: Page Life expectancy to the graph
execute the query
If you see a sudden drop when the query is slow, then the data is not in memory
if you run and it is fast and no drop, the pages were in memory
February 25, 2014 at 5:20 pm
thanks Eric , PLE is looks good. will there be a way to stop procedure not to recompile
February 25, 2014 at 7:02 pm
since this is happening in other environments, go to your test environment
run the query
record the time
run the query
record the time
run dbcc dropcleanbuffers
wait a few seconds
run the query
record the time
if possible, include a trace of the session
February 25, 2014 at 9:20 pm
i notice physical reads for first time to keep in cache and next executions are fast.
but after some time again it is doing physical reads. how to avaid this is there any best option
February 26, 2014 at 6:01 am
please post the times from the script I posted.
you do not have any method to force SQL to keep data in memory. It uses a LRU (Least Recently Used) algorithm to keep the data that users are currently using in memory. If you run a query once a day, it will likely be flushed out be the next day.
The only way to guarantee that it will be in memory is to have enough memory to hold all your data, which is getting amazing cheap these days. Seems like overkill, but our credit card system, which cannot tolerate delays of any kind, fits in memory on a VM server. Our 5 TB data warehouse is a different story.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply