Hello All:
Can any body help me in understanding if the execution plan for functions cached in SQL server?
Any online resource for this?
Thanks Ashwani
Thanks, but in the link it is not written that user defined functions are also considered for plan caching?
Ashwani K
2010-06-22 07:01:58
This is a quote taken from the article:`When any SQL statement is executed in SQL Server, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If no existing execution plan exists, SQL Server generates a new execution plan for the query.`
Barry
2010-06-22 07:11:50
+1
A:
Yes they do go in the execution plan cache.
The sys.dm_exec_query_plan DMV will show a plan for given plan handle. Quote from there:
Query plans for various types of Transact-SQL batches, such as ad hoc batches, stored procedures, and user-defined functions, are cached in an area of memory called the plan cache. Each cached query plan is identified by a unique identifier called a plan handle. You can specify this plan handle with the sys.dm_exec_query_plan dynamic management view to retrieve the execution plan for a particular Transact-SQL query or batch.
AdaTheDev
2010-06-22 07:12:42