5

I'm noticing this wait stat on my Azure SQL database, and I've never seen it before. I've tried Googling this wait stat but haven't been able to come up with any good information on what this is, although I saw a couple mentions of it possibly being related to exceeding the Azure performance tier set for the database.

Randy Minder
  • 2,002
  • 4
  • 21
  • 39

2 Answers2

9

The different service tiers of Azure SQL Database are limited by DTUs. Microsoft guarantees a minimum level of resources, which they compute as DTUs. The documentation provides the definition for a DTU as (emphasis mine):

This amount of resources is calculated as a number of Database Transaction Units or DTUs, and is a blended measure of CPU, memory, I/O (data and transaction log I/O). The ratio amongst these resources was originally determined by an OLTP benchmark workload designed to be typical of real-world OLTP workloads.

If you are seeing LOG_RATE_GOVERNOR waits, then you are bumping into the limits imposed on transaction log I/O by your performance tier.

If this wait is impacting performance negatively, you would need to either increase your service tier, or update your code to perform fewer writes. If you have one process that is IO intensive, you could throttle it yourself so that you leave transaction log IOs available for other processes before hitting the limit imposed by your SQL DB service tier.

I wrote a blog post where I used the DTU Calculator to estimate how each of these performance metrics combine to affect the DTU calculation. You may find that helpful in understanding how different types of load are "blended" into the DTU limits.

AMtwo
  • 16,141
  • 1
  • 32
  • 61
  • If I read your response correctly, the DTU limit imposed by the performance tier I've selected only applies to writes and not reads? So, if I had a database that only had reads performed against it, no matter what my service tier, I would never encounter any throttling? – Randy Minder Sep 18 '17 at 16:15
  • The DTU limit includes several factors. Writing to the transaction log is one of those limits, and the one you're hitting. There are additional limits on CPU, memory, and database I/O. So if your application was read-only, you would eventually hit all of those other limits on CPU, memory and the "O" part of Database I/O – AMtwo Sep 18 '17 at 17:12
2

Microsoft documentation states (here) LOG_RATE_GOVERNOR occurs when DB is waiting for quota to write to the log. It seems at the current tier processes are crossing the transaction log rate limits.

Try to identify processes and queries involved with those LOG_RATE_GOVERNOR waits. Scaling to premium tiers that use SSD storage may solve the issue.

You can also identify those queries that incur on that wait during execution if you use the mechanisms provided on this article or use Query Store. You may want to reconsider the objective of those UPDATES, DELETES, INSERTS and the whole process they are part of.

Hope this helps.

Alberto Morillo
  • 1,600
  • 7
  • 8
  • "when DB is waiting for quota to write to the log" I read that too and it meant nothing to me. – Randy Minder Sep 18 '17 at 15:52
  • It is all about contention writing to the log. You will see LOG_RATE_GOVERNOR along with WRITELOG wait. Those waits are related to writing to Transaction Log. You won't find WRITELOG with TempDB on SQL Azure, but you may find it on user databases. – Alberto Morillo Sep 18 '17 at 19:49