Edit

Share via

Persisted statistics for readable secondaries

Applies to: Applies to: SQL Server 2025 (17.x) Preview Azure SQL Database SQL database in Microsoft Fabric Preview

Query Store for readable secondaries was introduced in SQL Server 2022 (16.x), and the persisted statistics for readable secondaries feature uses the infrastructure that Query Store has in place for readable secondaries. Query Store for readable secondaries is on by default in SQL Server 2025 (17.x) Preview, whereas in SQL Server 2022 (16.x) it was off and required trace flag 12606 to enable.

Trace flag 12606 isn't required on SQL Server 2025 (17.x) Preview and later versions and if used, result in turning the Query Store for readable secondaries feature off.

Background

On readable secondary replicas, statistics can also be automatically created when the auto create statistics option is enabled but those statistics are temporary and disappear when an instance is restarted. When statistics on a read-only database or read-only snapshot are missing or stale, the Database Engine creates and maintains temporary statistics in tempdb.

When the Database Engine creates temporary statistics, the statistics name is appended with the suffix _readonly_database_statistic to differentiate the temporary statistics from the permanent statistics. The suffix _readonly_database_statistic is reserved for statistics generated by SQL Server. The reason that this approach was taken was to address workloads that execute against readable secondary replicas that might require distinct statistics that don't exist on the primary replica.

Temporary statistics created on secondary replicas remain visible only to the replica that generated them. The primary replica never directly accesses these temporary statistics objects and is only aware of the permanent statistics object after persistence. When temporary statistics are persisted to the primary replica, they become available to all replicas in the availability group through the synchronization mechanism. The persistence mechanism uses the Query Store for readable secondaries infrastructure introduced in SQL Server 2022 (16.x). Statistics information is sent to the primary replica where it's persisted as permanent statistics, then synchronized back to all secondary replicas. This process occurs automatically without requiring manual intervention.

Support catalog views

To support comparing stats creation or update between secondary and primary, and to assist with understanding where statistics were created, three new columns have been added to the sys.stats catalog view:

Column name Data Type Description
replica_role_id tinyint 1 = Primary, 2 = Secondary, 3 = Geo Secondary, 4 = Geo HA Secondary
replica_role_desc nvarchar(60) Primary, Secondary, Geo Secondary, Geo HA Secondary
replica_name sysname Instance name of the replica in the availability group. NULL for the primary replica

These columns track statistics ownership and origin throughout the persistence lifecycle. When a secondary replica creates temporary statistics and they're persisted to the primary, the replica_role_id and replica_name columns identify the originating replica. If these permanent statistics are later updated on the primary replica, ownership transfers to the primary, which is reflected in these columns.

Statistics persistence behavior

When temporary statistics are persisted from a secondary replica to the primary, several important behaviors occur: The temporary statistics on the secondary replica aren't automatically removed after persistence. Queries that originally triggered the creation of these temporary statistics continue to use them until the query undergoes recompilation or the replica is restarted. This means both temporary and permanent versions of the same statistics can coexist temporarily.

The optimizer doesn't consider replica ownership when determining whether to use statistics. It evaluates all available statistics based on column coverage and selectivity estimates. The replica information is maintained primarily for tracking and troubleshooting purposes.

A notable scenario occurs when permanent statistics created from temporary statistics become stale. If significant data modifications occur on the primary affecting columns in those statistics, the permanent statistics might be considered stale. When queries on secondary replicas reference these columns, the secondary updates the statistics based on its view of the data, reflecting the modifications that have been applied through the redo process.

In short, persistence doesn't remove the secondary's ability to refresh stale statistics; it simply adds a mechanism to share statistics across replicas.

Observability

Extended Events

persisted_stats_operation (Operational channel) is raised for enqueued, dequeued, processed, and failed events. This can be useful to monitor if a statistics message can't be persisted on the primary, or if there's interested in watching the message processing facility. The temporary statistics remain in tempdb on the secondary replicas while a background process retries sending the message if there's a communication problem between the primary and secondary replicas.

  • 9131: Feature disabled during SQL startup.
  • 9136: Table or index dropped/modified.
  • 9137: Schema changed since snapshot transaction started; retry.
  • 9139: Stats too large to send to the primary.

The following query can provide some visibility into the statistics on a table, including statistics persisted from secondary replicas:

SELECT sch.[name] AS SchemaName,
       obj.[name] AS TableName,
       s.[name] AS StatsName,
       CASE WHEN s.stats_id >= 2 AND s.auto_created = 1 THEN 'AUTO_STATS'
           WHEN s.stats_id >= 2 AND s.auto_created = 0 THEN 'USER_CREATED_STATS'
           ELSE 'INDEX_STATS'
       END AS type,
       s.is_temporary,
       CASE WHEN s.replica_name IS NULL
                 AND s.replica_role_desc = 'PRIMARY'
                 AND s.stats_id >= 2
                 AND s.auto_created = 1 THEN 'PRIMARY' ELSE s.replica_name
       END AS replica_name,
       s.replica_role_id,
       s.replica_role_desc
FROM sys.schemas AS sch
     INNER JOIN sys.objects AS obj
         ON sch.schema_id = obj.schema_id
     INNER JOIN sys.stats AS s
         ON obj.object_id = s.object_id
WHERE sch.[name] <> 'sys'
ORDER BY sch.[name], obj.[name], s.stats_id;

Considerations

The persisted statistics for readable secondaries feature is enabled by default as long as the auto create statistics option is enabled and the READABLE_SECONDARY_TEMPORARY_STATS_AUTO_CREATE and READABLE_SECONDARY_TEMPORARY_STATS_AUTO_UPDATE database scoped configuration options are enabled, which is the default configuration. There's no database-scoped configuration to toggle the feature on and off.

Related content