Azure SQL Average lock wait time logs?

All we need is an easy explanation of the problem, so here it is.

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?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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%'

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply