Exam 312-49v10 topic 1 question 734 discussion

Retrieving SQL Text of Cached Entries: The sys.dm_exec_cached_plans view returns information about the cached query plans. The sys.dm_exec_sql_text(plan_handle) function returns the text of the SQL batch that is identified by the specified plan handle. Using a CROSS APPLY allows the combination of each plan handle from sys.dm_exec_cached_plans with the corresponding SQL text from sys.dm_exec_sql_text(plan_handle). Acquiring Aggregate Performance Statistics: The sys.dm_exec_query_stats view provides aggregate performance statistics for cached query plans. This includes data such as execution count, total elapsed time, and total logical reads. By using the specified queries in option D, the IT forensics team can gather comprehensive information about the SQL text of cached entries and also obtain performance statistics for further analysis.

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.