Which dynamic management objects should you identify?

You have a SQL Server 2014 database.
You plan to create a stored procedure that will retrieve the following information:
The XML content of the query plans that is stored in memory
The number of times each query plan is used
You need to identify which dynamic management objects must be used to retrieve the required information for
the stored procedure.
Which dynamic management objects should you identify?
To answer, drag the appropriate dynamic management object to the correct requirement in the answer area.
Select and Place:

You have a SQL Server 2014 database.
You plan to create a stored procedure that will retrieve the following information:
The XML content of the query plans that is stored in memory
The number of times each query plan is used
You need to identify which dynamic management objects must be used to retrieve the required information for
the stored procedure.
Which dynamic management objects should you identify?
To answer, drag the appropriate dynamic management object to the correct requirement in the answer area.
Select and Place:

Answer:

Explanation:
Note:
* sys.dm_exec_query_plan
Returns the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan
handle can either be cached or currently executing.
* sys.dm_exec_cached_plans
Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this
dynamic management view to find cached query plans, cached query text, the amount of memory taken by
cached plans, and the reuse count of the cached plans.



Leave a Reply 0

Your email address will not be published. Required fields are marked *