SQL Server Statistics play a vital role in query performance optimization. They help the query optimizer in creating an efficient query plan based on the distribution of values in the participating rows. Outdated or inaccurate statistics can lead to resource-intensive query execution plans, impacting the overall performance of your SQL Server.
In SQL Server, there are three options to set the behavior of statistics:
- Auto Create Statistics: By default, SQL Server enables Auto Create Statistics. It automatically creates statistics objects on the required columns in the query predicate. These statistics have a prefix “_WA”. SQL Server also generates statistics for an object when you create an index or key, such as a primary key.
- Auto Update Statistics: When enabled, the query optimizer internally calculates and updates the statistics based on several inserts, updates, and deletes.
- Auto Update Statistics Asynchronously: This option determines whether SQL Server checks for synchronous or asynchronous statistics updates.
In SQL Server Always On Availability Groups, which provide high availability and disaster recovery solutions, statistics are created and maintained on the primary replica database. These statistics are then sent to the secondary replica along with other transaction log records. However, if you are using the secondary replica for reporting purposes and all the reports fetch data from it, you may require different statistics than those replicated from the primary replica.
When using a readable secondary replica, the query optimizer cannot create statistics on the secondary replica because the database is in read-only mode. Instead, it creates temporary statistics in the tempdb database. These temporary statistics are appended with the suffix “_readonly_database_statistics”. The query optimizer utilizes these temporary statistics to optimize the workload.
It’s important to note that temporary statistics in the secondary replica are not moved to the primary replica. If the secondary replica is restarted, these temporary statistics are flushed out. Temporary statistics take up 8 bytes (1 page) of storage in tempdb and do not depend on the table size.
To check SQL Server Statistics on a particular column, you can use the DBCC SHOW_STATISTICS command. For example:
DBCC SHOW_STATISTICS ('tbltest', 'name')Stale statistics can also be a concern in Always On Availability Groups. If there is no activity on the primary replica, it does not create or update any statistics. However, if you are executing workloads on the secondary replica, it creates and updates statistics as required. This can result in a difference in statistics between the primary and secondary replicas.
In summary, SQL Server Statistics are crucial for query performance optimization in Always On Availability Groups. Understanding their behavior and ensuring they are up-to-date is essential for maintaining optimal performance. If you have any questions or feedback, please leave them in the comments below.