December 31, 2008 at 10:06 am
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.
December 31, 2008 at 5:20 pm
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
January 2, 2009 at 7:30 am
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
January 2, 2009 at 9:04 am
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.
January 2, 2009 at 9:58 am
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