| title | sys.sp_change_feed_configure_parameters (Transact-SQL) | ||||
|---|---|---|---|---|---|
| description | The sys.sp_change_feed_configure_parameters system stored procedure is used to increase the batch size with higher transactions. | ||||
| author | WilliamDAssafMSFT | ||||
| ms.author | wiassaf | ||||
| ms.reviewer | imotiwala, ajayj, randolphwest | ||||
| ms.date | 12/17/2025 | ||||
| ms.service | fabric | ||||
| ms.subservice | system-objects | ||||
| ms.topic | reference | ||||
| ms.custom |
|
||||
| f1_keywords |
|
||||
| helpviewer_keywords |
|
||||
| dev_langs |
|
||||
| monikerRange | >=sql-server-ver16 || =azuresqldb-current || =azuresqldb-mi-current || =fabric || =fabric-sqldb || =azure-sqldw-latest |
[!INCLUDE sqlserver2022-asdb-asdbmi-asa-fabricmirroredsqldb-fabricsqldb]
Configures optional performance settings for the change feed for the current database context.
This system stored procedure is used to fine tune the operational performance for:
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
sys.sp_change_feed_configure_parameters
[ [ @maxtrans = ] max_trans ]
[ , [ @pollinterval = ] polling_interval ]
[ , [ @autoreseed = ] autoreseed ]
[ , [ @autoreseedthreshold = autoreseed_threshold_percent ]
[ , [ @dynamicmaxtrans = ] transactions ]
[ , [ @dynamicmaxtranslowerbound = ] transactions_lower_bound ]
[ ; ]
Data type is int. Indicates the maximum number of transactions to process in each scan cycle.
Used to reduce latency by decreasing change batch size with @maxtrans, or to reduce the cost by increasing the batch size. As the batch size increases, fewer I/O operations are performed.
- For Azure Synapse Link, the default value if not specified is
10000. If specified, the value must be a positive integer. - For Fabric mirroring, this value is dynamically determined and automatically set.
Data type is int. Describes the frequency that the log is scanned for any new changes, in seconds.
- For Azure Synapse Link, the default interval if not specified is 5 seconds. The value must be
5or larger. - For Fabric mirroring, this value is dynamically determined and automatically set.
Applies to: Fabric Mirroring only
The autoreseed argument defines the setting of the autoreseed option. 0 = disabled, 1 = enabled at the provided autoreseed_threshold_percent.
The autoreseed feature is disabled by default in SQL Server 2025 (Preview). The autoreseed feature is enabled and can't be managed or disabled in Azure SQL Database and Azure SQL Managed Instance.
During reseed, the mirrored database item in Microsoft Fabric is available but will not receive incremental changes.
For more information, see Configure automatic reseed for Fabric mirrored databases.
Applies to: Fabric Mirroring only
The autoreseedthreshold argument defines the log usage percentage threshold when an autoreseed event triggers. By default, 70.
Applies to: Fabric Mirroring only
Whether or not the dynamic maximum transactions setting for Fabric Mirroring is enabled. 0 = disabled, 1 = enabled. Fabric follows a maximum number of transactions to process in each scan cycle. For more information, Mirrored databases from SQL Server performance.
The dynamic maximum transactions feature is enabled by default in SQL Server 2025 (Preview). The dynamic maximum transactions feature is enabled and can't be managed or disabled in Azure SQL Database and Azure SQL Managed Instance.
Applies to: Fabric Mirroring only
The lower bound for dynamic maxtrans setting for Fabric Mirroring. By default, the lower bound value is 200.
0 (success) or 1 (failure).
A user with CONTROL database permissions, db_owner database role membership, or sysadmin server role membership can execute this procedure.
Applies to: Fabric Mirroring only
Use the following T-SQL sample to enable automatic reseed on the current mirrored database. Specify a log usage percentage threshold to trigger an autoreseed event, for example 70%. For more information, see Configure automatic reseed for Fabric mirrored databases.
USE <Mirrored database name>
GO
EXECUTE sys.sp_change_feed_configure_parameters
@autoreseed = 1
, @autoreseedthreshold = 70;Applies to: Fabric Mirroring only
Use the following T-SQL sample to disable automatic reseed on the current mirrored database.
USE <Mirrored database name>
GO
EXECUTE sys.sp_change_feed_configure_parameters @autoreseed = 0;Applies to: Fabric Mirroring only
To enable the dynamic maximum transactions feature, set @dynamicmaxtrans to 1. For example:
USE <Mirrored database name>
GO
EXECUTE sys.sp_change_feed_configure_parameters
@dynamicmaxtrans=1;To disable the dynamic maximum transactions feature, set @dynamicmaxtrans to 0. For example:
USE <Mirrored database name>
GO
EXECUTE sys.sp_change_feed_configure_parameters
@dynamicmaxtrans=0;Verify the setting of the dynamic maximum transactions feature with sys.sp_help_change_feed_settings.
Applies to: Fabric Mirroring only
To modify the maximum and lower bounds for the dynamic maximum transactions feature, use @maxtrans and @dynamicmaxtranslowerbound respectively. For example:
USE <Mirrored database name>
GO
EXECUTE sys.sp_change_feed_configure_parameters
@dynamicmaxtrans=1
, @dynamicmaxtranslowerbound=5
, @maxtrans=5000;