Azure SQL Average lock wait time logs?



  • I'm running an Azure SQL database with a webapp connected to it and we are now getting into some situations where requests timeout, namely some delete / insert operations that I'm already pretty sure are related to locks and some requests holding locks that others need to run.

    As a first step, what I wanted to check is if my lock clues are correct. I caught the right moment in the server and saw some locks there but I was wondering if there is any way to see what was the 'average lock wait time' for the last month or something along those lines.

    I read a lot about SQL Server Profiler and XEvents to debug logs but for now I just want to see a metric that I can look and say: OK, the average waiting to get a lock is too big, this is the problem 99% sure.

    I saw sys.dm_db_wait_stats but not sure how to interpret it.

    Is there something I can run or see on azure portal related to this?



  • The average wait time for a specific event is probably going to be low unless you have timeouts frequently because of it. The DMV you have identified will let you work out the average wait times by dividing the time spent waiting on it (wait_time_ms) by the amount of waits (waiting_tasks_count).

    Extended Events will definitely help you identify what exactly is slow https://www.sqlshack.com/using-sql-server-extended-events-to-monitor-query-performance/ and https://www.mssqltips.com/sqlservertip/5752/configuring-extended-events-to-find-locking-issues-in-sql-server/ are good places to start.

    The query would be something like this:

    select (wait_time_ms / waiting_tasks_count) / 1000 AS "avg time in seconds", (max_wait_time_ms) / 1000 AS "maxtime in seconds",
       * from sys.dm_db_wait_stats where wait_type like 'LCK%'
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2