SQL Server Latch buffer wait type experienced by inserts occassionally

Question

Hello

We have got 12TB pure oltp database(SQL 2017), not doing any complex access patterns. Occasionally we notice the insert statement latency spikes to 2 to 3 sec and we could notice it experience IO wait (in Foglight)

If we drill down further, it is the latch buffer wait that is appearing as IO wait and all the EBS volume related metrics are normal. Dont see any big spike in page splits or any other spike in logical/physical reads at that time.

Dont see latch wait on the lit of top waits in the instance

This happens very rarely (once or twice in the month). Since it is critical service, this increased insert latency is causing failures in the service. As far as i checked, i dont find any straight forward workaround to address this issue.

Please guide me what is the way forward

Thanks

Answer 1

Hi @udhayan d ,

 You can start from the MS document Diagnose and resolve latch contention on SQL Server, it will guide you to troubleshoot your issues and help you resolve your issue.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Answer 2

duplicate post due to network.