Performance Tuning
Enabling Query Store in Azure SQL Database
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:
|
1 |
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:
|
1 2 3 4 5 6 7 |
SELECT desired_state_desc , actual_state_desc , readonly_reason, current_storage_size_mb , max_storage_size_mb , max_plans_per_query FROM sys.database_query_store_options ; |
As long as things are running correctly, the desired and actual states should read either ALL or AUTO. For more information about using Query Store, check out Monitoring Performance by Using the Query Store.
The Takeaway
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.
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
Nice! Instant fix for the cache reloading. I love this blog, it saves me so much trouble.
Glad we could help you out!
Hi
Yes – it is great option for the SQL Azure users – we do not have profiler or EE for now -its help us.
good stuff, thank you!
Hi ,
When I Try to Retrieve the Data from External Database I Found Below Error
Error accessing the shard map manager database. Please verify that the shard map manager database is available. If it is available, please verify that the definition and affiliated credential are correct for external data source RemoteReferenceData.
Msg 46806, Level 16, State 2, Line 2
Alfaiz – this has literally nothing to do with what this post is about.
This isn’t the first time you’ve left a completely, wildly unrelated question on a blog post. Please hit http://dba.stackexchange.com with your questions.