Learn T-SQL Querying

sys.dm_exec_cached_plans

The sys.dm_exec_cached_plans DMV can be used to view all the query execution plans that are currently in the cache. Unlike sys.dm_exec_query_stats, which contains information about the execution of the query, this DMV contains information about the plan object itself, including things like the size of the plan, the type of plan (for example, stored procedure, prepared statement, ad hoc query, and so on), and the number of times the plan has been used. Also, since plans are stored as a batch, this DMV will have only one row per plan, rather than one row per statement as in sys.dm_exec_query_stats.

Here's an example of a query against sys.dm_exec_cached_plans:

SELECT TOP 10 plan_handle, usecounts, size_in_bytes, objtype, ...