How to identify a particular query reusing the plan or not ? [Resolved]

Posted by Devanand under Sql Server on 6/15/2010 | Views : 1425 | Status : [Member] | Replies : 4
hi dude

i am using one sql query continuously....

select * from stdCustomers where custpincode ='363 423'

how to identity this query reusing the plan or regenerating new everytime.

thanks in advance
devanand




Responses

Posted by: PandianS on: 6/15/2010 [Member] [MVP] Silver

Up
0
Down

Resolved
Hi
You have a small workaround here...

SCRIPT :1
select * from stdCustomers where custpincode ='363 423'
SCRIPT :2
SELECT	Q.plan_handle 'Plan Handle',

convert(varchar,Q.creation_time,120) 'Plan Created at' ,
convert(varchar,Q.last_execution_time,120) 'Plan Reused at',
Q.execution_count 'Plan Reused Count'
FROM sys.dm_exec_query_stats Q CROSS APPLY sys.dm_exec_sql_text(Q.PLAN_HANDLE) e
WHERE e.[text] LIKE 'select * from stdCustomers where custpincode =%'


Do the following steps:
1. Run the SCRIPT :2, If it returns any row then you have to Remove the Row(PLAN) from the Plan Cache.
2. Just copy the "Plan Handle" data from the step 1 (ie. 0x06000800244.........)
3. Run the script given below to remove the existing Plan from the Plan Cache
DBCC FREEPROCCACHE(Plan Handle)
ie: DBCC FREEPROCCACHE(0x06000800244.........)
The result will be "DBCC execution completed. If DBCC printed error messages, contact your system administrator."
4. Run the SCRIPT :2, it should not return any row. If returns, do the steps 2,3,4
5. Run the SCRIPT :1
6. Run the SCRIPT :2, If it returns any row then, do the following analysis.
6.1 If "Plan Created at" and "Plan Reused at" columns are SAME date and "Plan Reused Count" is 1 then "THE IS NEWLY CREATED PLAN".
6.2 If "Plan Created at" and "Plan Reused at" columns are NOT SAME date and "Plan Reused Count" is >1 then "THE PLAN REUSED N-1 TIMES" (here N is nothing but, "Plan Reused Count" value)
6.3 Whenever you run the SCRIPT :2, It will return "Plan Created at" and "Plan Reused at" and "Plan Reused Count" is >1.

If you want to generate a New Plan then, You have to remove the existing Plan from the Plan Cache.
Follow the steps 1,2,3

Conclusion :
If the "Plan Reused Count" is >1 then The "PLAN IS REUSED". The "Plan Reused at" column tells when was the Plan Re-used for this query.

I think this is clear about Generating / Re-Using the Plan.

If you need any clarifications, Pls get back....

Note: This script applicable only in SQL Server 2005/2008/2008 R2.

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 6/15/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Right Click on your Query and select execution plan

Thank you for posting at Dotnetfunda
[Administrator]

Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Devanand on: 6/16/2010 [Member] Starter

Up
0
Down
hi all

thanks for the excellent explanation.

thanks again
devanand



Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/21/2012 [Member] Starter | Points: 25

Up
0
Down
HI,

SQL SERVER MANAGEMENT STUDIO RIGHT CLICK

QUERY EXECUTION PLAN

REGARDS


sriram

Devanand, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response