The 11 Configuration Options Added in SQL Server 2022, and Their Documentation

· by · in

After an upgrade, I usually run sp_configure and compare the output to the checklist I already trust. Most of the time, the new names are obvious. Sometimes they appear in the output before I can find enough documentation to explain them.

A woman DBA with a notebook studies a control panel. Most switches have labels, and a few newer switches on the right have blank label plates, one of them glowing.

In the first post in this series, I checked new system views, functions, stored procedures and wait types in SQL Server 2022. For this post, I applied the same idea to server-wide configuration options. sys.configurations is the source. It is the system catalog view that returns one row for each server-wide configuration option. It includes the configured value, running value, minimum, maximum, is_dynamic and is_advanced columns.[1]

My 2019 to 2022 diff found 11 configuration options that were present in SQL Server 2022 and absent from SQL Server 2019. I found option pages for four of them, a linked feature article for one, bare rows in the server configuration table for five, and no matching option name for one.

How I checked

I used the same inventory files from the series opener. The 2019 instance is SQL Server 2019 CU32 Developer. The 2022 instance is SQL Server 2022 CU25 Enterprise. The 2025 instance is SQL Server 2025 CU9 Enterprise Developer. CU means cumulative update. I also checked the 2025 RTM inventory, where RTM means the initial release with no cumulative update.

For the count, I filtered pairwise.csv to Series = 2019-2022, Kind = new, and Cat = configuration. For the range and advanced-option flag, I read the matching rows from the 2022 and 2025 tab-separated inventory files. Then I ran this read-only query against the 2025 instance to get the current running value and the is_dynamic flag:

For documentation, I checked the live Microsoft Learn server configuration options page, the sp_configure page, and the sys.configurations page. I also checked each option page or linked article I could find. Then I searched the docs source repositories for SQL Server, Azure and Fabric with whole-word fixed-string git grep searches.[2][3][4]

The 11 rows

The table below uses the 2019 to 2022 diff, the 2022 inventory range, the 2025 value_in_use, and my documentation check from September 26, 2026.

Option 2022 range Advanced 2025 value_in_use Documentation status
ADR Cleaner Thread Count 1 to 32767 Yes 1 Own page
backup compression algorithm 0 to 2 No 0 Own page
Data processed daily limit in TB 0 to 2147483647 No 2147483647 Row in table only
Data processed weekly limit in TB 0 to 2147483647 No 2147483647 Row in table only
Data processed monthly limit in TB 0 to 2147483647 No 2147483647 Row in table only
external xtp dll gen util enabled 0 to 1 No 0 Linked feature article, not an option page
hardware offload config 0 to 255 Yes 0 Row in table only
hardware offload enabled 0 to 1 Yes 0 Own page
hardware offload mode 0 to 255 Yes 0 Row in table only
openrowset auto_create_statistics 0 to 1 Yes 1 Not found by this search
suppress recovery model errors 0 to 1 Yes 0 Own page, Azure SQL Managed Instance only

All 11 options are still present in the 2025 CU9 inventory. Ten have the same minimum and maximum in the 2025 CU9 inventory. backup compression algorithm changed from a maximum of 2 in SQL Server 2022 to a maximum of 3 in SQL Server 2025. The Learn page also adds Zstandard (ZSTD) as option value 3 in SQL Server 2025.[5]

Documentation gaps

The ADR Cleaner Thread Count documentation does not align cleanly across pages. The option page says the setting supports accelerated database recovery (ADR) starting in SQL Server 2022 and in Azure SQL Managed Instance. It also says the cleaner is single-threaded by default. The server configuration options table currently lists the option as SQL Server 2019 and later. My SQL Server 2019 CU32 inventory did not have the row, while the SQL Server 2022 CU25 and SQL Server 2025 CU9 inventories did.[6]

In the hardware offload group, one option has an explanation and two do not. hardware offload enabled has its own page, and that page says changing it requires a restart. The server configuration options table marks all three hardware offload options with the restart-required code. Only hardware offload enabled is linked. I found hardware offload config and hardware offload mode only as plain-text table rows, not as pages that explain their values.[7]

The Synapse serverless SQL pool cost-control article uses the same daily, weekly and monthly pattern as the three Data processed ... limit in TB rows. The article says cost control can set daily, weekly and monthly budgets for data processed. It also shows sp_set_data_processed_limit calls for daily, weekly and monthly. The same article then queries sys.configurations where the name is like Data processed %.[8] I found the three option names as bare rows on the server configuration page, but I did not find SQL Server option pages for them.

