Why is the cache plan duplicated?

%3CLINGO-SUB%20id%3D%22lingo-sub-798621%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20is%20the%20cache%20plan%20duplicated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798621%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20day%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369217%22%20target%3D%22_blank%22%3E%40Dmitriy4096%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20go%20point%20by%20point%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%26gt%3B%26gt%3B%26nbsp%3BWe%20have%202%20queries.%20Each%20has%20the%20same%20QueryHash%20and%20QueryPlanHash.%26nbsp%3BBut%20in%20the%20sys.dm_exec_cached_plans%20table...This%20is%20mistake%3F%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3EHash%20function%20by%20definition%20is%20a%20function%20which%20maps%20Multiple-To_Single%20relations%2C%20which%20mean%20that%20for%20several%20input%20we%20can%20get%20the%20same%20output.%20This%20has%20nopthing%20with%20the%20cache%20plan%20which%20might%20be%20difference.%20Therefore%2C%20Yes%20this%20is%20Normal%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegarding%20the%20title%3A%20%3CFONT%20color%3D%22%230000FF%22%3EWhy%20is%20the%20cache%20plan%20duplicated%3F%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3ENew%20EP%20might%20be%20created%20for%20many%20reason%20including%20even%20if%20you%20have%20small%20change%20in%20the%20query%20text%20like%20a%20blank%20space%2C%20or%20if%20(as%20mentioned%20above)%20you%20pass%20different%20parameter.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26gt%3B%26gt%3B%20I%20did%20not%20understood%20what%20is%20the%20second%20question%20you%20have.%20In%20general%20the%20if%20you%20use%20parameters%20then%20during%20first%20execution%20the%20server%20built%20the%20execution%20plan%20(EP)%20which%20fit%20this%20parameter%2C%20and%20this%20EP%20might%20be%20used%20for%20other%20execution%20even%20if%20the%20parameter%20is%20different.%20I%20recommend%20to%20search%20more%20information%20on%3A%26nbsp%3Bsql%20server%20parameter%20sniffing%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-813519%22%20slang%3D%22en-US%22%3ERe%3A%20Why%20is%20the%20cache%20plan%20duplicated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-813519%22%20slang%3D%22en-US%22%3ESo%20the%20plan%20is%20QueryPlanHash%20SyS.dm_exec_cashed_plans%20table...Did%20I%20write%20that%20wrongh%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-731194%22%20slang%3D%22en-US%22%3EWhy%20is%20the%20cache%20plan%20duplicated%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731194%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%202%20queries.%20Each%20has%20the%20same%20QueryHash%20and%20QueryPlanHash.%3CBR%20%2F%3EBut%20in%20the%20sys.dm_exec_cached_plans%20table%2C%20a%20cache%20is%20formed%20to%20perform%20each%20of%20these%20two%20queries.%3CBR%20%2F%3EThis%20is%20mistake%3F%3C%2FP%3E%3CP%3EIn%20fact%2C%20memory%20is%20inefficiently%20consumed%2C%20the%20caches%20of%20other%20requests%20are%20%22washed%20out%22%2C%20which%20degrades%20the%20performance%20of%20the%20DBMS.%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3ESynthetic%20example%20to%20play%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3Euse%20master%3CBR%20%2F%3EGO%3CBR%20%2F%3EDBCC%20FreeProcCache%3CBR%20%2F%3EGO%3CBR%20%2F%3Eselect%20COUNT(*)%20from%20sys.dm_exec_cached_plans%3CBR%20%2F%3EGO%3CBR%20%2F%3EDECLARE%20%40number%20INT%2C%20%40txt%20nvarchar(1000)%2C%20%40myid%20binary(16)%2C%20%40myidStr%20varchar(34)%3CBR%20%2F%3ESET%20%40number%20%3D%201000%3B%3CBR%20%2F%3EWHILE%20%40number%20%26gt%3B%200%3CBR%20%2F%3EBEGIN%3CBR%20%2F%3E%26nbsp%3B%20SET%20%40myid%20%3D%20CONVERT(binary(16)%2C%20NEWID())%3CBR%20%2F%3E%26nbsp%3B%20SET%20%40myidStr%20%3D%20sys.fn_varbintohexstr(%40myid)%3CBR%20%2F%3E%26nbsp%3B%20SET%20%40txt%20%3D%20'declare%20%40result%20varbinary(1)%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20set%20%40result%20%3D%20(select%20top%201%200%20as%20fld%20from%20dbo.spt_values%3CBR%20%2F%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20where%200x0%20%26lt%3B%26gt%3B%20'%20%2B%20%40myidStr%20%2B%20')'%3CBR%20%2F%3E%26nbsp%3B%20exec%20sp_executesql%20%40txt%3CBR%20%2F%3E%26nbsp%3B%20SET%20%40number%20%3D%20%40number%20-%201%3CBR%20%2F%3EEND%3B%3CBR%20%2F%3EGO%3CBR%20%2F%3Eselect%20COUNT(*)%20count%2C%20SUM(size_in_bytes%2F1024)%2F1024%20size_in_mbytes%20from%20sys.dm_exec_cached_plans%3CBR%20%2F%3EGO%3CBR%20%2F%3E%3CBR%20%2F%3EWhen%20executed%20with%20the%20value%20%22%40number%20%3D%201%22%20after%20executing%207%20entries%20in%20the%20sys.dm_exec_cached_plans%20table.%3CBR%20%2F%3EWhen%20executed%20with%20the%20value%20%22%40number%20%3D%201000%22%20after%20executing%201006%20records%20(39%20mb)%20in%20the%20sys.dm_exec_cached_plans%20table.%3CBR%20%2F%3EThe%20text%20%22Execution%20Plan%20XML%22%20of%20these%20requests%20matches%20exactly%2C%20except%20for%20the%20%40myidStr%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Frequent Visitor

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.

2 Replies

Good day @Dmitriy4096 ,

 

Let's go point by point:

 

>> We have 2 queries. Each has the same QueryHash and QueryPlanHash. But in the sys.dm_exec_cached_plans table...This is mistake?

Hash function by definition is a function which maps Multiple-To_Single relations, which mean that for several input we can get the same output. This has nopthing with the cache plan which might be difference. Therefore, Yes this is Normal

 

Regarding the title: Why is the cache plan duplicated?

New EP might be created for many reason including even if you have small change in the query text like a blank space, or if (as mentioned above) you pass different parameter.

 

>> I did not understood what is the second question you have. In general the if you use parameters then during first execution the server built the execution plan (EP) which fit this parameter, and this EP might be used for other execution even if the parameter is different. I recommend to search more information on: sql server parameter sniffing

 

So the plan is QueryPlanHash SyS.dm_exec_cashed_plans table...Did I write that wrongh?