SQL Server Management Studio (SSMS) is a popular client tool for managing SQL Server and its databases. With the release of SSMS v18.4, Microsoft has introduced new options in the Query Store, making it easier for database administrators to configure and manage query performance.

Query Store is a powerful tool introduced in SQL Server 2016 that helps in monitoring and troubleshooting database performance. It captures execution plans, tracks query usage, and provides information about CPU and memory consumption. Unlike the Plan Cache, which stores execution plans in-memory, Query Store captures data on disk and retains it even after SQL Server restarts.

In SSMS v18.4, Microsoft has added several GUI options to the Query Store page, allowing DBAs to set capture policies and make changes without writing any code. These options include:

• Max Plans per query value (MAX_PLANS_PER_QUERY)
• Wait Statistics Capture Mode value (WAIT_STATS_CAPTURE_MODE)
• Custom policies (STALE_CAPTURE_POLICY_THRESHOLD, EXECUTION_COUNT, TOTAL_COMPILE_CPU_TIME_MS, TOTAL_EXECUTION_CPU_TIME_MS)

These options can be easily configured using the Query Store tab in SSMS v18.4, providing a more user-friendly experience for managing query performance.

To enable Query Store for a database, simply right-click on the database, select Properties, and choose Query Store on the Database Properties page. From there, you can set the Operation Mode to Read Write and enable the Query Store feature.

The Max Plans per query value represents the maximum number of execution plans that will be stored per query in the Query Store. By default, this value is set to 200, but it can be adjusted based on the complexity and stability of your queries. It’s important to keep an eye on the count, as a large number of plans can consume disk space.

The Wait Statistics Capture Mode option allows you to control whether the Query Store captures wait statistics information. By default, this option is enabled, allowing you to collect wait stats data for troubleshooting performance issues.

In addition to the GUI options, you can also configure these settings using T-SQL in SSMS 18.3 and below. For example, you can use the following code to set the Max Plans per query and Wait Statistics Capture Mode:

ALTER DATABASE AdventureWorksDW2017 SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    MAX_PLANS_PER_QUERY = 300,
    WAIT_STATS_CAPTURE_MODE = OFF
)

The Query Store Capture Mode allows you to specify query capture policies for the database. You can choose to capture all queries, only current queries, or specific queries based on execution count and CPU consumption. The options include:

• All: Captures all queries executed against the database
• Auto: Starting from SQL Server 2019, this is the default option that excludes infrequent queries
• None: Stops capturing new queries
• Custom: Provides additional control for user-defined capture policies

When selecting the Custom option, you can customize the Execution Count, Stale Threshold, Total Compile CPU Time, and Total Execution CPU Time values. These settings help measure processor performance and should be carefully adjusted.

Overall, the new options added in the Query Store with SSMS v18.4 provide enhanced flexibility and productivity for managing query performance in SQL Server. Whether you prefer using the GUI or writing T-SQL code, you can easily configure and customize the Query Store to optimize your database’s performance.

By leveraging the power of SSMS 18.4 and SQL Server 2019, database administrators can effectively troubleshoot query performance and improve overall database efficiency.