4

We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage. The Logs are are their own partition, and the log is set to "auto grow" and is about 70GB in size (data file is about 100GB)

Any thoughts on how to improve I/O performance for the transaction logs? We have a sense that it's affecting performance.

  • Transaction Logs are backedup every 30 minutes
  • I/O on the transaction log partition is 47% during normal activity and when we run one of our jobs it goes to 90%+
  • We have a 8 vCPU cloud server and we have 8 temp db and 8 temp log
  • Attached performance graphsdb performance graph

enter image description here

I should mention that we don't have a DBA, I do have some SQL experience and we have SQL developers.

log usage

DBCC Log (info) shows 456 rows

NEW QUESTION: Could it be that my transaction log at 60GB is TOO LARGE? Would I get less I/O making the trans log smaller, say 20GB-30GB?

dirving
  • 41
  • 3

1 Answers1

1

Thing i would do to make the transactionlog faster.

  1. Check NTFS block size, and reformat drive to 64k
  2. Check VLF's (should warn in EXEC xp_readerrorlog) if you above 1k.. shrink/grow file to fix.
  3. Set sensible log-file growth rates (not 1% or 1MB)
  4. Consider using Delayed Durability where its possible (be mindfull of Data integrity/DR)

The all-knowing K. Tripp has an article on this http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

Kasper Brandenburg
  • 299
  • 1
  • 6