We had an AlwaysOn Async replica we had some problems so we deleted the availability group After that the database and CPU seem to work abnormally It's very slow and we have waittype = LATCH_EX , ACCESS_METHODS_DATASET_PARENT We didn't have such a problem till today Can anybody help ?????
Given the wait types of
ACCESS_METHODS_DATASET_PARENT I highly doubt AlwaysOn was the culprit.
These two latch types may go hand in hand. You'll want to investigate where the
latch_ex waits are actually happening - sys.dm_os_latch_stats may help here where the latch_class is not 'buffer'.
Given that removing AlwaysOn did not help with the situation, I highly doubt AlwaysOn was the problem. However, AlwaysOn may have contributed to some wait time though how much is impossible to say, though I doubt it would be noticeable against your current top waits.
Paul has some information on these: https://www.sqlskills.com/help/latches/access_methods_dataset_parent/ https://www.sqlskills.com/help/waits/latch_ex/
Additionally this whitepaper on latch contention may be helpful: http://download.microsoft.com/download/B/9/E/B9EDF2CD-1DBF-4954-B81E-82522880A2DC/SQLServerLatchContention.pdf