Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  Other SQL Server 2008 Topics  Caching using Parameterize queries

Author  Topic  rbhatia Starting Member 2 Posts Posted - 2008-12-31 : 12:03:44 I’ve been reading up on how SQL caches execution plans and came across one issue that has me puzzled. I have the following code blocks where Code Block 1 is a query that uses parameters while Code Block 2 displays the cache. The idea was to see how SQL caches queries when parameters are used in queries : --CODE BLOCK 1: DECLARE @Email varchar(100)='david%' DECLARE @State varchar(10)='MD' SELECT pea.[EmailAddress] ,e.[BirthDate] ,a.[City] FROM [Person].[Person] c JOIN [HumanResources].[Employee] e ON c.BusinessEntityID = e.BusinessEntityID Join Person.EmailAddress pea on c.BusinessEntityID=pea.BusinessEntityID Join Person.BusinessEntityAddress bea ON e.BusinessEntityID=bea.BusinessEntityID JOIN [Person].[Address] a ON bea.[AddressID] = a.[AddressID] JOIN [Person].[StateProvince] sp ON a.[StateProvinceID] = sp.[StateProvinceID] WHERE pea.[EmailAddress] LIKE @Email AND sp.[StateProvinceCode] = @State ; --CODE BLOCK 2: SELECT [cp].[refcounts] ,[cp].[usecounts] ,[cp].[objtype] ,[st].[dbid] ,[st].[objectid] ,[st].[text] ,[qp].[query_plan] FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp ; I first ran Code Block 1 against the AdventureWorks2008 database four times. Then, I ran Code Block 2 to review the cache. I noticed that the cache had one instance of the query with UseCounts=4 which is what I expected. Next, I reran Code Block 1 three more times and then ran Code Block 2 to review the cache again this time expecting to see still one instance of the query with UseCount=7 (4+3). Instead, I saw two instances of the same query in the cache with Use Counts 4 and 3 respectively. Why is that ? Shouldn’t SQL have used the same execution plan that was in the cache since the query was identical ? Isn’t that how parameterization is supposed to work ?