AlwaysOn synchronize performance can affect memory?

Published May 16 2022 07:48 AM 1,034 Views
Microsoft

In an AlwaysOn environment, if you found your memory is slowly increasing or out of memory, you can use below way to check if it's caused by slow synchronization.

 

Firstly, check the errorlog or use DBCC MEMORYSTATUS to dump the memory usage, and see if the allocation mainly stayed on OBJECTSTORE_SERVICE_BROKER, for example:

 

OBJECTSTORE_SERVICE_BROKER (node 0) KB
---------------------------------------- ----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 55028152   --near  52GB

 

Then we can use a dmv to check the detailed usage under this object store, identify if it is used on the part named with 'HADR...'

select * from sys.dm_os_memory_clerks where type='OBJECTSTORE_SERVICE_BROKER'

 

YueJ_7_0-1652710995013.png

 

And in my last case, the 52GB memory are all used on the 'HADR Log Block Msg Pool', and this pool size is related with the log_send_queue. Before a HADR Log Block is successfully sent to secondary replica, the handle is not released in primary replica, and its allocated memory is not released too. When primary replica cannot send HADR log block messages to secondary while keep receiving control messages from secondary, primary replica thinks its connection with the secondary replica is still alive and continues generating new HADR Log Block messages. It will end up with more and more memory consumption.

 

So I use the AlwaysOn dashboard to check the log send queue size on secondary, it's indeed very large but I even didn't notice that, it did not give me any warning or error message.

 

For the log queue size large issue, we can refer to below blogs to use AG latency tool to do further troubleshooting:

https://techcommunity.microsoft.com/t5/sql-server-support-blog/troubleshooting-data-movement-latency... 

 

And in my case, it's easy, I checked the errorlog on secondary directly and found lots of below message

2021-11-20 05:50:12.44 spid1s      There have been 1284352 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file E:\TransactionLog\WSS_ContentLog.ldf.

2021-11-20 05:50:15.74 spid338s    There have been 1284608 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file E:\TransactionLog\WSS_Content2020_Log.ldf.

 

This is a known issue we already have bunch of docs and KBs explained it:

https://techcommunity.microsoft.com/t5/running-sap-applications-on-the/message-misaligned-log-ios-wh... 

 

So after I enabled the trace flag 1800, both log_send_queue size and the memory issue resolved.

 

 

