SQL Server 2016: Identify regression defects through Query Store

SQL Server 2016: Identify regression defects through Query Store

For most developers, a regression defect in performance can usually be traced back to a special event, such as a large influx of users or code changes. For database developers, the process is not that simple. With the rebuilding of indexes and updating of statistics, SQL Server may decide to "Overwrite" your code and regenerate the execution plan. If you cannot find the correct backup and the hardware of the same level as the production environment, it is basically impossible to understand the changes in the execution plan, at least for the moment.

In SQL Server 2016, Microsoft saves the historical changes to the execution plan through a feature named Query Store. Once Query Store is enabled, it records the information in each Query, including:

• Number of executions
• Execution time
• Memory usage
• Logical read
• Logical write
• Physical read
• Number of execution plan changes

To reduce the pressure on the server, the information is aggregated according to a fixed time window. If you need more detailed data, you should use the Extended Events feature instead.

To view this information, you can directly open the Regressed Queries view.

In this tool, you can view regression defects based on any recorded indicator. After finding the regression defect, you can choose to force SQL Server to use the previous execution plan.

Fine-tune Query Store

Because the tracking of these indicators may bring a lot of overhead, SQL Server allows you to fine-tune the Query Store. Adjustable factors include the length of the aggregation time window (in minutes), the maximum size of the Query Store (in MB), and the maximum number of executable plans that can be saved. You can also allow Query Store to record only queries that meet specific conditions.

Access through programming

Like most SQL Server features, all the information displayed in the regression query tool can be directly accessed through a series of management views.

This article permanently updates the link address: