Computer Hacking Forensic Investigator (CHFI v10) — Question 402

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?

Answer options

• A. Use: select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_plan_attributes(plan_handle) followed by: select * from sys.dm_exec_query_stats
• B. Use: select * from sys.dm_exec_cached_plans cross apply sys.dm_exec_sql_text(plan_handle) followed by: select * from sys.dm_exec_plan_attributes(plan_handle)
• C. Use: select * from sys.dm_exec_sql_text(plan_handle) cross apply sys.dm_exec_cached_plans followed by: select * from sys.dm_exec_query_stats
• D. Use: 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

Correct answer: D

Explanation

The correct answer is D because it effectively retrieves the SQL text from the cached plans and then gathers the performance statistics from sys.dm_exec_query_stats. Option A is incorrect as it retrieves plan attributes instead of SQL text first, while B fails to gather performance statistics in the correct sequence, and C incorrectly attempts to access cached plans before the SQL text, leading to incomplete data retrieval.