During a recent SQL Server Performance Tuning Practical Workshop, I received an interesting question about query plans. The attendee wanted to know how to find the query plan, cache size, and execution count for any query. In this article, we will explore the answer to this question and provide a simple script to retrieve this information.

If you run an individual query, you can click on the SELECT statement and check the size of the query plan. However, if you want to see the cache size, query plan, and execution count for multiple queries, you can use the following script:

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;

This script retrieves the necessary information from the system views in SQL Server. It lists all the queries that are stored in the Query Cache, along with their respective cache size, query plan, and execution count. The result set can be further filtered using additional WHERE conditions to display specific data.

By understanding the query plans and cache size, you can gain insights into the performance of your queries. Analyzing the execution count can help identify frequently executed queries that may benefit from optimization or indexing.

If you find this script useful for your business, I encourage you to give it a try. Feel free to modify it according to your specific requirements. If you have any feedback or suggestions, please let me know. I would love to hear how you are using these scripts in your environment.

Stay tuned for more SQL Server tips and tricks. Happy querying!