Rajendra Gupta
Execution plan and statistics

SQL Server Statistics in Always On Availability Groups

May 27, 2019 by

Introduction to SQL Server Statistics

SQL Server Statistics are an essential part of query performance in SQL Server. They help the query optimizer to prepare a query plan using the distribution of values in participating rows. If we do not have updated statistics, it might lead to resource intensive query execution plan. For example, for a large table having a billion records, SQL may choose to have an index scan instead of an index seek.

In most cases, query optimizer takes care of creating and updating the distribution statistics. In SQL Server, we have three options to set the behavior of statistics. Let’s take a brief overview of these options.

Right click on a database and open database properties.

SQL Server Statistics

  • Auto Create Statistics: By default, SQL Server enables Auto Create Statistics (In the image – Auto Create Statistics = True). It creates statistics objects on the required column in the query predicate. All statistics created using this option have a prefix _WA. SQL Server also generates SQL Server statistics for an object once you create an index or key such as the primary key

    SQL Server Statistics examples

  • Auto Update Statistics: Query optimizer does internal calculations based upon several inserts, update, delete and update the stats automatically when Auto Update Statistics = true
  • Auto Update Statistics Asynchronously: If this option is true (Default setting – false), SQL Server checks for the synchronous or asynchronous statistics updates

SQL Server Statistics behavior in SQL Server Always On Availability Group

SQL Server Always On Availability Groups configures primary and secondary replica to provide high availability and disaster recovery solution. Starting from SQL Server 2016, we can Read-Only Routing in SQL Server to route the read workload to the secondary replica. We can do read-write transactions in primary replica only. Read more about it in the How to Configure Read-Only Routing for an Availability Group in SQL Server 2016 article.

In SQL Availability groups, SQL Server creates and maintains statistics on primary replica databases. These SQL Server statistics from primary replica are sent to secondary replica similar to other transaction log records. If we are using the secondary replica for the reporting purpose and all the reports query fetching data from it, it might require different statistics from those statistics that are replicated from Primary replica. The query optimizer cannot create statistics on secondary replica because the database is in read-only mode.

Test Environment:

  • Two nodes SQL Always On the replica with synchronous mode

Demo for SQL Server Statistics in Always On Availability Groups

Let’s create a table in Primary replica:

The table does not contain any data as of now. Verify the stats on both primary and secondary replica using the following query:

  • Primary replica SQL Server Statistics output:

    Primary replica SQL Server Statistics output

  • Secondary replica SQL Server Statistics output:

    Secondary replica SQL Server Statistics

As highlighted earlier, SQL Server automatically creates the statistics on primary replica as per the query optimizer requirements. Let’s insert a few records into this table.

Execute a select statement to create statistics:

Now execute the query to check SQL Server Statistics on both the primary and secondary replica. We can see new statistics for the name starting from _WA_sys. Primary replica replicates these statistics to the secondary replica, and you can see the same statistics at that end also.

Primary replica output:

SQL Server Statistics on primary replica

Secondary replica output:

Secondary replica output

SQL Server Statistics in Readable replica in SQL Server Always On

Let’s consider that you are using a secondary replica to serve the read-only workload. As we are not executing these queries on the primary replica, it cannot create new statistics in primary replica and replicates it to the secondary replica.

Let’s see how it works in a readable secondary replica. Execute a command in the secondary replica database:

Primary replica output

Now recheck the statics on both the replicas and see the difference.

  • Primary replica – No change in SQL Server Statistics:

    Readable replica statistics

  • Secondary replica – new SQL Server Statistics (temporary) added:

    Readable replica statistics in secondary database

