SQL SERVER – List Query Plan, Cache Size, Text and Execution Count

I love the curious questions from people who attended the class. Here is one such question during recent SQL Server Performance Tuning Practical Workshop. Let us see the question which I received about the Query Plan.

How do I find out query plan, cache size and execution count for any query?

This is indeed a very interesting question and the answer is very simple. If you run any individual query, you can click on the SELECT statement and check the size of the said plan. Here is an example of the same.

However, if you want to see the cache size, query plan along with the execution count, you can run the following query. The query only lists all the queries which are from the Query Cache.

SELECT
cplan.usecounts,
qrypln.[query_plan],
sqltxt.text,
planobj.pages_in_bytes / 1024 AS [PlanKB],
cplan.plan_handle,
cplan.cacheobjtype,
cplan.objtype
FROM sys.dm_exec_cached_plans cplan
OUTER APPLY sys.dm_exec_sql_text(cplan.[plan_handle]) sqltxt
OUTER APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qrypln
INNER JOIN sys.dm_os_memory_objects planobj
ON planobj.memory_object_address = cplan.memory_object_address
WHERE cplan.parent_plan_handle IS NULL
AND cplan.cacheobjtype IN (N'Compiled Plan', N'Compiled Plan Stub')
ORDER BY cplan.objtype, cplan.plan_handle;

Here is the result of the query listed above. Please note it also lists the above query in the result set as well. If you want you can write additional WHERE condition to list the necessary data.

Let me know if you use such scripts for your business. I would love to blog about it so others can be benefited and I will give you due credit for the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)