As we all know SQL Server, will for good or bad, cache execution plans. Up until now it’s been very tough to see the cached execution plans. You pretty much had to grab the query or procedure (with the parameters) and run it with Query Analyzer and have it return the execution plan. The only other option was to use SQL Profiler to capture the query along with the execution plan which it used.
Starting in SQL Server 2005 you now have the ability to query the system catalog and see what query plans are stored in the cache, as well as the queries which trigger that plan to be used, as well as how many times that cached plan has been used.
The first DMV to look at is sys.dm_exec_cached_plans. This DMV gives you the size of the plan, the handle of the plan (which is binary), and the number of times the plan has been used.
The next DMV to look at is sys.dm_exec_requests. This DMV gives you the currently running processes, wait information, all the session settings for the processes, etc. It also includes the handle for all the plans which are currently being run. If you join this sys.dm_exec_requests to sys.dm_exec_cached_plans you can see exactly which processes are using which plans. Because this shows you the spid (the session_id column) and the plan_handle you can see what users are running which commands.
The next object is a system table function called sys.dm_exec_query_plan. This function accepts a single input parameter of a plan handle. There are several columns in the output, the most important of which is the query_plan. This is the actual xml plan which is stored in the SQL Server.
The last object we’ll be looking at is the sys.dm_exec_plan_attributes table function. This function also accepts a single input parameter of a plan handle. This function returns the various session settings which were in place when the plan was created. This is important information to have when working with query plans, as changing a single ANSI connection setting will force SQL to create a new execution plan.
Don’t forget how to view the XML execution plan in the UI.