sp_BlitzCache reporting >100% single-use plans

[BrentOzar]

Version of the script
SELECT @Version = '8.16', @VersionDate = '20230820';

What is the current behavior?
When identical queries run in different databases, the single-use plans warning reports higher than 100%:

If the current behavior is a bug, please provide the steps to reproduce.
DBCC FREEPROCCACHE, then create this stored proc in multiple Stack Overflow databases on the same server (like StackOverflow, StackOverflow2010, StackOverflow2013) and run it dozens of times in each database. In my case, I ran it via 3 instances of SQLQueryStress each pointing to a different Stack database, each running 4 threads x 50 calls.

CREATE OR ALTER PROC dbo.sp_GenerateBadSQL WITH RECOMPILE AS
	BEGIN
	DECLARE @Id1 INT = CAST(RAND() * 10000000 AS INT) + 1,
		@Id2 INT = CAST(RAND() * 10000000 AS INT) + 1;

	DECLARE @StringToExec NVARCHAR(1000) = 
		N'SELECT * FROM dbo.Users WHERE Views = ' + CAST(@Id1 AS NVARCHAR(100))
		+ N' AND Reputation = ' + CAST(@Id2 AS NVARCHAR(100))
		+ N' ORDER BY Reputation DESC;'
	EXEC(@StringToExec);

	SET @StringToExec = 
		N'SELECT * FROM dbo.Users WHERE Reputation = ' + CAST(@Id1 AS NVARCHAR(100))
		+ N' AND Views = ' + CAST(@Id2 AS NVARCHAR(100))
		+ N' ORDER BY Views DESC;'
	EXEC(@StringToExec);

	SET @StringToExec = 
		N'SELECT * FROM dbo.Users WHERE AccountID = ' + CAST(@Id1 AS NVARCHAR(100))
		+ N' AND UpVotes = ' + CAST(@Id2 AS NVARCHAR(100))
		+ N' ORDER BY DisplayName, Reputation DESC;'
	EXEC(@StringToExec);

	/* And a nicely parameterized one: */
	SET @StringToExec = N'SELECT /* PARAMETERIZED */ * FROM dbo.Users WHERE Views = @Id1 AND Reputation = @Id2 ORDER BY DisplayName, Reputation DESC;';
	EXEC sp_executesql @StringToExec, N'@Id1 INT, @Id2 INT', @Id1, @Id2;
	
	/* And a recompiling one: */
	SET @StringToExec = N'SELECT /* OPTION RECOMPILE */ * FROM dbo.Users WHERE Views = @Id1 AND Reputation = @Id2 ORDER BY DisplayName, Reputation DESC OPTION (RECOMPILE);';
	EXEC sp_executesql @StringToExec, N'@Id1 INT, @Id2 INT', @Id1, @Id2;

	END
GO

What is the expected behavior?
The volume knob shouldn't go up to 11, and you shouldn't have 173% single use plans.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
We've had hit or miss reports of this working and not working over the years.

[BrentOzar]

The root cause is that we're using two different DMVs for the calculations. In my case:

• sys.dm_exec_query_stats has about 1,200 rows
• sys.dm_exec_cached_plans has about 2,000 rows

I do find it amusing that sys.dm_exec_cached_plans contains rows for plans that don't exist in sys.dm_exec_query_stats:

Aaaand I'm not about to go any farther down that rabbit hole. The point is that sp_BlitzCache's approach of counting single-use plans in sys.dm_exec_cached_plans isn't going to work - we should be looking at the same DMV throughout, which in this case would be sys.dm_exec_query_stats.