Home > Workload Solutions > SQL Server > Best Practices > Intel-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Database Best Practices > Performance Best Practices > SQL Server 2019: Query Optimizer Hotfixes
In this best practice, we configured the SQL Server database to use recent fixes to the query optimizer. The goal of enabling query optimizer hotfixes is to take advantage of updates that could improve database performance.
Category | SQL Server 2019 Enterprise |
Product | SQL Server |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine Tuning |
Overview
The default approach that SQL Server uses for the query optimizer enables any fixes for the latest database compatibility level for a given product release. This means any fixes for the query optimizer will be used up to the compatibility level of the database, but any hotfixes beyond that compatibility level will not be used. Microsoft provides an option to enable query optimizer hotfixes using the scoped configuration QUERY_OPTIMIZER_HOTFIXES.
Use the following command to enable optimizer hotfixes:
ALTER DATABASE SCOPED CONFIGURATION QUERY_OPTIMIZER_HOTFIXES = ON;
This command configures the database to use all query optimizer hotfixes. For example, optimizations that were included in a recent database cumulative update will apply if the administrator has altered the scope to include query optimizer hotfixes. Using QUERY_OPTIMIZER_HOTFIXES applies at the database level, meaning the change would have to be made for each database.
Recommendation
After implementing query optimizer hotfixes, the database showed a slight increase in the following metrics:
There were no changes to the following metrics:
As NOPM, TPM, and BRPS showed slight performance gains, we recommend this as a Day 3 fine tuning best practice. As every database is maintained at a various update levels, we recommended testing this best practice in a non-production database to validate performance.
Implementation Steps
Perform the following command to enable query optimizer hotfixes:
ALTER DATABASE SCOPED CONFIGURATION QUERY_OPTIMIZER_HOTFIXES = ON;
Additional resources
Configure the max worker threads Server Configuration Option