Home » Microsoft » 70-473 v.2 » Which command should you use?
You have a Microsoft Azure SQL database named DB1.
You need to monitor DB1 to identify all regressed queries.
Which command should you use?
A. select*from sys.dm_exec_query_stats
B. ALTER DATABASE DB1 SET QUERY_STORE (OPERATION_MODE=READ_WRITE)
C. select*from sys.dm_exec_query_stats cross apply sys.dm_exec_query_plan(plan_handle)
D. ALTER DATABASE DB1 SET QUERY_STORE (QUERY_CAPTURE_MODE= ALL)GO
Correct Answer: C
Explanation/Reference:
Queries with multiple plans? These queries are especially interesting because they are candidates for regressions due to plan choice change. The following query identifies these queries along with all plans:
WITH Query_MultPlans
AS
(
SELECT COUNT(*) AS cnt, q.query_id
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
GROUP BY q.query_id
HAVING COUNT(distinct plan_id) > 1 )
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-2017#Regressed
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/04/24/how-to-find-query-plan-choice-regressions-with-sql-server-2017-ctp2/
