Triggers reference the wrong column

[peschkaj]

Submitted by Andrew Notarian andrew.p.notarian@leidos.com

The XPM option does not work for me, SQL 2012 SP1.  Not sure if this is something that only works on 2014.

/*------------------------
EXEC sp_BlitzCache @sort_order='executions per minute'
------------------------*/
Setting up temporary tables for sp_BlitzCache
Determining SQL Server version.
Creating dynamic SQL based on SQL Server version.
Adding SQL to collect trigger stats.
Collecting execution plan information.
Msg 207, Level 16, State 1, Line 190
Invalid column name 'creation_time'.
Msg 207, Level 16, State 1, Line 190
Invalid column name 'creation_time'.
Msg 207, Level 16, State 1, Line 191
Invalid column name 'creation_time'.
Msg 207, Level 16, State 1, Line 301
Invalid column name 'execution_count'.
Msg 207, Level 16, State 1, Line 302
Invalid column name 'age_minutes'.
Msg 4104, Level 16, State 1, Line 302
The multi-part identifier "qs.creation_time" could not be bound.
Msg 4104, Level 16, State 1, Line 302
The multi-part identifier "qs.last_execution_time" could not be bound.
Msg 207, Level 16, State 1, Line 302
Invalid column name 'age_minutes'.
Msg 4104, Level 16, State 1, Line 302
The multi-part identifier "qs.creation_time" could not be bound.
Msg 4104, Level 16, State 1, Line 302
The multi-part identifier "qs.last_execution_time" could not be bound.
Msg 207, Level 16, State 1, Line 303
Invalid column name 'execution_count'.
Msg 207, Level 16, State 1, Line 303
Invalid column name 'age_minutes'.
Msg 207, Level 16, State 1, Line 303
Invalid column name 'age_minutes'.
Msg 4104, Level 16, State 1, Line 303
The multi-part identifier "qs.creation_time" could not be bound.
Msg 4104, Level 16, State 1, Line 303
The multi-part identifier "qs.last_execution_time" could not be bound.
Computing CPU, duration, read, and write metrics
Checking for query level SQL Server issues.
Scanning individual plan nodes for query issues.
Checking for plan compilation timeouts.
Checking for forced parameterization and cursors.
Populating Warnings column
Building query plan summary data.
Displaying analysis of plan cache.

[peschkaj]

This is because both sys.dm_exec_procedure_stats and sys.dm_exec_trigger_stats don't have a creation_time column.

The fix:

• sys.dm_exec_procedure_stats needs to reference cached_time
• sys.dm_exec_trigger_stats needs to reference cached_time
• Add an elapsed_time column to the SET @body statement as a generic sortable for time column.