Always On可用性组中SQL Server统计信息

SQL Server统计信息简介 (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.

SQL Server统计信息是SQL Server查询性能的重要组成部分。 它们帮助查询优化器使用参与行中的值分布来准备查询计划。 如果我们没有更新的统计信息，则可能会导致资源密集型查询执行计划。 例如，对于具有十亿条记录的大型表，SQL可能选择使用索引扫描而不是索引查找。

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.

在大多数情况下，查询优化器负责创建和更新分布统计信息。 在SQL Server中，我们有三个选项来设置统计信息的行为。 让我们简要概述这些选项。

Right click on a database and open database properties.

右键单击数据库，然后打开数据库属性。

• 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启用自动创建统计信息（在图像中-自动创建统计信息= True）。 它在查询谓词的必需列上创建统计对象。 使用此选项创建的所有统计信息均带有前缀_WA。 一旦创建索引或键（例如主键），SQL Server还将为对象生成SQL Server统计信息
  
  

  Select
  top 10
      object_name(s.object_id) as table_name,
      s.name as stat_name,
      s.is_temporary,
      ds.last_updated,
      ds.modification_counter,
      ds.rows,
      ds.rows_sampled,
         ds.steps
  from sys.stats as s (nolock)
  cross apply sys.dm_db_stats_properties(s.object_id, s.stats_id) as ds
  WHERE s.name like '_WA%'  and object_name(s.object_id) not like '%sys%'
  ORDER BY s.name;
  

  

• Auto Update Statistics: Query optimizer does internal calculations based upon several inserts, update, delete and update the stats automatically when 自动更新统计信息：当Auto Update Statistics = true 自动更新统计信息为true时，查询优化器会根据几次插入，更新，删除和更新统计信息自动进行内部计算
• Auto Update Statistics Asynchronously: If this option is true (Default setting – false), SQL Server checks for the synchronous or asynchronous statistics updates 异步自动更新统计信息：如果此选项为true（默认设置-false），则SQL Server将检查同步或异步统计信息更新

SQL Server Always On可用性组中SQL Server统计信息行为 (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.

SQL Server Always On可用性组将主副本和辅助副本配置为提供高可用性和灾难恢复解决方案。 从SQL Server 2016开始，我们可以在SQL Server中进行只读路由 ，以将读取的工作负载路由到辅助副本。 我们只能在主副本中进行读写事务。 在如何在SQL Server 2016中为可用性组配置只读路由中阅读有关此内容的更多信息。

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.

在SQL可用性组中，SQL Server创建并维护有关主副本数据库的统计信息。 与其他事务日志记录类似，这些来自主副本SQL Server统计信息被发送到辅助副本。 如果我们将辅助副本用于报告目的，并且所有报告都查询从中获取数据，则它可能需要与从主副本中复制的统计信息不同的统计信息。 查询优化器无法在辅助副本上创建统计信息，因为数据库处于只读模式。

Test Environment:

测试环境：

• Two nodes SQL Always On the replica with synchronous mode
  具有同步模式的两个节点SQL Always On副本

Always On可用性组中SQL Server统计信息演示 (Demo for SQL Server Statistics in Always On Availability Groups)

Let’s create a table in Primary replica:

让我们在主副本中创建一个表：

CREATE TABLE tbltest 
  ( 
     id      INT IDENTITY PRIMARY KEY, 
     NAME    NVARCHAR(100), 
     country NVARCHAR(100) 
  )

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

到目前为止，该表不包含任何数据。 使用以下查询来验证主副本和辅助副本上的统计信息：

SELECT Object_name(s.object_id) AS table_name, 
       s.NAME                   AS stat_name, 
       s.is_temporary, 
       ds.last_updated, 
       ds.modification_counter, 
       ds.rows, 
       ds.rows_sampled 
FROM   sys.stats AS s (nolock) 
       CROSS apply sys.Dm_db_stats_properties(s.object_id, s.stats_id) AS ds 
WHERE  Object_name(s.object_id) = 'tbltest'

• 
  
  
  
  
  

• 
  
  
  
  
  

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.

如前所述，SQL Server根据查询优化器的要求自动在主副本上创建统计信息。 让我们在此表中插入一些记录。

DECLARE @Id INT 
 
SET @Id = 1 
 
WHILE @Id <= 10000 
  BEGIN 
      INSERT INTO tbltest 
      VALUES      ('Rajendra - ' + Cast(@Id AS NVARCHAR(20)), 
                   'SQLShack - ' + Cast(@Id AS NVARCHAR(10)) 
                   + 'Article') 
 
      SET @Id = @Id + 1 
  END

Execute a select statement to create statistics:

执行选择语句以创建统计信息：

SELECT remarks 
FROM   [adventureworks2014].[dbo].[tbltest] 
WHERE  remarks BETWEEN 'SQLShack - 5674Article' AND 'SQLShack - 9994Article'

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.

现在执行查询以检查主副本和辅助副本上SQL Server统计信息。 我们可以看到从_WA_sys开始的新名称统计信息。 主副本将这些统计信息复制到辅助副本，您也可以在那末看到相同的统计信息。

Primary replica output:

主副本输出：

Secondary replica output:

辅助副本输出：

SQL Server中可读副本中SQL Server统计信息始终处于打开状态 (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:

让我们看看它如何在可读的辅助副本中工作。 在辅助副本数据库中执行命令：

SELECT name 
FROM   [adventureworks2014].[dbo].[tbltest] 
WHERE  name BETWEEN 'SQLShack - 5674Article' AND 'SQLShack - 9994Article'

Primary replica output

主副本输出

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

现在，重新检查两个副本上的静态数据，然后查看差异。

• 
  
  
  
  
  

• 
  
  
  
  
  

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.

我们可以看到tbltest表的新统计信息_WA_Sys_00000002_405A880E_readonly_database_statistics 。 仔细查看这一行，我们可以看到将这些统计信息的标志is_temporary值设为1。 在辅助副本中，查询优化器在tempdb数据库中创建临时统计信息。 它为这些临时统计信息附加了后缀“ _readonly_database_statistics” 。 我们需要在这里注意，查询优化器在数据库本身的主副本中创建统计信息，并且仅将其复制到辅助数据库。 在这种情况下，它无法在只读辅助数据库中生成统计信息，因此它使用tempdb创建临时统计信息。 查询优化器足够聪明，可以使用tempdb中的这些统计信息并优化工作负载。 SQL Server Always On可以在主副本到辅助副本的方向上工作； 因此，这些临时统计信息无法移动到主副本。 如果由于任何问题而重新启动辅助副本，则会清除这些临时统计信息。 它在tempdb中占用8字节（1页）的存储空间，并且与表的大小无关。

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:

我们可以使用DBCC Show_Statistics命令来检查特定列上SQL Server统计信息。 在以下示例中，我们要检查tbltest表和name列的统计信息：

DBCC show_statistics('tbltest', 'name')

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

我们可以看到此列的临时统计信息及其范围和密度。

Always On可用性组中的过时SQL Server统计信息 (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
  自动_WA统计信息
• 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.

将更多记录插入主副本。 这会使静态副本在主副本上失效。 此时，在辅助副本上执行以下select语句以使用where条件中的主键列检索记录。

SELECT remarks 
FROM   [adventureworks2014].[dbo].[tbltest] 
WHERE  id BETWEEN 2000 AND 3000000 

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.

检查辅助副本上的统计信息，您会看到奇怪的行为。 在以下屏幕截图中，您会注意到它还显示了主键统计信息以及临时统计信息。

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.

它表明SQL Server为此创建了临时统计信息。 由于我们尚未在主副本上执行select语句，因此查询优化器不会更新主副本上的统计信息。

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.

这意味着SQL Server已创建了一个临时SQL Server统计信息。 它不会丢失辅助副本数据库中的永久统计信息。 tempdb中仍然可以使用临时统计信息，但是查询优化器是智能的，并且知道临时统计信息是比永久统计信息更新的统计信息。 它使用它来准备执行计划并获取数据。

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.

如果看一下select语句的执行计划，我们可以看到查询优化器正在使用PK__tbltest__3214EC07B0E08F59统计信息来执行此查询。

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

让我们使用“更新统计信息”命令手动更新主副本上的统计信息：

Update Statistics tbltest

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

它更新有关主副本的统计信息，并将其复制到辅助副本。 检查辅助副本上SQL Server统计信息状态。

We do not have 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.

在辅助副本中，我们仍然具有临时统计信息_readonly_secondary_status。 如您所知，临时统计信息是在tempdb中创建的，因此，如果在辅助副本上重新启动SQL Services，它将创建新的tempdb副本，并且所有临时统计信息都将被删除。

SQL Server Always On可用性组数据库中SQL Server统计信息行为的摘要 (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.

步骤编号	步骤说明	主副本	二级副本
1个	在主数据库上创建带有主键的表	主密钥统计信息在主副本上生成-P1	辅助副本从主副本获取统计信息– P1
2	在“备考”列上有谓词的辅助服务器上执行查询	不适用	创建临时统计信息– T1
3	在主数据库上执行相同的查询（与步骤2相同）	它在主副本上生成永久统计信息S1	它被复制，并且辅助副本在此步骤中具有三个状态– P1，T1和S1
4	在辅助服务器上重新启动SQL Service		清除辅助副本上的临时统计信息T1。
5	在主副本中插入记录，并在辅助副本上执行从步骤2开始的查询。		它更新在辅助副本中获得的临时统计信息T1。
6	在主副本中插入更多记录，并在主副本上为谓词中的另一列执行select语句。	它在主副本上生成永久统计信息S2	辅助副本从主副本获取统计信息– S2
7	在辅助服务器上运行步骤6中的相同select语句		它创建临时统计信息T2。 在此步骤中，辅助副本具有三个状态– P1，S2和T2。 查询优化器在查询中使用T2，因为它具有最新的统计信息。

结论 (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.

在本文中，我们探讨了SQL Server Always On可用性组中SQL Server Statistics的行为。 照顾到性能调整是至关重要的方面。 如果您有任何反馈或问题，请随时将它们留在下面的评论中。

翻译自: https://www.sqlshack.com/sql-server-statistics-in-always-on-availability-groups/