Azure SQL Database – High WRITELOG Wait

azure-sql-databasesql server

My WaitStats analysis of our Azure SQL Database is showing WRITELOG and HADR_SYNC_COMMIT as counter the highest wait. This is a Premium 250 eDTU resource.

Script

SELECT   TOP (10) wait_type,
                  CAST (([wait_time_ms] / 1000.0) AS DECIMAL (16, 2)) AS [WaitS],
                  CAST (100.0 * [wait_time_ms] / SUM([wait_time_ms]) OVER () AS DECIMAL (16, 2)) AS [Percentage]
FROM     sys.dm_db_wait_stats
ORDER BY [Percentage] DESC;

enter image description here

I cannot find much information on how to address this in AZure SQL database.

Any help is appreciated.

Thanks

Best Answer

WRITELOG is waiting on commit for your transaction's log records to be hardened to disk, and HADR_SYNC_COMMIT is waiting on commit for your transaction's log records to be sent over the network to a secondary replica and hardened to disk. So they are very, very similar waits.

The both indicate that your application is commiting a lot of transactions, perhaps too many.
And on premium your log file is on a local flash drive with very low latency, so to suffer lots of WRITELOG waits suggests there's something that needs to be fixed in your application.

If you have any processes that run INSERT, UPDATE, or DELETE of single rows in a tight loop, consider wrapping them in an explicit transaction, so you only have to wait for the transaction log to be flushed at the end.

As always Query Store is your friend, and can show you the waits by query, and you can analyze the waits by session too in sys.dm_exec_session_wait_stats to see what parts of your workload are suffering these waits.

You can get a better sense of how much your clients are waiting by comparing the session elapsed time and cpu time to the wait times. EG

select s.session_id, 
       w.wait_type, 
       w.wait_time_ms, 
       w.signal_wait_time_ms, 
       s.total_elapsed_time, 
       s.cpu_time, 
       w.wait_time_ms/cast(nullif(s.total_elapsed_time,0) as float) wait_percent_of_elapsed
from  sys.dm_exec_sessions s
join sys.dm_exec_session_wait_stats w
  on s.session_id = w.session_id
where w.wait_time_ms > 0
order by wait_percent_of_elapsed desc
Related Question