sp_BlitzCache add check for table variable use

[CharBosell]

During a SQL Performance Tuning class today with Brent, we were discussing the horrible performance of Table variables in SQL queries.

I would like to see the addition of functionality for the identification of queries that are using table variables in sp_BlitzCache to help in identifying these issues.

[BlitzErik]

It would be easy enough to identify table names beginning with '@' in execution plans, but physical tables can also lead with them. There's nothing explicit in an execution plan that differentiates between the two that I know of. At best we'd be guessing.

Here's some repro code attached. (BGO: updated, switched to attached file because Github is stripping parts of it out.)

TableVariables.txt

[BrentOzar]

I couldn't get this line to work:

CREATE TABLE @craptable

The only way I could get it to work was to frame it in brackets like this:

CREATE TABLE [@craptable]

Which I thought I'd be able to catch because typical table variables aren't framed in square brackets, but the diabolical engine is adding the brackets behind me even when I don't use them:

<Object Table="[@crap]" Storage="RowStore" />

[BlitzErik]

Yeah. I'm totally down to add it as something to look closer at in warnings, since physical tables that start with @ are pretty rare. The real benefit would be if we could also warn when estimates are skewed because of them, but of course cached plans don't give you actual vs estimated rows.

[BrentOzar]

Works! Repro:

DBCC FREEPROCCACHE GO DECLARE @Dining TABLE (DinerName VARCHAR(50)); INSERT INTO @Dining VALUES ('Pope Erik'); SELECT * FROM @Dining; GO sp_BlitzCache

Produces: