Is it necessary to recycle app pool in order to refresh cached query plans?

Question

I'm not sure if I'm asking the right question exactly, but if you know the answer then you'll probably understand what I'm asking.

I updated a stored proc and tested in dev w/ no problem. So then I updated the proc in production and it caused errors. Took me a bit to figure out what was going on because all I changed was a field in the select list from i.Price to Price = coalesce(r.Price, i.Price). Eventually I figured the only difference could be that r.Price is a decimal value whereas i.Price is a money value so the return type of the field had changed.

Normally that shouldn't matter for c# code. the DataReader value gets cast to decimal either way just fine. But all I had to do to fix the error was reset the app pool and then it worked. I believe I could have also updated my query to cast to a money type and that probably would have worked as well.

I know I've run into this kind of issue a few times before, enough to know there's some kind of caching going on under the hood in asp.net and/or the sql assemblies.

My question is, how can I get this cache to refresh without recycling the app pool. Or is that the only way?

Answer 1

Here what exactly happens when you deploy :

ASP.NET has a feature called shadow copying that enables assemblies that are used in an application domain to be updated without unloading the AppDomain. Normally, this is required because the Common Language Runtime (CLR) will lock the assemblies so you can’t just copy a new assembly over it. Shadow copying means that the original assembly is copied to a temporary location. The copied assembly is locked, and the original assembly can be updated with a new version.

What is assembly interning?

From :

From MSDN:

The ASP.NET shadow copy feature enables assemblies that are used in an application domain to be updated without unloading that AppDomain (necessary because the CLR locks assemblies that are being used). This is done by copying application assemblies to a separate location (either a default CLR-determined location or a user-specified one) and loading the assemblies from that location. This allows the original assembly to be updated while the shadow copy is locked. ASP.NET turns on this feature by default for Bin folder assemblies so that DLLs can continue to be updated while a site is up and running.

https://msdn.microsoft.com/en-us/magazine/hh882452.aspx

App Pool will be recycled automatically when below happens:

Any modifications in the Application’s BIN Directory

Making changes in any Configuration File/s, like Web.config or others ( if you have any specific config file in your application say in a directory called App_Config).

Making modifications in the Source code files in APP_CODE Directory. This maybe change in any Source code files, or adding or deleting files from this directory.

Making changes in the Global.asax file

Making Changes in the Machine.config file.

Making any modifications in the Web Application’s Root Directory. This means creating files/subdirectories on the fly can lead to application pool recycling.

Modifications for references of Web Services of App_WebReferences directory.

Modifying the Security Settings of any directory in the Root directory. (Like specifying read security rights for everyone or any other specific user or user group.)

For more details : Does any change in any file inside bin folder cause application recycle in ASP.NET web application?