sp_BlitzCache - OutputTableName definition for column PlanCreationTimeHours should evaluate against CheckDate

[Adedba]

Version of the script
SELECT @Version = '7.999', @VersionDate = '20201011';

What is the current behavior?
If I log sp_BlitzCache to a table in a database using the @output... parameters and query that information, the PlanCreationTimeHours computed column will reflect the datediff between the plan creation time and now (SYSDATETIME()) I am not sure this is correct and makes more sense to be compared with CheckDate as this will reflect the Creation age as of that collection time as I may decide to query this information some days later.

Current computed column definition:
PlanCreationTimeHours AS DATEDIFF(HOUR, PlanCreationTime, SYSDATETIME())

If the current behavior is a bug, please provide the steps to reproduce.

/* log sp_BlitzCache to a table */

EXEC sp_BlitzCache 
	@OutputDatabaseName = N'DBA',
	@OutputSchemaName = N'dbo',
	@OutputTableName = N'sp_BlitzCache';

You will need to have at least an hours' worth of information logged in the table in order to see the issue.

You can use this query to check the logged information which should show all rows where the computed column states a plan age larger than the actual plan age (Alter object names as required):

SELECT TOP (100) [ID]
      ,[CheckDate]
	  ,[PlanCreationTime]
	  ,[PlanCreationTimeHours]
	  ,DATEDIFF(HOUR,CAST([PlanCreationTime] AS DATETIMEOFFSET(7)),[CheckDate]) AS [PlanCreationTimeHours_Revision]
  FROM [DBA].[dbo].[BlitzCache]
  WHERE DATEDIFF(HOUR,CAST([PlanCreationTime] AS DATETIMEOFFSET(7)),[CheckDate]) = 0 /* plan age less than an hour ago at point of collection */
  AND CheckDate < DATEADD(HOUR,-1,SYSDATETIMEOFFSET()) /* Collection was more than an hour ago */
  AND [PlanCreationTimeHours] > 0 /* Where current column definition states plan age is older than an hour */
  ORDER BY ID ASC

What is the expected behavior?
I would expect the plan age to reflect the age at point of collection rather than comparing against the current date/time

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
I have checked SQL2012 and SQL2016

Happy to pick this up and code the fix if agreed that it is indeed a bug?

Thanks

[BrentOzar]

Ah! Yep, great catch - that's a bug! If you run out of time and can't work on it, let me know and I can hop in there too. Thanks!

[BrentOzar]

Thanks for the pull request! Looks good, merging into dev, will be in the November release with credit to you in the release notes.