%3CLINGO-SUB%20id%3D%22lingo-sub-3380913%22%20slang%3D%22en-US%22%3EAlwaysOn%20synchronize%20performance%20can%20affect%20memory%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3380913%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20an%20AlwaysOn%20environment%2C%20if%20you%20found%20your%20memory%20is%20slowly%20increasing%20or%20out%20of%20memory%2C%20you%20can%20use%20below%20way%20to%20check%20if%20it's%20caused%20by%20slow%20synchronization.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirstly%2C%20check%20the%20errorlog%20or%20use%20DBCC%20MEMORYSTATUS%20to%20dump%20the%20memory%20usage%2C%20and%20see%20if%20the%20allocation%20mainly%20stayed%20on%20OBJECTSTORE_SERVICE_BROKER%2C%20for%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E%20OBJECTSTORE_SERVICE_BROKER%20(node%200)%20KB%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3E----------------------------------------%20----------%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3EVM%20Reserved%200%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3EVM%20Committed%200%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3ELocked%20Pages%20Allocated%200%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3ESM%20Reserved%200%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3ESM%20Committed%200%3C%2FEM%3E%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20color%3D%22%230000FF%22%3E%3CEM%3EPages%20Allocated%2055028152%26nbsp%3B%20%26nbsp%3B--near%26nbsp%3B%2052GB%3C%2FEM%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20we%20can%20use%20a%20dmv%20to%20check%20the%20detailed%20usage%20under%20this%20object%20store%2C%20identify%20if%20it%20is%20used%20on%20the%20part%20named%20with%20'HADR...'%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%230000FF%22%3Eselect%20*%20from%20sys.dm_os_memory_clerks%20where%20type%3D'OBJECTSTORE_SERVICE_BROKER'%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22YueJ_7_0-1652710995013.png%22%20style%3D%22width%3A%20678px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371903iE017E3C3E24BE487%2Fimage-dimensions%2F678x312%3Fv%3Dv2%22%20width%3D%22678%22%20height%3D%22312%22%20role%3D%22button%22%20title%3D%22YueJ_7_0-1652710995013.png%22%20alt%3D%22YueJ_7_0-1652710995013.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20in%20my%20last%20case%2C%20the%2052GB%20memory%20are%20all%20used%20on%20the%20'HADR%20Log%20Block%20Msg%20Pool'%2C%20and%20this%20pool%20size%20is%20related%20with%20the%20log_send_queue.%20Before%20a%20HADR%20Log%20Block%20is%20successfully%20s%3CSPAN%3Eent%20to%20secondary%20replica%2C%20the%20handle%20is%20not%20released%20in%20primary%20replica%2C%20and%20its%20allocated%20memory%20is%20not%20released%20too.%20When%20primary%20replica%20cannot%20send%20HADR%20log%20block%20messages%20to%20secondary%20while%20keep%20receiving%20control%20messages%20from%20secondary%2C%20primary%20replica%20thinks%20its%20connection%20with%20the%20secondary%20replica%20is%20still%20alive%20and%20continues%20generating%20new%20HADR%20Log%20Block%20messages.%20It%20will%20end%20up%20with%20more%20and%20more%20memory%20consumption.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ESo%20I%20use%20the%20AlwaysOn%20dashboard%20to%20check%20the%20log%20send%20queue%20size%20on%20secondary%2C%20it's%20indeed%20very%20large%20but%20I%20even%20didn't%20notice%20that%2C%20it%20did%20not%20give%20me%20any%20warning%20or%20error%20message.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EFor%20the%20log%20queue%20size%20large%20issue%2C%20we%20can%20refer%20to%20below%20blogs%20to%20use%20AG%20latency%20tool%20to%20do%20further%20troubleshooting%3A%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-support-blog%2Ftroubleshooting-data-movement-latency-between-synchronous-commit%2Fba-p%2F319141%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fsql-server-support-blog%2Ftroubleshooting-data-movement-latency-between-synchronous-commit%2Fba-p%2F319141%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EAnd%20in%20my%20case%2C%20it's%20easy%2C%20I%20checked%20the%20errorlog%20on%20secondary%20directly%20and%20found%20lots%20of%20below%20message%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%230000FF%22%3E2021-11-20%2005%3A50%3A12.44%20spid1s%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20There%20have%20been%201284352%20misaligned%20log%20IOs%20which%20required%20falling%20back%20to%20synchronous%20IO.%26nbsp%3B%20The%20current%20IO%20is%20on%20file%20E%3A%5CTransactionLog%5CWSS_ContentLog.ldf.%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%230000FF%22%3E2021-11-20%2005%3A50%3A15.74%20spid338s%26nbsp%3B%26nbsp%3B%26nbsp%3B%20There%20have%20been%201284608%20misaligned%20log%20IOs%20which%20required%20falling%20back%20to%20synchronous%20IO.%26nbsp%3B%20The%20current%20IO%20is%20on%20file%20E%3A%5CTransactionLog%5CWSS_Content2020_Log.ldf.%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EThis%20is%20a%20known%20issue%20we%20already%20have%20bunch%20of%20docs%20and%20KBs%20explained%20it%3A%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23000000%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Frunning-sap-applications-on-the%2Fmessage-misaligned-log-ios-which-required-falling-back-to%2Fba-p%2F367796%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Frunning-sap-applications-on-the%2Fmessage-misaligned-log-ios-which-required-falling-back-to%2Fba-p%2F367796%3C%2FA%3E%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CFONT%20color%3D%22%23000000%22%3ESo%20after%20I%20enabled%20the%20trace%20flag%201800%2C%20both%20log_send_queue%20size%20and%20the%20memory%20issue%20resolved.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Co-Authors
Version history
Last update:
‎May 16 2022 07:48 AM
Updated by: