alwayson高可用组_AlwaysOn可用性组–好奇心使您的工作更轻松–第2部分

alwayson高可用组

In continuation to the previous article, where we talked about what happens when I add a readable secondary and why there are different options to control connections to the AG, it’s time to continue and demystify more curiosities about the beloved Availability Groups!

在上一篇文章的续篇中，我们讨论了当我添加可读的辅助文件时会发生什么，以及为什么有不同的选项来控制与AG的连接，现在该继续并揭开有关心爱的可用性组的更多好奇心了！

And the next curiosity is….

接下来的好奇心是……。

统计如何在可读的辅助数据中工作？ ( How does statistics work in a readable secondary? )

Have you ever thought about that? Maybe if your database is running fine, you aren’t even bother to know if your stats are updated in the primary replica… why burn your brain thinking about the secondary replica?

你有没有想过？ 也许，如果您的数据库运行良好，您甚至都不必担心主副本中的统计信息是否已更新……为什么要动脑子思考次副本？

Well, this is something nice to know, at least to explain to the customers

好吧，这是一个很好的了解，至少可以向客户解释

Statistic is an object containing a histogram of the values distribution in a column. It’s common to have a statistic created for a single column, but there are statistics for multiple columns. Based on those created statistics, the Query Optimizer will create the execution plans, in order to better execute a query request.

统计是一个对象，其中包含列中值分布的直方图。 为单个列创建统计信息是很常见的，但是有多个列的统计信息。 根据创建的统计信息，查询优化器将创建执行计划，以便更好地执行查询请求。

The statistics can be created manually or automatically, if the AUTO_CREATE_STATISTICS is set to ON, in the database properties, or when you create indexes.

如果将AUTO_CREATE_STATISTICS设置为ON，在数据库属性中或在创建索引时，可以手动或自动创建统计信息。

As you may guess, the primary replica is where all the statistics are created, as this is the read / write replica. No matter if the statistic was created automatically or manually.

您可能会猜到，主要副本是创建所有统计信息的地方，因为这是读/写副本。 无论统计信息是自动创建还是手动创建。

As you can see in the picture above, the stats are transferred as other database objects, with the objective of synchronize the primary and all the secondary replicas of an Availability Group. This way the databases should be identical in a point in time. But wait… Identical? Hmm… this might be a problem…

如上图所示，统计信息作为其他数据库对象传输，目的是同步可用性组的主副本和所有辅助副本。 这样，数据库在时间点上应该是相同的。 但是等等...完全一样吗？ 嗯...这可能是个问题...

Why the word “identical” called my attention? Simple… I will answer with a question: Is your workload identical in the primary and secondary replicas?

为什么“相同”一词引起我的注意？ 很简单……我将回答一个问题：您的工作负载在主副本和辅助副本中是否相同？

If your answer is “yes”, probably you are either using the secondary replica as standby, for disaster recovery purposes, or using to balance the load randomly, so the workload in the primary and secondary is the same.

如果您的回答是“是”，则可能您是将辅助副本用作备用副本，以进行灾难恢复，或用于随机平衡负载，因此主副本和辅助副本中的工作负载相同。

If you answer was “no” or “I’m not sure”, you might not be using the statistics that are generated in the primary replica. Queries executed on Primary, may not be similar to the ones executed in the Secondary. But this is not the only concern here… Statistics on Secondary Databases, may be stale at some point, retuning bad execution plans.

如果回答是“否”或“我不确定”，则可能不是在使用主副本中生成的统计信息。 在Primary上执行的查询可能与Secondary上执行的查询不同 。 但这不是这里唯一的问题…… 统计 在辅助数据库上，有时可能会过时 ，重新调整错误的执行计划。

The two described examples are common and are a big problem if you think that the secondary replica is read-only! So, how could I create statistic to support the reports that are being done in the secondary replica? How can I update a stale statistic?

如果您认为辅助副本是只读的，则描述的两个示例很常见，并且是一个大问题！ 因此，如何创建统计信息以支持辅助副本中正在执行的报告？ 如何更新陈旧的统计信息？

Hopefully, Microsoft developers thought about this and they came up with a solution. And this is simpler than you can imagine

希望微软开发人员对此有所考虑，并提出了解决方案。 这比您想像的要简单

When a new statistic is created in the secondary replica, the TempDB will be used to store the object. This means that all the activity done in the secondary replica will be covered with the proper statistics. This also means that when you restart your secondary replica all the temporary statistics will disappear!

在辅助副本中创建新的统计信息时，将使用TempDB存储对象。 这意味着适当的统计信息将覆盖在辅助副本中完成的所有活动。 这也意味着，当您重新启动辅助副本时，所有临时统计信息都将消失！

