Enter Query Store
Query Store, in short, is a way to track query performance over time. In Microsoft’s words, “The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review”. It’s like
sys.dm_exec_query_stats but it persists across reboots! And it has execution plans!
The Query Store is a great feature for on premises SQL Server backed applications, but it adds significant value in Azure SQL Database. Instead of potentially losing execution history and execution plans, developers can see the history of query performance and execution plans across reboots.
Enabling Query Store in Azure SQL Database
Point SSMS at your Azure SQL Database instance and run:
ALTER DATABASE my_databas SET QUERY_STORE = ON;
No, really, it’s that easy.
How Do I Really Know Query Store is Enabled?
Figuring out if Query Store is enabled is pretty easy, too. All you need to do is look at
sys.database_query_store_options. The following query should give you exactly what you need to know about the Query Store:
SELECT desired_state_desc ,
FROM sys.database_query_store_options ;
As long as things are running correctly, the desired and actual states should read either
AUTO. For more information about using Query Store, check out Monitoring Performance by Using the Query Store.
Azure SQL Database instances can be rebooted at any time. It’s important to keep this in mind and plan accordingly, not just in the application but also in how you approach tuning your T-SQL. You should enable the Query Store in Azure SQL Database instances where you care about performance.