SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


stored proc taking time for first run every 30 mins


stored proc taking time for first run every 30 mins

Author
Message
mxy
mxy
SSC Eights!
SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)

Group: General Forum Members
Points: 865 Visits: 3749
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
EricEyster
EricEyster
Right there with Babe
Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)

Group: General Forum Members
Points: 742 Visits: 520
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.
mxy
mxy
SSC Eights!
SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)

Group: General Forum Members
Points: 865 Visits: 3749
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
sqlbuddy123
sqlbuddy123
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6108 Visits: 2243
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
mxy
mxy
SSC Eights!
SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)

Group: General Forum Members
Points: 865 Visits: 3749
i can replicate this issue in multiple environments
EricEyster
EricEyster
Right there with Babe
Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)

Group: General Forum Members
Points: 742 Visits: 520
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
mxy
mxy
SSC Eights!
SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)

Group: General Forum Members
Points: 865 Visits: 3749
thanks Eric , PLE is looks good. will there be a way to stop procedure not to recompile
EricEyster
EricEyster
Right there with Babe
Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)

Group: General Forum Members
Points: 742 Visits: 520
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
mxy
mxy
SSC Eights!
SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)SSC Eights! (865 reputation)

Group: General Forum Members
Points: 865 Visits: 3749
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
EricEyster
EricEyster
Right there with Babe
Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)Right there with Babe (742 reputation)

Group: General Forum Members
Points: 742 Visits: 520
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search