Caching with Parameterize queries

  • I’ve been reviewing 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 :

    [font="Courier New"]--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 ; [/font]

    [font="Courier New"]--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 ; [/font]

    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 ?

    Thanks.

  • Rakhi,

    I ran codeblock1 thirteen times and got what I expected under codeblock2 output(i.e. 13) but when I did a small modification in the query under codeblock1(I just pressed ENTER) then I got (13+1) which is quiet expected.

    Did you perform some change in the query because otherwise usecounts would be proportional to the number of times you execute the codeblock1?

    MJ

  • Maybe a change to the connection settings? This will cause a new plan to be compiled.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • No I did not change anything. Here is exactly how I ran it :

    Step 1: Highlight code block 1 (the main query) and hit the EXECUTE button 3 times.

    Step 2: Highlight code block 2 (the cache query) and hit the EXECUTE button to view the cache. I see the expected results where the UseCount for the main query was set to 3.

    Step 3: Highlight code block 1 (the main query) and hit the EXECUTE button 2 times.

    Step 4: Highlight code block 2 (the cache query) and hit the EXECUTE button to view the cache. I now see 2 instances of the main query in the cache, one with UseCount=3 and the other with UseCount=2.

    So in conclusion, so long as I run the main query multiple times in succession, without running any other queries in between, the plan is being reused. When I switch to a different query and then come back to run the main query again, a new plan is generated and cached.

  • I just repeated your tests and did not get your results.

    Just a guess, did you highlight it exactly the same way both times? Meaning, including carriage return and line feeds and comments... everything? If not, you can get a different plan.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply