One of the biggest disadvantages of SQL Server has always been the lack of a monitoring system. A system that collects performance information about executed processes and saves that data over time.
I know, we have a profiler, traces, and extended events. But some of them are deprecated, need extra care, knowledge, and configuration. I mean something simple to use that does not need deep knowledge.
In SQL Server 2016, Microsoft introduced something new - query store (QS). That feature meets most of the requirements. It captures a history of queries, plans, and runtime statistics, and retains these for our review. Exactly as we want it!
It gives us information on the choice of the query plan and its performance. Helping find performance differences caused by query plan changes and simplifies troubleshooting. In most cases, it is possible to do so without using any queries and only by using a visual graph! Turn it on with two-three clicks, and you're ready to go!
* Minimal need - version 16 or higher of Management Studio (SSMS).
After the last words, most DBAs will throw a stone at me, but it's true. Default pre-configurations are okay for the "non-DBA's" needs. I'm not trying to belittle knowledge or skills, but it's more than enough for the developers and tech leads.
For deep diving, we can configure the QS using the ALTER DATABASE options or GUI interface.
For new DBs on SQL Server 2022, Azure SQL Database, and Managed Instance, it's turned on by default. So, if you are a lucky one and your environment is one of those, you can stop reading here (kidding, don't!). What if your environment is not one of those, or someone has turned this feature off?
As we can understand, for SQL Server versions 2016-2019, the query store is not enabled by default. Same as for Azure Synapse Analytics databases. It must be turned on manually, to track performance history and troubleshoot query plan-related issues.
By the way, the simplest way to check if it's enabled or not is just to query a catalog view. Like this:
SELECT
[name],
is_query_store_on
FROM
sys.databases;
GOIf the "is_query_store_on" column is 1, the query store is enabled for the database whose name appeared in the first column. Otherwise (0), indicates that the query store is not enabled.
How to enable Query Store manually:
First, the query store is a database-level feature, which means that it must be enabled on every database separately.
Second, the smallest permission set you need to have to use query store is VIEW DATABASE STATE. That will allow you access to query store reports. To enable QS or change some property, minimum permission is ALTER on the Database.
Third, at least version 16 of SQL Server Management Studio (SSMS).
Now we are ready to go: Right-click on the DB > Properties > Query Store
It is important to understand that query store has two operational modes.
1. Actual: the state of this feature at every particular moment;
2. Requested: the user's desired state for the feature.
When we first open the query store Database property, both of them will be in the OFF state, and we can change only one of them. By switching to the requested mode, we are changing the actual mode. Choose "Read Write" operational requested mode and click OK. Done!
If there is no issue, your query store is turned on and starts capturing all the "goodies" we talked about.
Yeah, I know, you will ask what this "read-only" requested mode is. Well, mostly it's not in use, but if you're asking, it means no new capturing. This says that new query runtime statistics or executed plans will not be collected. Just like this. So, if you want to stop new data collection, without turning off the query store - use it :)
Almost forgot! We can enable query store by using Transact-SQL statements too!
How to enable Query Store using T-SQL:
We can use this statement to enable the query store for a specific database
ALTER DATABASE [MyDB] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);* In Azure Synapse Analytics, execute it without additional options. Just remove (OPERATION_MODE = READ_WRITE).
Want to find candidates for enabling this feature across all databases? No problem, this simple query will generate an enable script for each discovery. Just keep in mind that query store cannot be enabled for the master or tempdb databases, pretty useless for the model database.
SELECT
[name],
CASE
WHEN SERVERPROPERTY('EngineEdition') != 6 THEN CONCAT(N'ALTER DATABASE [', [name], N'] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE); ')
ELSE CONCAT(N'ALTER DATABASE [', [name], N'] SET QUERY_STORE = ON; ')
END AS QSEnableScript
FROM
sys.databases
WHERE
is_query_store_on != 1
AND database_id > 2;
GOBy the way, if you are planning to use the FORCE_LAST_GOOD_PLAN automatic tuning option, remember that this plan will fail if the Query Store isn't enabled or if the Query Store isn't in Read-Write mode. But this is another story :)
More reading: