Shulou(Shulou.com)05/31 Report--

This article mainly shows that "deleting the statistics of a table on the primary alwayson node in the database leads to the synchronization delay of a secondary node", the content is simple and clear, and I hope it can help you solve your doubts. Now let me lead you to study and study the article "deleting the statistics of a table in the primary alwayson node in the database leads to the synchronization delay of a secondary node".

Environment:

Sqlserver2014 alwayson architecture

A secondary node loses statistics.

On the client side, the user queries the table information of a secondary node through the linked server.

Select * from [10.0.0.100]. [hr] .table1

Error report:

The information in the statistics _ WA_Sys_00000091_28B9D11F that queried the table1 table above the secondary node 10.0.0.100 is missing.

How to handle it:

Go to the master node to drop the statistics of this table.

Drop statistics dbo.table1._WA_Sys_00000091_28B9D11F

After the drop is complete, the client queries the secondary node through the linked server for the table table1.

However, it causes another auxiliary node to appear the wait_type of LCK_M_SCH_S in the SQL of querying this table.

This SQL is also querying the table1 table.

In addition, a transaction has not been completed at this secondary node.

There is also a problem with the synchronization of alwayson:

From the point of view of time:

The statistics of the table on the drop master node, the wait_type of the LCK_M_SCH_S in which the SQL occurred, the transaction has not been released, and the alwayson synchronization is not normal.

During this period, the table1 table produces a lot of blocking.

The problem is that the query data of the secondary node with the synchronization problem is inconsistent with the primary node.

In the end, nothing was done, and after 15:00, the synchronization automatically resumed after the transaction was released by itself.

Because there are a lot of deadlocks in the table1 table at this time, it is judged that the occurrence of deadlock releases the transaction and resumes synchronization.

The above is all the content of the article "deleting the statistics of a table in the primary alwayson node in the database leads to the synchronization delay of a secondary node". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.