3

Due to poor disk performance I need to evaluate using the delayed durability setting in SQL Server 2016.

Microsoft states clearly, that this comes with a data loss risk because transactions are committed in memory and not on disk.

This risk is not limited to SQL Server crashing, it's also possible from other causes, for instance if the server is rebooted.

Are there any methods of mitigating the risk by pushing SQL Server to flash data to disk if someone tries to restart the server? I've searched for something like trigger on event but found nothing.

Do you see other risks aside from the ones I mentioned above that I should take into consideration? This is a standalone SQL Server, not involved in a Failover Cluster and not part of an Availability Group.


The poor performance I mentioned is about log latency; the highest waits in this SQL Server instance are CX* following by WriteLog. Right now it takes 3-4ms to write a transaction to disk. It's about the cloud and even after activating T1800 in SQL Server, the system suffers on log latency. I guess it's about virtualization / vSAN firmware and deeper volume settings; we have a vendor service ticket already open, but just in case the vendor can't help I have to investigate all possibilities which might improve performance.

0

1 Answer 1

8

Are there any possibilities to mitigate the risk pushing SQL server to flash data to disk if someone tries to restart a server?

SQL Server uses write-ahead logging to guarantee durability so log buffers containing committed delayed durability transactions must be flushed to disk to mitigate data loss. Stored procedure sys.sp_flush_log may be used to flush log buffers, including delayed durability transactions. Below is the excerpt with guidance from the documentation:

If you choose to use delayed transaction durability because of the performance benefits, but you also want to have a guaranteed limit on the amount of data that is lost on server crash or failover, then execute sys.sp_flush_log on a regular schedule. For example, if you want to make sure you don't lose more than n seconds worth of data, you would execute sp_flush_log every n seconds.

Example script executed via SQL agent job:

USE YourDatabase;
DECLARE @RPO datetime = '00:00:05'; --no more than 5 seconds of data loss
WHILE 1 = 1
BEGIN
    WAITFOR DELAY @RPO;
END;

I don't believe there is a way to automatically run the proc at shutdown and, even if there were, data loss is always possible with delayed durability. The feature is not intended to address "bad log disk performance" but to reduce log latency of when some data loss is acceptable.

As an aside, slow log writes are typically related to infrastructure. Even 3ms to 4ms is tolerable for many applications however I can't say whether that is acceptable for your workload. If it is not, your cloud provider may have options other than delayed durability, which is a heavy hammer to wield.

4
  • Doesn't using sys.sp_flush_log at a regular interval start to defeat the benefit of delayed durability as well?...since it basically invokes the rest of the process of what normally would happen if delayed durability was off? I understand it gives the user more control on when that process is invoked, but I wonder at what point it would diminish the gains of delayed durability, when scheduled to run routinely.
    – J.D.
    Oct 31, 2023 at 11:59
  • 1
    @J.D., my thought is it can defeat the benefits at some point but the tipping point is workload dependent. Factors like large bursts in non-durable transactions are a consideration as well.
    – Dan Guzman
    Oct 31, 2023 at 12:41
  • 2
    sp_flush_log doesn't defeat the purpose because you're not calling it on the users' sessions. The point of delayed durability is that transactions can commit without waiting for a log flush. Log flushing always happens in the background. sp_flush_log just gives you some control over the process. Oct 31, 2023 at 14:40
  • @J.D. - the concerns you have might make a good standalone question
    – Hannah Vernon
    Oct 31, 2023 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.