Prevent App Pool recycle when web config changes

We all know when we need to recycle and get web app restarted we can edit the web.config file. Sometime we(developers) even do dummy change to force it restart.

But what if you don't want to recycle it, but want to update the web.config file anyway. For example I had a situation where I want to update the web.config file, but didn't wanted to recycle/restart the web app immediately, because users were using it heavily. I just needed to do the web.config update now and restart the web app later (out of working hours).

That's when I heard the setting called - "Disable Recycling for Configuration Changes".

This setting can be found in "Advanced Settings" section of an app pool. So this only effective to one app pool at a time.

Default value for this is "False".

Setting this to true will cause app pool to NOT to restarts/recycle when web.config changes.

 

Query Hints -> OPTIMIZE VS RECOMPILE

I have known query hint OPTION(RECOMPILE) for while and most I've heard was it is bad and should be used with care.

As per my understanding when OPTION(RECOMPILE) is used within a stored procedure, plan for that stored procedure get compiled every time it runs (instead of caching the plan in plan cache for future use). Compiling plan is known to be very CPU expensive as it involves many steps, specially for large complex queries. So we should avoid it as much as possible. Using OPTION(RECOMPILE) is a trade off between plan compiling CPU time vs using bad/mediocre plan for set of values.

Recently I came across another related query hint - OPTION(OPTIMIZE FOR ...)

OPTIMIZE FOR hint can be used two ways.

OPTIMIZE FOR (@myparam1 = 1, @myparam2 = 2) -> i.e. specify set of parameters and values to optimize for. So SQL server compile and cache the plan that uses value 1 and 2 for above two parameters. It doesn't use what value came first at run time (like it normally does). By changing the values run time you can have multiple plans cached for same stored procedure.

Other approach is OPTIMIZE FOR UNKNOWN -> In this case SQL server tries to get best plan for each parameter set passed in, BUT it might decided in some cases compiling for new plan will cost me lot so I will use something similar I already have in cache. In other words it try to do the trade off and try to come up with plan that cost same every time. This great article by Kendra Little on BrentOzar website explains it much more details.

Again, as we come across regularly, in performance tuning world of SQL Server, there is nothing fixed, you always need to try and and see what is best for each case.

P.S. I'm not a SQL server expert (yet), I'm still learning, so please be kind enough to comment if something wrong. These notes are my own reference mostly.