Use DMV and CROSS APPLY to Get Details of SQL’s Cached Query Plans
SQL 2005’s dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. For example, following query fetches SQL server’s cached query plans. You can use the refCounts and useCounts for performance analysis.
SELECT cached.*, sqltext.* FROM sys.dm_exec_cached_plans cached CROSS APPLY sys.dm_exec_sql_text (cached.plan_handle) AS sqltext
You can click here to see the screen snapshot of the query output.
Note: CROSS APPLY, a new feature of SQL Server 2005, is quite simple: an INNER (or outer) JOIN between a table and a table-valued function.
sys.dm_exec_sql_text: Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.
sys.dm_exec_cached_plans: Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.