You are the database administrator of a SQL Server 2008 instance that contains several query plans.
You want toobtain information about a query plan that is cached by SQL Server for faster query execution.
Which Dynamic Management View (DMV) should you use?
A.
sys.dm_exec_query_stats
B.
sys.dm_exec_query_plan
C.
sys.dm_exec_text_query_plan
D.
sys.dm_exec_cached_plans
Explanation:
You should use the sys.dm_exec_cached_plans DMV. DMVs return information about server state, which canbe used to monitor the health of a server instance, tune the performance of the server, and diagnose problems. The sys.dm_exec_cached_plans is an execution-related DMV that can be used to obtain information aboutquery plans that are cached by SQL Server for faster query execution. You should not use the sys.dm_exec_query_stats DMV. The sys.dm_exec_query_stats DMV providesaggregate performance data for cached query plans. This DMV can be used to gather performance information,such as identifying the longest-running queries. You should not use the sys.dm_exec_query_plan DMV. The sys.dm_exec_query_plan DMV provides queryplan information in Extensible Markup Language (XML) format for a specified batch. You should not use the sys.dm_exec_text_query_plan DMV. The sys.dm_exec_text_query_plan DMVprovides query plan information in text format for a Transact-SQL batch or for a particular statement within abatch.
Objective:
Optimizing SQL Server PerformanceSub-Objective:
Collect performance data by using Dynamic Management Views (DMVs).References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Views (Transact-SQL) > Dynamic Management Views and Functions (Transact-SQL) >Execution Related Dynamic Management Views and Functions (Transact-SQL) > sys.dm_exec_cached_plans(Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > System Views (Transact-SQL) > Dynamic Management Views and Functions (Transact-SQL) >Execution Related Dynamic Management Views and Functions (Transact-SQL)