Option (recompile) can taint Temp Tables
Now what does that mean, exactly?
Spread its disease?
Option (recompile) isn't a bad thing. In the right circumstances, it can be a great tool! But I found something today that I absolutely did not know about and it was a devious trap that had caused some major performance issues...
Let's get some backstory!
Option (recompile) is used to not save a query plan in the query plan cache, forcing us to (re)compile the query each time it is run. This can be effective when we have a parameter in play that is causing a substantial flux in how the execution plan will look, depending on what value that parameter holds. One might cause the query to perform a clustered index scan and another might cause a nonclustered index seek + key lookup. Option (recompile) will allow the query to choose its own path each time, depending on what is more suitable at the time.
Picture these very simple queries. One is using option (recompile) and the other is not. We execute these two queries twice.
When we check the query plan cache, we find that the "Users" query is recompiled each time, only saving data from the very last compilation in the cache. The "Post" query has stats from each execution.
Now picture these two queries!
Still very simple, there's not even an option (recompile) hint here, but the results of the first query is used to influence the second query. Let's execute that twice.
No surprise there, both queries plan's stats were saved in the cache. Both keep data from both executions.
"Hey Max, where are you going with this?? This is really obvious stuff and is hardly worth to blog about!"
Hold on, we're just getting to the good part!
Let's run one last query, combining these two segments...
Now what will the query plan cache look like?
Here it comes.
Nothing is stored. Nothing. Absolutely Nothing.
I'll be honest. I'm not entirely sure what is going on here. It took me a while of troubleshooting to figure out what was causing the performance issue I was having BECAUSE THERE WAS NO DATA PRESENT TO EXAMINE!
I could see the logic, though... We have explicitly told the SQL Server not to save the metadata of the first query, but since he is populating a temp table and using that on other queries, there could be a conflict of interest. The easiest way would probably just be to taint this temp table, causing every query that touches to spread its recompile tendrils to the darkest corners of the nether.
Actually, let's just put that to the test...
Let's put an unrelated query in the middle, and see what happens!
Execute twice aaaaaaand go.
Oh cool! So now we get to save the latest compilation, like it usually does! Now I can properly present that the tainted temp table really is tainted by the option (recompile)! See how it also has only 1 execution stored?
Lastly, did you know that option (recompile) does more than just not save the query plan?
Since it knows that the plan will be perfectly formed for this particular query's current parameters (in this case, none, but it still applies), it will kind of... loosen certain restrictions... like... memory grants, for instance. Option (recompile) can grant a substantially larger memory grant than if it wasn't set. And that is also infected into the subsequent queries, using that temp table. In my situation, that caused the second query (which was much larger than this one) to recompile each time, which took several hundred milliseconds each time and the memory grant was ~5gb each run as well. Oh did I mention this whole thing was run like 7 gazillion times, as well?
Hold up, there is one last caveat to this.
All the queries up there have been run within the same execution.
If you would split these queries up, like this...
...it would contain the disease from spreading. This will cause the second query to run just like normal.
No option (recompile).
All of its stats saved for all eternity. ...or you know, at least for the next few hours, until all of the developers' unparameterized queries create enough duplicate plans to push this one out of the cache again...