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