Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

stored proc taking time for first run every 30 mins Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 9:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 50, Visits: 1,058
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
Post #1545012
Posted Tuesday, February 25, 2014 9:55 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291, Visits: 519
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.
Post #1545031
Posted Tuesday, February 25, 2014 10:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 50, Visits: 1,058
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
Post #1545041
Posted Tuesday, February 25, 2014 10:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:23 PM
Points: 1,194, Visits: 2,209
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


Post #1545049
Posted Tuesday, February 25, 2014 11:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 50, Visits: 1,058
i can replicate this issue in multiple environments
Post #1545098
Posted Tuesday, February 25, 2014 12:04 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291, Visits: 519
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

Post #1545100
Posted Tuesday, February 25, 2014 5:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 50, Visits: 1,058
thanks Eric , PLE is looks good. will there be a way to stop procedure not to recompile
Post #1545180
Posted Tuesday, February 25, 2014 7:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291, Visits: 519
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
Post #1545190
Posted Tuesday, February 25, 2014 9:20 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 12:44 PM
Points: 50, Visits: 1,058
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
Post #1545206
Posted Wednesday, February 26, 2014 6:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291, Visits: 519
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.

Post #1545351
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse