sp_BlitzCache - Look for plan cache pollution from optional SPIDs

[erikdarlingdata]

Is your feature request related to a problem? Please describe.
Over in dm_exec_plan_attributes there's an attribute called optional_spid which populates under certain circumstances with temp table use.

It has been around since at least SQL Server 2008, but it was only documented recently. Thank you @WilliamDAssafMSFT for that!

We can use it to detect a form of plan cache pollution from #temp table use. I have a blog post scheduled about it. I'll update this issue with the link when it goes live.

Describe the solution you'd like
Look for entries in dm_exec_plan_attributes where optional_spid is populated with a > 0 value, and get a count of them to compare to total plans. I'm not sure about a threshold to warn about yet, and I'm not sure how common the problem is, but hey. Maybe one of you smart folks does?

Describe alternatives you've considered
Becoming a backup dancer for Lenny and the Squigtones.

Are you ready to build the code for the feature?
Obvs.

[m60freeman]

@erikdarlingdata Your blog post link is: https://www.erikdarlingdata.com/sql-server/of-temporary-tables-and-plan-cache-pollution. I too will be interested to hear what people think a reasonable threshold is for this.

I have almost all Azure SQL Databases and in spot checking a few, I see as many as thousands of rows returned by your query. Looking at a sampling of the query plans (by adding "CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) as deqp" to the FROM and "deqp.query_plan" to the SELECT), they all seem to be from Microsoft analyzing various things with queries I can't run because they use three part names.

I haven't caught one yet that came from a user stored procedure, but I haven't looked at anywhere near a reasonable sampling of these plans yet either.

[BrentOzar]

Howdy man - I'm going to go ahead and close this for now, but if you want to come back and work on it, you can reopen it.