You have an Azure SQL Database managed instance.
The instance starts experiencing performance issues.
You need to identify which query is causing the issue and retrieve the execution plan for the query. The solution must minimize administrative effort.
What should you use?
To identify the query causing performance issues and retrieve its execution plan while minimizing administrative effort, Query Store is the best option. Query Store captures a history of queries, plans, and runtime statistics automatically. It helps easily identify performance issues without significant administrative overhead by providing built-in reports and capabilities to pinpoint problematic queries and their execution plans effectively.
C. Query Store
When you see "must minimize administrative effort", go to DMVs.
https://blobeater.blog/2019/11/20/execution-plans-in-azure-sql-database/
Use Query Store to find wait statistics for each query over time. In Query Store, wait types are combined into wait categories. You can find the mapping of wait categories to wait types in sys.query_store_wait_stats. https://learn.microsoft.com/en-us/azure/azure-sql/database/identify-query-performance-issues?view=azuresql
The solution must minimize administrative effort
I believe this is D: Dynamic Management Views, because you need to see the queries causing the performance issues right now, not the past. Whenever I have to handle a performance issue in real time, I'm grabbing the DMVs, not jumping into Query Store.
I was thinking same, query store has more historical data. DMVs are fast just query and see. Query store need to be enabled,
Couldnt you make the argument that the Query Store would be the first place to go. It is virtually no administrative overhead to check the Regressed Queries report for example. Can you really say that D is correct and C is incorrect?
I'm not sure why people are brining up "null" jobs. The question only says Performance issues...
https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/monitoring-with-dmvs?view=azuresql
After some further research: Dynamic management views (DMVs) and dynamic management functions (DMFs) return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver16
Status of the request. Can be one of the following values: background rollback running runnable sleeping suspended Not nullable. I believe it is D based on this Microsoft article: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql?view=sql-server-ver16
Yoy need to enable query store in managed instance so d is close..
on a second thought, it should be DMVs. query store saves information about queries that completed (successfully or timed out), not queries currently running and giving us headaches at the present moment.
Query Store > Regressed Queries.
C is correct. D is incorrect, as Dynamic Management views does not store Execution Plan