suppress recovery model errors is documented, but the page applies to Azure SQL Managed Instance only. The page says the option changes attempts to alter the recovery model from errors to warnings in Managed Instance. The same option is present in the boxed SQL Server 2022 and SQL Server 2025 inventories, with value_in_use = 0 on my 2025 instance.[9]

I did not find a source that explains the purpose or product scope of openrowset auto_create_statistics. It is an advanced option. Its 2025 running value is 1, and I found it in the 2022 and 2025 inventories. I did not find that exact option name on the server configuration options page, the sp_configure page, the OPENROWSET page, or the ALTER DATABASE SCOPED CONFIGURATION page. Whole-word fixed-string searches of the SQL Server, Azure and Fabric docs source repositories also found no match for the option name.[10][11]

How I treat these

If I change one of the documented options, I test it first and put it through the normal server configuration change process. Their public pages or linked articles describe the feature they affect, even when they do not answer every operational question. The HkDllGen article says SQL Server 2022 CU17 and later can use the Microsoft-signed hkdllgen.exe process. It applies when you enable the external In-Memory OLTP DLL generator.[12]

I am more careful with the bare rows. A minimum, maximum and default value tell me what SQL Server accepts. They do not tell me what the value means, what workload it affects, or whether support expects customers to set it.

For openrowset auto_create_statistics, the searched sources do not explain its purpose, scope, or supported usage. The name suggests a relationship between OPENROWSET and automatic statistics creation, but I did not find a public source that says what it controls. I do not change it based on the name alone.

Next, I am applying the same check to the dynamic management views in SQL Server 2022.

Did you have to explain one of these configuration options during an upgrade review, or did I miss a Microsoft page that documents one of the bare rows? Tell me on Bluesky or LinkedIn, and I’ll update the notes.

References

  1. sys.configurations (Transact-SQL) - Microsoft Learn. Defines the configuration catalog view and the value, value_in_use, minimum, maximum, is_dynamic and is_advanced columns used in the query. ↩
  2. Server Configuration Options - SQL Server, Azure SQL Managed Instance - Microsoft Learn. Lists server configuration options, possible values, default values, restart requirement codes and supported products. ↩
  3. sys.sp_configure (Transact-SQL) - Microsoft Learn. Describes sp_configure output and maps config_value and run_value to sys.configurations.value and sys.configurations.value_in_use. ↩
  4. MicrosoftDocs/sql-docs, MicrosoftDocs/azure-docs and MicrosoftDocs/fabric-docs - GitHub. The Markdown source repositories searched with whole-word fixed-string git grep for each option name on 2026-09-26. ↩
  5. Server Configuration: Backup Compression Algorithm - Microsoft Learn. Documents the backup compression algorithm option, including SQL Server 2022 values 0 to 2 and SQL Server 2025 value 3 for ZSTD. ↩
  6. Server Configuration: ADR cleaner thread count - Microsoft Learn. Documents ADR Cleaner Thread Count and says the setting is used for ADR starting in SQL Server 2022 and in Azure SQL Managed Instance. ↩
  7. Server Configuration: hardware offload enabled - Microsoft Learn. Documents hardware offload enabled, integrated acceleration and offloading, default value 0 and restart requirement. ↩
  8. Cost management for serverless SQL pool - Microsoft Learn. Describes daily, weekly and monthly data processed budgets in Synapse serverless SQL pool and shows sp_set_data_processed_limit plus sys.configurations rows named Data processed %. ↩
  9. Server Configuration: suppress recovery model errors - Microsoft Learn. Documents the option for Azure SQL Managed Instance and says it can return warnings instead of errors for recovery model changes. ↩
  10. OPENROWSET (Transact-SQL) - Microsoft Learn. The OPENROWSET page checked for openrowset auto_create_statistics. ↩
  11. ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) - Microsoft Learn. The database-scoped configuration page checked for openrowset auto_create_statistics. ↩
  12. Create In-Memory OLTP App Control and Managed Installer Policies - Microsoft Learn. Documents HkDllGen and the external xtp dll gen util enabled sp_configure example for SQL Server 2022 CU17 and later. ↩
Tags: , , ,