Under the covers, Azure SQL Database is just good old fashioned SQL Server and this includes Query Store on Azure. While many things can be different when working with Azure, Query Store just isn’t one of them. Let’s talk about it a bit.
Query Store on Azure
Unlike your databases created on a SQL Server instance (big iron, VM, hosted VM, wherever), the databases you create on Azure SQL Database have Query Store enabled by default. Managed Instance and Synapse are different. In their case, they operate the same as an instance of SQL Server, off by default. Further, in the single database of Azure SQL Database, you can’t, as in can not, disable Query Store. It’s on by default and it’s staying that way. This leads to a simple conclusion, if you’re running Azure SQL Database, it’s a good idea to learn about Query Store, because you’re going to be dealing with it whether you want to or not.
Good news is, Query Store is awesome. For most people, most of the time, it’s just going to sit there, with the default settings, collecting good information about the queries running on your system. All you really need to do is access that info. Further, getting at the information is really easy. Further, this is the same with your local instances. Query Store is turned on, or off, at the database level. So, to see the information gathered, you go to the database.
You can query the system views for Query Store on Azure (I have several posts showing different ways to do this), or, you can use SQL Server Management Studio. Connecting up to your Azure database, you can see the following in the Object Explorer:
Those are all reports, built into SSMS, that let you see into the Query Store data. If I were you, I’d start with “Top Resource Consuming Queries”. That’s the stuff that’s causing you pain.
Query Store Reports
Double clicking that report, and you should see something that looks like this:
My little test instance only has a few queries run against it in the last 24 hours. You may see more queries than three, up to the limit of 25 on the report. You can click on any given query on the bar graph. That will then show it’s various execution plans and when they were compiled on the right. Click on any of them will show the plan on the bottom.
The default for the report is to show the queries listed by Duration. You can spot this in the upper left of your screen. That drop down is where you can start to see some real power for the Query Store information you’re getting by default on your Azure SQL Database. Click on it to see the other options you have for sorting your queries:
With that listing, you can easily, and quickly, get a great understanding of exactly what kind of queries are being run on your system, and how they’re behaving. There are a whole bunch of other settings you can adjust on the report. I’m not going over them all. The idea here is simple, you have information, right at your fingertips. Further, for that information, there’s literally nothing you have to do except reach out and take it. That’s pretty handy.
That is, of course, the happy path. Chances are, you may need to make some adjustments to your Query Store in a given database. Microsoft has a good document to help get you started on the types of adjustments you may want to make. Read through that as part of your learning about Query Store on Azure SQL Database.
Conclusion
As you can see, the fundamentals to get started using Query Store are very simple. Yes, like anything, it can get complex. However, like anything, start at the beginning and work through the fundamentals with Query Store. Walk before you try to run.