SQL Server Latch buffer wait type experienced by inserts occassionally

udhayan d 181 Reputation points
2024-09-12T11:22:31.41+00:00

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

User's image

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,834 questions
{count} votes

2 answers

Sort by: Most helpful
  1. CathyJi-MSFT 22,206 Reputation points Microsoft Vendor
    2024-09-13T02:22:57.9433333+00:00

    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.

    0 comments No comments

  2. CathyJi-MSFT 22,206 Reputation points Microsoft Vendor
    2024-09-13T02:24:10.4633333+00:00

    duplicate post due to network.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.