We have 2 queries. Each has the same QueryHash and QueryPlanHash.
But in the sys.dm_exec_cached_plans table, a cache is formed to perform each of these two queries.
This is mistake?
In fact, memory is inefficiently consumed, the caches of other requests are "washed out", which degrades the performance of the DBMS.
Synthetic example to play:
use master
GO
DBCC FreeProcCache
GO
select COUNT(*) from sys.dm_exec_cached_plans
GO
DECLARE @number INT, @txt nvarchar(1000), @myid binary(16), @myidStr varchar(34)
SET @number = 1000;
WHILE @number > 0
BEGIN
SET @myid = CONVERT(binary(16), NEWID())
SET @myidStr = sys.fn_varbintohexstr(@myid)
SET @txt = 'declare @result varbinary(1)
set @result = (select top 1 0 as fld from dbo.spt_values
where 0x0 <> ' + @myidStr + ')'
exec sp_executesql @txt
SET @number = @number - 1
END;
GO
select COUNT(*) count, SUM(size_in_bytes/1024)/1024 size_in_mbytes from sys.dm_exec_cached_plans
GO
When executed with the value "@number = 1" after executing 7 entries in the sys.dm_exec_cached_plans table.
When executed with the value "@number = 1000" after executing 1006 records (39 mb) in the sys.dm_exec_cached_plans table.
The text "Execution Plan XML" of these requests matches exactly, except for the @myidStr value.
