One of the ongoing challenges in database administration is SQL Server statistics maintenance. On large production systems, updating statistics can consume significant resources and affect critical workloads. This article demonstrates how transactional replication, STATS_STREAM, and Service Broker can be combined to move statistics generation to a Subscriber while preserving accurate optimizer statistics on the Publisher.
Inspired by Erin Stellato’s „Friday Feedback“ on LinkedIn, where she’s asking: „If you could offload statistics updates to a secondary replica, would you?“ I was mentioning our work for a customer with a replication-centric approach. Instead of generating expensive statistics directly on the production server, statistics are collected and refreshed on a subscriber. The role of the subscriber is for STATS UPDATE(S) only!
General note
Allow me to make one observation. This blog post presents our approach in a highly simplified form and is intended to illustrate the architecture that enabled us to achieve our goal. We employ both an automated approach and a configurable approach for updating statistics.
- All statistics with the NORECOMPUTE option are taken into account.
- Other statistics can/will be configured in a configuration table
In a configuration table, we can define parameters for each statistics object, specifying how the statistics should be built …
- Sample rate
- Partitioning
- Exclusion times
- …
The scripts are provided solely for the purpose of understanding the concepts. Certain information (endpoints, security, certificates) is not covered in this post! This post is intended for experts who are familiar with both transactional replication and Service Broker. Further information regarding the technology used can be found on the official Microsoft websites.
Our planned architecture
Our configuration consists of a SQL Server that serves as a subscriber in a transactional replication setup. On this subscriber, statistics objects are monitored and updated. For the exchange of information, we opted for a solution using Service Broker, as we were unable to implement a „linked server“ configuration due to security and regulatory requirements.
The Subscriber serves as the statistics generation platform, where statistics are updated and exported using STATS_STREAM. The generated statistics stream is then transferred back to the Publisher and applied without requiring a full statistics scan on the production system. This approach reduces maintenance overhead on the Publisher while preserving high-quality optimizer metadata for query compilation and execution.
An overview of the individual steps
Subscriber monitors and updates statistics
- UPDATE STATISTICS on the Subscriber
- Read the STATS_STREAM of the updated statistics
- Send STATS_STREAM + ROWCOUNT + PAGECOUNT + OBJECT NAME to Publisher via Service Broker
Publisher adopts statistics
- Receive message
- Extract STATS_STREAM
- UPDATE STATISTICS … WITH STATS_STREAM
- Send ACK back to Subscriber
Sending an ACK is not strictly necessary. However, we implemented it for monitoring purposes, in order to determine whether and when an update has failed. The message sent by Service Broker has – in simplified terms – the following structure:
<StatisticUpdate>
<schema_name>dbo</schema_name>
<table_name>customers</table_name>
<stats_name>nix_customers_c_nationkey</stats_name>
<rows>15000000</rows>
<data_pages>95000</data_pages>
<stats_stream>0xAQAAABAA.....</stats_stream>
</StatisticUpdate>Architecture for SQL Server Statistics Maintenance
We have set up transactional replication between the Publisher and the Subscriber on a continuous basis. The restrictions associated with Transactional Replication prevent tables that lack a Primary Key from benefiting from this solution. Tables without a primary key cannot be used in transactional replication!
Configuration of Subscriber
First, we set up an SQL Agent job that monitors statistics for the tables being tracked. The following example checks whether the threshold of 10,000 changes has been exceeded.
SELECT s.object_id
, s.stats_id
, OBJECT_SCHEMA_NAME(s.object_id) AS SchemaName
, OBJECT_NAME(s.object_id) AS TableName
, s.name AS StatisticsName
, p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties
(
s.object_id
,s.stats_id
) AS p
/*
This is only an example for threshold
Basically we have the threshold(s) in a configuration table!
*/
WHERE p.modification_counter > 10000;
Once the statistics to be updated have been identified, they can be updated. As soon as a statistic has been updated, its binary data is read using STATS_STREAM and can then be transmitted to the publisher via Service Broker.
/*
Options are coming from the configuration table
- SAMPLE RATE
- RESAMPLE
- FULLSCAN
...
*/
UPDATE STATISTICS dbo.customer (nix_customers_c_nationkey) WITH <Options>;
GO
/* Create a temporary table object for the storage of the stats data */
CREATE TABLE #StatsStream
(
stats_stream VARBINARY(max),
Rows BIGINT,
data_pages BIGINT
);
GO
/* Use DBCC SHOW_STATISTICS to extrakt the binary data of the stats object
INSERT #StatsStream
(stats_stream, rows, data_pages)
EXEC('DBCC SHOW_STATISTICS(''dbo.customers'', ''nix_customers_c_nationkey'') WITH STATS_STREAM');
GO
/*
Now we can generate the XML for the message for the service broker!
*/
DECLARE @schema_name AS NVARCHAR(127) = N'dbo';
DECLARE @table_name AS NVARCHAR(127) = N'customers';
DECLARE @stats_name AS NVARCHAR(127) = N'nix_customers_c_nationkey';
SELECT @schema_name AS schema_name,
@table_name AS table_name,
@stats_name AS stats_name,
rows,
data_pages,
stats_stream
FROM #StatsStream
FOR XML PATH('StatisticUpdate'),TYPE;
GO
Configuration of Service Broker
USE master;
GO
/*
Service Broker must be enabled for the database on
- Publisher
- Subscriber
*/
ALTER DATABASE <database_name> SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO
/* Configuration of message type on Publisher & Subscriber */
USE <database_name>
GO
CREATE MESSAGE TYPE [//Stats/UpdateRequest] VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//Stats/UpdateAck] VALIDATION = WELL_FORMED_XML;
GO
/* Creation of the contract (both servers) */
CREATE CONTRACT [//Stats/Contract]
(
[//Stats/UpdateRequest]SENT BY INITIATOR,
[//Stats/UpdateAck] SENT BY TARGET
);
GO
/* Creation of the Queue and Service on Subscriber */
USE <database_name>;
GO
CREATE QUEUE dbo.StatsSendQueue;
CREATE SERVICE [//Stats/SubscriberService] ON QUEUE dbo.StatsSendQueue ([//Stats/Contract]);
GO
/* Creation of the Queue and Service on Publisher */
USE <database_name;
GO
CREATE QUEUE dbo.StatsReceiveQueue;
CREATE SERVICE [//Stats/PublisherService] ON QUEUE dbo.StatsReceiveQueue ([//Stats/Contract]);
GO
/* Configuration of transmission route on Subscriber and Publisher */
CREATE ROUTE PublisherRoute /* from subscriber to publisher */
WITH
(
SERVICE_NAME = '//Stats/PublisherService'
, ADDRESS = 'TCP://publisher_server:4026'
);
GO
CREATE ROUTE SubscriberRoute /* from publisher to subscriber */
WITH
(
SERVICE_NAME = '//Stats/SubscriberService'
, ADDRESS = 'TCP://subsriber_server:4026'
);
GO
Note About the Sample Implementation
By combining transactional replication, STATS_STREAM, and Service Broker, SQL Server statistics maintenance can be offloaded from the production workload while still providing accurate optimizer statistics on the Publisher.
When implementing this approach in a real-world environment, additional considerations such as error handling, transaction management, message retry logic, monitoring of Service Broker conversations, logging, security configuration, permissions, and proper cleanup of conversation endpoints should be addressed. Furthermore, any dynamic SQL should be implemented with appropriate safeguards, and special scenarios such as renamed or dropped objects, concurrent processing, and large STATS_STREAM payloads should be taken into account.
This scenario can only work if the Meta Data of the object on the Publisher is exactly the same as on the Subscriber. Furthermore it is important that INDEX CREATE Options (e.g. DATA_COMPRESSION) must although exactly the same in both databases!
Treat the implementation shown here as a starting point and architectural guideline rather than a complete solution. As with any production workload, thorough testing, validation, and adaptation to your specific requirements are essential before deployment.
Thank you for Reading!
