Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
eglons
Starting Member
15 Posts |
 Posted - 2013-06-04 : 13:24:00
|
| Hi, I'm calling a stored proc from an ASP page. It seems to take ages the first run and then is fast if regularly used. If I leave the web session for a while then come back to it and rerun the page it again takes ages....Reading up on this it seems to be SQL server recompiling the stored proc when first called (and then again when called after a period of inactivity).Does anyone know how to either force it to recompile just once or failing that recompile infrequently... the execution plan will not alter unless I change the stored proc so the compiliation overhead is something I could do without.thanks in advance for any help |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-04 : 14:02:18
|
| The query might be getting recompiled due to changes in statistics or for other reasons. There is a detailed description here: http://msdn.microsoft.com/en-us/library/ee343986(v=sql.100).aspx In particular, look up two query hints, KEEP PLAN and KEEPFIXEDPLAN. The former reduces the recompile threshold, and the latter forces the plan to be saved. I have used neither, so my knowledge is only academic.Also, when you invoke the stored procedures, make sure you include the schema name (such as dbo.MyProc rather than MyProc). |
 |
|
|
eglons
Starting Member
15 Posts |
Posted - 2013-06-04 : 15:29:10
|
| OK thanks I'll try these... Other things I should have mentioned... the stored proc is having to use dynamic SQL (where a SQL string is built and then executed... is this likely to force recompiles. It's also using a temporary table (I could use a permanent one if it's likely to be better?) |
|
|
|
|
|
|
Stored Proc Recomplile