Hi
1. Normally, when we run/refer the Modular Object(Procedure, Function,....Etc.,), Plan will be generated for that Modular Object and cached in sql server.
2. That Plan will be re-used whenever we call the objects again and again instead of Parsing and Compiling.
3. In this case, Procedure and User Defined Function also behaves same...
4. We can see/identity the Plan which been generated for an Object using "sys.dm_exec_cached_plans" DMV
A. Creating a Function:
Use SQLServer
Go
alter Function Function1() Returns Varchar(25)
as
Begin
Return('SQL Server ''Denaly''')
End
Go
B. Checking whether Plan is created for the function:
Use SQLServer
Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- No Plan cached yet.. Because, Plan will be generated and cached in SQL Server @ Every first time of the execution/call (or) some Recompile factors (or) when the Plan cleared from the cach..
C. Execution/Calling the Function:
Use SQLServer
Go
Select dbo.Function1()
D. Checking whether Plan is created for the function:
Use SQLServer
Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- Now, we have cached plan for the Function. and "Reuse" column has the value as "1"
E. Execution/Calling the Function again:
Use SQLServer
Go
Select dbo.Function1()
F. Checking whether Plan is created for the function:
Use SQLServer
Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- Now, we have cached plan "Reuse" column has the value as "2"
G. Execution/Calling the Function again:
Use SQLServer
Go
Select dbo.Function1()
Go
Select dbo.Function1()
H. Checking whether Plan is created for the function:
Use SQLServer
Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- Now, we have cached plan "Reuse" column has the value as "4"
- The "Reuse" column says the Function is not been Re-Compiled and the Plan created again and again whenever we call the function... Instead, It Re-uses the cached plan...
I. Forcely, Removing/Clearing the Plan cache of this Function:
DBCC FreeProcCache(0x050009004A0CC672B820B30B000000000000000000000000)
Go
- The Plan handle value for this Function taken from the script (H.)
- Now, Tha Cached plan was removed from the SQL Server cache. So, the Plan will be newly created for this function again when we call/run the function.
J. Execution/Calling the Function:
Use SQLServer
Go
Select dbo.Function1()
Go
K. Checking whether Plan is created for the function:
Use SQLServer
Go
Select P.objtype [Object Type], Object_Name(x.objectid) [Object Name],x.[text] [Script], P.usecounts [Reuses], P.plan_handle [Plan Handle] from sys.dm_exec_cached_plans P Cross Apply Sys.dm_exec_sql_text(P.Plan_Handle) X
Where P.objtype = 'Proc'
And x.objectid = OBJECT_ID('Function1')
Go
- Now, the "Reuses" value is "1". So, the Plan has been re-generated and used only once...
So, the Procedure and the Function are behaving as same for Caching the Plan and Re-using it!
Note: "User Defined Function" also treated as "Proc" in SQL Server Plan. Please refer the column "Object Type" in our tracking script!
I hope it would give you better understanding!
Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions
Jasminej, if this helps please login to Mark As Answer. | Alert Moderator