SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SP compilation time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shaggy
Posting Yak Master

India
245 Posts

Posted - 01/28/2014 :  09:34:52  Show Profile  Reply with Quote
Hi,
SP is taking more time for compilation.SP has 30 update statement all are using indexed column in where clause.
how can i force SP to take less compilation time.

pls advice

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/28/2014 :  10:20:38  Show Profile  Reply with Quote
One of the things that causes a recompile is when a significant change in the statistics occurs - like from updates. What I would do if you haven't already is run a profiler trace with one of the events being SP:Recompile. That way you can confirm that the procedure is being recompiled one or more times during execution. If that is the case you can use a query hint to suppress the recompiles. From books online (query hints):
quote:
KEEPFIXED PLAN

Forces the query optimizer not to recompile a query due to changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query will be recompiled only if the schema of the underlying tables is changed or if sp_recompile is executed against those tables.



EDIT:
of course this are obvious repercussions with this - maybe the optimizer should change plans based on the latest statistics.

Be One with the Optimizer
TG

Edited by - TG on 01/28/2014 10:22:59
Go to Top of Page

shaggy
Posting Yak Master

India
245 Posts

Posted - 01/29/2014 :  00:29:59  Show Profile  Reply with Quote
Thanks TG,

But the second time my SP execution is fast due to already plan avaible in cache.

Edited by - shaggy on 01/29/2014 00:30:47
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/29/2014 :  04:06:11  Show Profile  Reply with Quote
quote:
Originally posted by shaggy

Thanks TG,

But the second time my SP execution is fast due to already plan avaible in cache.


yep..once plan is in cacahe sp will execute faster


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

shaggy
Posting Yak Master

India
245 Posts

Posted - 01/29/2014 :  04:14:42  Show Profile  Reply with Quote
So can I freeze plan in cache so that evertime i execute Sp it will be faster.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 01/29/2014 :  10:07:51  Show Profile  Reply with Quote
It is sounding more like the data is cached for subsequent runs. Why did you believe that the time is taken for execution plan compilation? If you run the trace I suggested you will know how the time is divided between recompiles vs. statement duration.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000