After an unexpected shutdown of a company's database server, the IT forensics team is tasked with collecting data from the Database Plan Cache to investigate potential issues. What query should they use to retrieve the SQL text of all cached entries and acquire additional aggregate performance statistics?
To retrieve the SQL text of all cached entries and acquire additional aggregate performance statistics, one should use the query: select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) followed by: select * from sys.dm_exec_query_stats. The sys.dm_exec_cached_plans view returns information about the cached query plans that SQL Server currently has in memory, and the cross apply with sys.dm_exec_sql_text(plan_handle) returns the SQL text of these cached plans. The sys.dm_exec_query_stats view provides the aggregate performance statistics for these plans, including information like execution counts and total worker time.
Issue the syntax select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text (plan_handle) to retrieve the SQL text of all cached entries The plan_handle argument retrieves the compiled query plans from the SQLCP or the OBJCP cache stores. Issue the syntax select * from sys.dm_exec_query_stat s to view the aggregate performance statistics for the cached query plans. It displays only one row per query statement.