Lesson Learned #218:Hands-On-Labs: What do I need to do in case of high LOG_RATE_GOVERNOR wait time

We used to have cases where our customers reported a performance issue that is causing delay in their business. Working on this issue we saw that the LOG_RATE_GOVERNOR was the most wait time. In this video below we going to provide us some insights about it,.

Why this issue happened?

• Transaction log rate governance
• Limit high ingestion rates for workloads
• Depends on Service/Database Tier.
• More Information: Resource management in Azure SQL Database - Azure SQL Database | Microsoft Docs

How?

• Azure Portal
• Select * from sys.dm_db_resource_stats

select top 5 * from sys.dm_db_resource_stats

• Select * from sys.dm_exec_requests in combinations with other DMVs.

SELECT
req.session_id
, req.start_time
, cpu_time 'cpu_time_ms'
, status
, wait_time
, last_wait_type
, total_elapsed_time
, transaction_id
, wait_resource
, task_address
, object_name(st.objectid,st.dbid) 'ObjectName'
, substring
(REPLACE
(REPLACE
(SUBSTRING
(ST.text
, (req.statement_start_offset/2) + 1
, (
(CASE statement_end_offset
WHEN -1
THEN DATALENGTH(ST.text)
ELSE req.statement_end_offset
END
- req.statement_start_offset)/2) + 1)
, CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text,*
FROM sys.dm_exec_requests AS req
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
where session_id <> @@SPID

Best Practices

• Use Business Critical, Premium or Hyperscale if possible.
• Use smaller batches.
• Review if all indexes are needed
• Avoid huge amount like text.
• Implement a more robust retry logic execution.
• Move Historical data to another DB.