3

Here's my situation: I have a SQL Server 2012 installation with a relatively small (185GB) SSD volume, and a large (3TB) RAID10 spinning disk volume. I currently have tempdb data and log files on the SSD, as well as the transaction logs for all user databases. One database is very large and does massive batch processing; it collects network switch metrics every 10 minutes and loads it into the database. The database is set to SIMPLE logging.

The app owners recently added a second collector node, causing the # of active transactions to skyrocket. This in turn resulted in the transaction logs filling up the SSD drive, causing the user database and tempdb to be unavailable due to insufficent space.

I added a log file to tempdb and the user database on the RAID10 volume to get both databases available again.

I've read Multiple Transaction Log files and performance impact, and especially Mike Fal's answer to it. But, I'm confused about a few things in Mike Fal's answer.

Once it does, it will attempt to return to the first available VLF in the file. If one is not available, the log file will then grow.

I read the answer as "try to re-use a VLF in the current file, and then try to grow the current file, and if the current file is able to grow, don't switch files".

Is there a way to indicate to SQL Server to prefer the re-use of the log files on the SSD, if available, rather than grow the file on the spinning disks? Ideally I'd like to set the files on the SSD to almost-filling-the-disk, and turn auto-growth off, and then only use the files on the spinning disks as overflow when necessary - and let the files on the spinning disks grow as needed, as I have 2.5TB+ free on that volume.

As I read it though, if I set the files on the spinning disks to auto-grow, I won't ever fall back to the files on the SSD - I have to set a hard cap on the files on the spinning disk volume in order to someday fall back to the files on the SSD. Due to the architectural change, I'm not sure how large the transaction logs will need to grow; the current hardware was sized for one collector node, and they didn't inform me they were adding a second collector node until after the disk filled.

Community
  • 1
dpw
  • 217
  • 1
  • 5
  • 2
    Why don't you just size the log file on the spinny disk to an appropriate size, and get rid of the other file. This way you don't have unpredictable behavior when the log activity may rotate between files, but at the larger size you will stop getting hit by the most expensive part of logging anyway (autogrow events). – Aaron Bertrand May 13 '16 at 20:12
  • 2
    Anyway, no, SQL Server does not have any kind of concept of indicating log file precedence, because you're not supposed to have multiple log files. It is basically only supported so you can get out of jams. – Aaron Bertrand May 13 '16 at 20:14
  • Why dont you just address the root of the problem - why is 2nd collector generating so much t-log ? Is it being run in batches ? Refer to Data Loading Performance Guide to help with some best practices. – Kin Shah May 13 '16 at 21:34
  • @AaronBertrand: yeah, that's what I'll probably do. I hate to give up the performance of the SSD drive though. – dpw May 13 '16 at 21:49
  • @Kin: I don't think it's actually generating more t-log than the first, it's that the t-log was using 75% of the SSD drive already. With twice the number of transactions, there's now more active transactions than the SSD has space for. – dpw May 13 '16 at 21:51
  • Developments in transaction log technology: https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/12/02/transaction-commit-latency-acceleration-using-storage-class-memory-in-windows-server-2016sql-server-2016-sp1/ – wBob Dec 28 '16 at 21:11
  • I've found that log files don't really improve on an SSD anyways. Something about sequential writes being ideal for spinning disks. I would follow @AaronBertrand's advice. Expand the log files so you will get fewer VLFs and less unpredictability where the file is actually writing. – Shooter McGavin May 16 '18 at 19:03
  • @ShooterMcGavin: the performance decrease was very noticeable when I had to move the log to spinning disk. I've since moved on to a different job so I can't give you exact metrics though. – dpw May 17 '18 at 20:03
  • @dpw, I don't have the article available but have recently read where there were some tests done to compare the performance of HDD vs SSD specifically with SQL Server. What was found was that the data files specifically benefit the greatest from SSD because of the random read/writes. While there was some minimal improvement on logs, it was basically insignificant. In your particular environment, hardware, replication, etc, who knows, that might be different. Found another similar article. https://www.mssqltips.com/sqlservertip/2272/ssd-and-sql-server-sqlio-performance/ – Shooter McGavin May 17 '18 at 20:16

0 Answers0