Those temporary stats are identifiable by their name, with the suffix “readonly_database_statistic”. You can use the sys.stats view in order to check the statistics of specific table (filtering by object id) or the entire database. This view will show the statistic name and this way you can identify the temporary ones. If your objective is identify only the temporary statistics, the same view has a column called “is_temporary” to distinguish the permanent and non-permanent statistics.

这些临时统计信息可以通过名称标识，后缀为“ readonly_database_statistic”。 您可以使用sys.stats视图来检查特定表（按对象ID筛选）或整个数据库的统计信息。 该视图将显示统计名称，这样您就可以识别临时名称。 如果您的目标是仅识别临时统计信息，则同一视图中有一列称为“ is_temporary”以区分永久统计信息和非永久统计信息。

Here is a sample table:

这是一个示例表：

 
  SELECT * FROM sys.stats WHERE OBJECT_ID = object_id('<TABLE_NAME>')
 

Do you remember the first part of this series, where we talked about the need of a readable secondary to convert the transactions to snapshot isolation level? Because of this, the TempDB was being used to store all the row versions, right? Notice that the TempDB is also used in the statistics case! This enforces the importance of the TempDB in an Availability Groups environment. Make sure you follow all the best practices in order to avoid problems!

您还记得本系列的第一部分吗，我们在这里讨论过需要具有可读性的辅助设备将事务转换为快照隔离级别吗？ 因此，TempDB被用于存储所有行版本，对吗？ 注意，在统计情况下也使用TempDB！ 这加强了TempDB在可用性组环境中的重要性。 确保遵循所有最佳做法，以避免出现问题！

一些动手 ( Some hands-on )

If you want to test and see this happening with your own eyes, I have something ready for you. You will need an “AdventureWorks2014” database, which you can find here. Setup a simple Availability Group including this database and execute the code as follow.

如果您想用自己的眼睛进行测试并看到这种情况的发生，我已经为您准备好了一些东西。 您将需要一个“ AdventureWorks2014”数据库，您可以在此处找到。 设置一个包括此数据库的简单可用性组，并按如下所示执行代码。

Execute the following in the primary replica:

在主副本中执行以下操作：

 
USE [AdventureWorks2014] 
 
go 
 
-- Create a test table 
IF (SELECT Object_id('TestTable')) > 0 
  BEGIN 
      DROP TABLE testtable 
  END 
 
go 
 
CREATE TABLE testtable 
  ( 
     id          INT, 
     codemployee INT, 
     iddept      INT 
  ) 
 
go 
 
-- Insert one row 
INSERT INTO testtable 
VALUES      (1, 
             100, 
             2) 
 
-- check the stats 
SELECT * 
FROM   sys.stats 
WHERE  object_id = Object_id('TestTable') 
 
-- query TestTable  
SELECT * 
FROM   testtable 
WHERE  id = -1 
 
-- check the stats again 
SELECT * 
FROM   sys.stats 
WHERE  object_id = Object_id('TestTable') 
 

This code will create a table, insert one row to this table and, finally, execute a select. This select is not returning any results. When you query the sys.stats view, you will notice that a statistic was created to cover the executed query.

此代码将创建一个表，在该表中插入一行，最后执行选择。 此选择不返回任何结果。 查询sys.stats视图时，您会注意到创建了一个统计信息以覆盖执行的查询。

Now run the following code in the secondary replica:

现在，在辅助副本中运行以下代码：

 
-- Check the stats 
SELECT * 
FROM   sys.stats 
WHERE  object_id = Object_id('TestTable') 
 
-- Let's query again, in a different way 
SELECT * 
FROM   testtable 
WHERE  codemployee = 100 
 
-- Check the stats one more time 
SELECT * 
FROM   sys.stats 
WHERE  object_id = Object_id('TestTable') 
 

This code is querying the sys.stats view and showing that both the primary and secondary replicas have the same statistics for that table. After that we run a different query (from the primary replica) and we query again the sys.stats. You will notice that a new temporary statistic was created!

此代码正在查询sys.stats视图，并显示主副本和辅助副本对该表具有相同的统计信息。 之后，我们运行一个不同的查询（来自主副本），然后再次查询sys.stats。 您会注意到创建了一个新的临时统计信息！

I hope you enjoyed and keep following the Availability Curiosities 🙂

我希望您喜欢并继续关注可用性好奇心🙂

Thank you for reading.

感谢您的阅读。

翻译自: https://www.sqlshack.com/alwayson-availability-groups-curiosities-to-make-your-job-easier-part-2/

alwayson高可用组