We can see a new statistics _WA_Sys_00000002_405A880E_readonly_database_statistics for tbltest table. Look this row carefully, and we can see a flag is_temporary value to one for these statistics. In the secondary replica, query optimizer creates temporary statistics in the tempdb database. It appends the suffix ‘_readonly_database_statistics’ for these temporary statistics. We need to note here that query optimizer creates statistics in the database itself in primary replica and it gets replicated to the secondary database only. In this case, it cannot generate statistics in a read-only secondary database, so it uses tempdb to create temporary statistics. The query optimizer is smart enough to use these statistics from the tempdb and optimize the workload. SQL Server always On works in primary to secondary replica direction; therefore, these temporary statistics cannot be moved to the primary replica. If we restart the secondary replica due to any issues, these temporary statistics are flushed out. It takes 8 bytes (1 page) of storage in tempdb, and it does not depend upon the table size.

We can use the DBCC Show_Statistics command to check SQL Server Statistics on a particular column. In the following example, we want to check statistics for tbltest table and name column:

We can see the temporary statistics for this column along with its range and density.

temporary statistics

Stale SQL Server Statistics in Always On Availability Groups

Consider a scenario in which you are not running any queries on the primary replica. It does not create or update any statistics on primary replica due to no activity on the primary replica. At the same time, we are executing workload on the secondary replica, and it creates, updates statistics as per requirements.

At this point, we have three statistics on the secondary replica:

  • Primary key statistics
  • Automatic _WA statistics
  • Temporary statistics

Insert more records into the primary replica. It makes the statics stale on the primary replica. At this point, execute the following select statement on the secondary replica to retrieve records using primary key column in where condition.

Check the statistics on the secondary replica, and you can see strange behaviour. In the following screenshot, you can notice it shows primary key statistics also as temporary statistics.

primary key statistics

It shows that SQL Server has created temporary statistics for this. Since we have not executed the select statement on the primary replica, query optimizer does not update statistics on the primary replica.

It implies that SQL Server has created a temporary SQL Server Statistics. It does not lose the permanent statistics in the secondary replica database. Temporary statistics is still available in tempdb, but query optimizer is intelligent and knows about the temporary statistics is updated statistics than the permanent statistics. It uses it to prepare an execution plan and get the data.

If we look at the execution plan of the select statement, we can see that query optimizer is using PK__tbltest__3214EC07B0E08F59 statistics for executing this query.

Execution plan and statistics

Let’s manually update statistics on the primary replica using the Update Statistics command:

It updates statistics on primary replica and replicates the same to the secondary replica. Check the SQL Server Statistics status on the secondary replica.

We do not have temporary statistics for the primary key.

temporary statistics for the primary key

We still have temporary statistics in the secondary replica as _readonly_secondary_status. As you know now that temporary statistics gets created in the tempdb therefore if you restart SQL Services on the secondary replica, it creates a new copy of tempdb, and all temporary statistics are dropped.

temporary statistics for the primary key

Summary of SQL Server Statistics behaviour in SQL Server Always On Availability Group databases

Let’s summarize actions into the following section.

Step No

Step Description

Primary Replica

Secondary Replica

1

Create a table with a primary key on Primary

Primary key stat generates on the primary replica – P1

Secondary replica gets the statistics from primary replica – P1

2

Execute a query on Secondary with a predicate on the remarks column

NA

Temporary statistics gets created – T1

3

Execute the same query on primary ( same as step 2)

It generates a permanent stats S1 on the primary replica

It gets replicated, and Secondary replica has three stats – P1, T1 and S1 at this step

4

Restart SQL Service on Secondary

It clears temporary statistics T1 on the secondary replica.

5

Insert records in primary replica and execute the query from step 2 on the e secondary replica.

It updates the temporary statistics T1 gets in the secondary replica.

6

Insert more records in primary replica and execute a select statement on the Primary replica for another column in the predicate.

It generates a permanent stats S2 on the primary replica

Secondary replica gets the statistics from primary replica – S2

7

Run the same select statement from step 6 on secondary

It creates temporary statistics T2. The secondary replica has three stats – P1, S2 and T2 at this step. The query optimizer uses T2 in the query because it has the latest stats.

Conclusion

In this article, we explored the behavior of SQL Server Statistics in SQL Server Always On Availability Groups. It is a crucial aspect to take care of performance tuning. If you have any feedback or questions, feel free to leave them in the comments below.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views