SQL Server 2014-Can we stop sql auto recompile process?

Is there a way to check what is causing a stored proc to recompile (other than index rebuild, stats update and parameter sniffing-without running profiler trace or extended events) and can we stop/cancel/ Kill the auto recompile process to reduce CPU load?

You can monitor for statement level recompile events using Extended Events. I have a blog post here that lays out exactly how to do it. You can see that there is a column for the recompile reason. That will tell you explicitly why any given statement was recompiled.

You can, for a plan that stays in cache, prevent it from recompiling while it's in cache by using the query hint KEEPFIXED PLAN. If the plan ages out of cache, it will still have to compile the next time the query is run. This also doesn't prevent recompiles from structural or code changes. Drop an index, change the procedure, you still get a recompile. This is because it can no longer be assured that the plan will work because it may reference the index you dropped, or a different column than the code you submitted. So it has to recompile. However, for statistics updates, KEEPFIXED PLAN will prevent automatic recompiles.

There is also a KEEP PLAN hint that will reduce the number of recompiles due to data changes, but it won't eliminate them the way KEEPFIXED PLAN will.

One more option would be to create plan guides for specific plans where recompilation is taking significant resources. Although, for plan guides and plan forcing using query store, the compilation process still runs, using up those resources. You just end up with a specified plan.

StackExchange Database Administrators Q#279297, answer score: 6