Enabling and configuring Query Store

As with all new features, Query Store is not active for a database by default. You can enable and configure it in SQL Server Management Studio (SSMS) by using the database properties (Query Store Page) or by using Transact-SQL. To enable Query Store, your account must be db_owner of the database or a member of the sysadmin fixed server role.

Enabling Query Store with SSMS

To enable Query Store for a database, you need to select it and click on Properties. You can find a new Query Store property page at the bottom of the list. As mentioned at the beginning of the section, it is disabled by default, which is indicated by the values Off for both Operation Mode (Actual) and Operation Mode (Requested). To enable it, you need to change the value for the parameter Operation Mode (Requested) to Read Write, as shown in the following screenshot:

Figure 9-3; Enabling Query Store in SQL Server Management Studio

Enabling Query Store with Transact-SQL

The same action...