The cache plan handle can be found by querying select * from sys.dm_exec_cached_plans How do I get the sql text and Query Plan for this plan handle? create function returnSQL(@planhandle varbinary(max)) returns table As return select sql.text,pc.usecounts,pc.cacheobjtype,pc.objtype,pc.size_in_bytes, Pq.query_plan From Sys.dm_exec_sql_text(@planhandle) as sql cross join Sys.dm_exec_query_plan(@planhandle) as pq join Sys.dm_exec_cached_plans as pc on pc.plan_handle = @planhandle; […]

Every T-SQL including database information (indexes etc.,) is passed to SQL server query optimizer which generates a Query Plan, this query plan is executed by SQL Server Engine. Each query plan has a query handle, these cached query plan will be cleared for every restart of sql server. Now this is not suitable for a […]