4

I have problem with Log Flush Wait Time - as of now, they are averaging at 30000ms (min 0 max 35000); database itself is performing poorly, transaction logs are also growing beyond reasonable description. Log files have to be shrunk to keep the size at bear, not filling up whole disk drive with log files. I checked DB waits and they are in general LCK_M_S, also OLEDB. Database itself lives on SQL 2016 Always-on.

I still believe that there may be some SQL server issues with settings - I can also see that there is a lot of SQL Memory pressure (30000 free stalls, ~900000000 page reads, 30000 page life exp.) - server has 32GB RAM, hosting ~80 databases.

I know the application is the probably main reason for this but still, any suggestions on proceedings?

closed as off-topic by LowlyDBA, Kin Shah, RDFozz, Mr.Brownstone, hot2use Nov 8 '17 at 8:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – LowlyDBA, Kin Shah, RDFozz, Mr.Brownstone, hot2use
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    How often are you taking log backups? – Erik Darling Mar 17 '17 at 11:52
  • 4 times - two full and two transaction log – dm12 Mar 17 '17 at 12:26
  • 3
    This is perhaps a good time to review that strategy, and start taking log backups frequently enough to meet your company's RPO (which I assume is fairly stringent, since you're using AGs). Start reading here: brentozar.com/archive/2011/12/letters-that-get-dbas-fired – Erik Darling Mar 17 '17 at 12:31
  • 4
    You might be better off with TLog bacups every 1 hour or even every 15 minutes. However, if you have transactions that never commit, then you might have to sort out that first. – hot2use Mar 17 '17 at 12:36
  • 1
    To elaborate slightly - in the FULL recovery model, SQL has to keep completed transactions in the log until either a FULL backup is done, or until a transaction log backup is done. It sounds like you're taking 4 backups a day that allow SQL to release old completed transactions and reuse log space. If you take transaction log backups more frequently, the log will be able to clear the old transactions and reuse space sooner, and won't need to grow as large. Currently, shrinking the log files just means that SQL has to grow them back out (which won't help performance). – RDFozz Mar 17 '17 at 16:36
2

Usual supect, in this case: The IO subsystem is slow. Are you using SSDs?

DMVs are your friends.

Identity & Isolate Issues

Log Flush Wait Time

  • This is an indication of Underlying IO bottleneck
  • What is the write latency?
  • Find out using sys.dm_io_virtual_file_stats & sys.dm_io_pending_io_requests

Size log files appropriately

  • Your application seems to be a very high transactional load type.
  • If log files are frequenting to a certain size limit, it means it needs such size.
  • I would size my log files to be 750 MB if the log file is frequently reaching that size at its peak.

Transactional log backups

  • Maybe you just need a more frequent log backup.
  • Test & decide.

As far as memory, a 30000 PLE looks good to me on a 32 GB RAM server. Assuming this is SQL's memory, not the server's.

Resources:

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