The next view I’d like to spend some time talking about is the sys.dm_exec_cached_plans view. sys.dm_exec_cached_plans gives a row for each cached plan in the plan cache for SQL server. The plan cache is used to cache a mapping of a particular SQL command to it’s corresponding compiled query. This is very valuable as it can be CPU intensive to compile queries and having a good rate of plan reuse is an easy way to mitigate the cost of compilation.
Since the plan cache is a cache plans do get evicted from it when there is memory pressure. So, do not expect it to be a full view of the workload against a server, but it does give a very good insight into what is happening.
Like the sys.dm_exec_requests view, you can lookup the text and plan of the query.
There are a few ways I primarily use this view:
1. I use this view to look at the actual plans that SQL server is executing for a particular query.
2. I use the view to look at possible queries running against a particular table. I’ll usually do a like restriction on the query text where I supply the table name.
SELECT [text].text,[plan].query_plan,* FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS [text] CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS [plan]
There are a few things in particular to watch out for in the plan cache from this view:
Adhoc queries – there are two types of workloads that will cause you to see these queries in the plan cache. The first being occasional one-off queries by someone running a statement against the database. This is expected in most DB systems as there usually will be a DBA or someone from the company trying to run custom queries against a data store. If this happens often in your business environment, it’s a good idea to try out the optimize for adhoc workload on your server. Rather than caching an entire plan for the query, instead a stub will be cached. If the query is executed again the stub will be replace with the actual compiled plan. For systems where a lot of single use queries get run, this is a good option.
Another instance where you’ll see a lot of these types of queries is from misbehaving applications. Application developers should write parameterized statements which separate out the parameters of a query from the actual SQL text. When this is done, it prevents a cached plan from being included for every combination of parameters that are included. When SQL is not parameterized, the parameters themselves are considered part of the text and will cause SQL Server to miss plans that otherwise could have been reused.
Forced parameterization is an option that cause SQL server to automatically separate out parameters from the SQL text. This is largely used for those programs which don’t appropriately parameterize their queries. However, this does not avoid the adhoc plan from being cached in SQL server. An adhoc plan gets cached prior to parameterization followed by looking up of the new parameterized query after parameterization has been forced.
Like all the views I’ve talked about up to now, there are many ways and possibilities to use them. I hope this gives you a good introduction to this view. You can find the documentation on sys.dm_exec_cached_plans here http://msdn.microsoft.com/en-us/library/ms187404.aspx