As per my understandings, from sys.dm_exec_query_stats
, we can cross apply other DMFs to get the statement level cached plans and the query text. From sys.dm_exec_cached_plans
, we can get the cached plans and the query text (by cross apply other DMFs). So what's the differences?
Differences Between sys.dm_exec_query_stats and sys.dm_exec_cached_plans – SQL Server
dmvexecution-planmetadatasql server
Related Question
- SQL Server Page Allocation Failure – Error FAIL_PAGE_ALLOCATION 1
- Sql-server – Shredding Query Plan XML For Potential Skewed Estimates – Data Type Conversions
- SQL Server – How to Include Actual Row Counts in Execution Plans from Query Cache or Query Store
- Practical Impacts of sys.dm_exec_query_stats Warning in SQL Server
- SQL Server – When Are Non-Parameterized, Non-Trivial, Adhoc Query Plans Reused?
- Sql-server – Are stored procedure query texts stored in any tables
- Sql-server – Is it possible to get the name of a procedure by it’s plan handle or query plan hash if the execution plan is already cleared from the plan cache
Best Answer
In terms of getting the plan, using sys.dm_exec_query_stats you can use statement_start_offset and End offset to get the plan for individual statement in a stored procedure.
Eg
And obviously it has different information regarding query execution stats and plans.