How Readable Secondary Replicas Build Their Own Statistics

A report runs on the secondary and gets a plan you never saw on the primary. Readable secondary replicas can build temporary statistics to estimate that read-only workload. Those statistics help the query, but they do not become part of the database that replicates to the next server.

Why Readable Secondary Replicas Need Their Own Clues

An availability group sends database changes from the primary to a secondary. A readable secondary accepts queries but cannot make ordinary changes to that database. The optimizer still needs statistics for columns used by reports. When a needed statistic is missing or stale, SQL Server can create a temporary one in tempdb. It belongs to the secondary instance and serves its local read workload.

I start with the query and its plan, not with an assumption that replicas must choose identical plans. Hardware, cache, workload, and local temporary statistics differ. A report can filter a column the primary application never uses. The primary has no reason to create a statistic for that column until something asks for it. Why would the secondary have the same estimates without its own evidence?

Find Temporary Objects on Readable Secondary Replicas

Connect to the readable secondary and run the following query in the reporting database. sys.stats has an is_temporary flag, and the engine adds a reserved suffix to the temporary name. Use the flag as the main test. The object and column joins reveal which table and predicate deserve a closer look. Metadata visibility still depends on the login's permissions.

SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
       o.name AS table_name, s.name AS statistics_name,
       c.name AS column_name, s.is_temporary
FROM sys.stats AS s
JOIN sys.objects AS o ON o.object_id = s.object_id
JOIN sys.stats_columns AS sc
  ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id
JOIN sys.columns AS c
  ON c.object_id = sc.object_id AND c.column_id = sc.column_id
WHERE s.is_temporary = 1 AND o.type = 'U'
ORDER BY schema_name, table_name, statistics_name;

Do not use the suffix as a naming pattern for user-created statistics. It is reserved for SQL Server. A zero-row result has several possible meanings. The query did not need a new statistic, or one already exists. Automatic creation can be disabled, or the local workload has not run since restart. Check the reporting query and the plan before trying to manufacture an object just to populate this list.

Confirm the Local Replica Role

A connection string with read intent does not prove where the query landed. Check the local availability group state on the session that ran the report. This query reports local database replica rows and their role. Permissions to the DMV depend on SQL Server version and server grants, so an empty result can mean the login cannot see the state. It is also empty when the database is not in an availability group at all.

SELECT DB_NAME(database_id) AS database_name,
       is_primary_replica, synchronization_state_desc
FROM sys.dm_hadr_database_replica_states
WHERE is_local = 1
ORDER BY database_name;

I pair this check with the server name and database name captured by the report connection. A plan comparison is meaningless if both copies came from the same replica by accident. Read routing, failover, and connection pooling can move a session. Include the actual query text and parameter values in the comparison. A different parameter is a much simpler explanation than a mystery in statistics.

Understand What a Restart Removes

Temporary statistics live in tempdb. A SQL Server service restart clears tempdb, so the secondary must recreate those objects as queries need them. A failover changes which instance serves the report and can change the available local statistics. The first execution after a restart can spend time collecting information or compile differently from later executions. That does not mean the database lost permanent statistics; those travel with it.

Do not promise that a temporary statistic will be present during the next incident. Save the query and the plan evidence instead. If the same reporting predicate matters every day, a permanent statistic created on the primary can be the more stable choice. The primary writes it into the database, and normal availability group synchronization carries it to secondaries. Plan the creation and maintenance cost on the primary first.

Decide When to Create One on the Primary

Look for repeated reporting queries with poor estimates on the same columns. Check whether an existing index statistic already covers the useful leading column. Then test a targeted CREATE STATISTICS statement on a safe copy of the primary database. The secondary cannot host a permanent change to the user database. Deploy the supported statistic through the same review path as other schema changes, then compare the report plans again.

One statistic is not a cure for a query with a conversion around its filter or a parameter that changes selectivity by orders of magnitude. Fix the predicate shape and compare actual with estimated rows. A multi-column statistic can help correlated filters, but its histogram covers the leading column. Choose it for evidence, not because two column names looked lonely in a plan.

What Automatic Creation Costs Readable Secondary Replicas

Creating or updating a temporary statistic can require a schema modification lock. On a busy readable secondary, that can delay reporting and can interfere with redo work. SQL Server provides database-scoped settings to control automatic temporary statistic creation and updates when the tradeoff is unacceptable. Turning them off without a replacement can worsen estimates. Measure the blocking pattern, identify the query, and test the alternative before changing the setting.

I treat these settings as a last decision, not a first reaction to a wait. A report that queries all history every minute will stay expensive with perfect statistics. Restrict the data range, check indexes, and observe redo lag. The best plan for the report is of little comfort if the secondary falls too far behind to answer the business question.

Keep Primary and Secondary Evidence Together

For one query, save the primary and secondary plans, the exact text, parameter values, replica identity, and the temporary-statistics inventory. Note when a restart or failover occurred. This record lets the next comparison ask whether the environment or the query changed. Without it, two different plans become a guessing game with expensive prizes.

The temporary object is a useful local response to a real read-only constraint. It is also a reminder that an availability group moves database state, not every piece of instance state. Keep report design, permanent statistics, and replica operations in the same conversation when performance matters.

Related reading on this blog: Query Store Feature for Secondary Replicas and AlwaysOn and Propagation of Compatibility Level.

A secondary plan is not a copy of the primary plan, it is a decision made with local evidence.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.