SQL Server options that influence log buffer flushing or commits-per-second performance?

Question

I am investigating two SQL Server 2014 instances, both running identical software, both running identical hardware (VMs), both loaded with the same test database, but one with hugely greater performance on a microbenchmark.

The benchmark essentially runs the following in a loop:

BEGIN TRANSACTION
    INSERT INTO [tablename] ([data])
    VALUES (CONVERT(NVARCHAR(2000), NEWID()))
COMMIT

One server can achieve approximately 200 transactions per second. The other, approximately 2000 transactions per second.

Since this transaction is doing so little, I hypothesized that the bottleneck could be disk I/O in flushing the log buffer. I thought the DELAYED_DURABILITY option was sure to explain the difference. But alas, it was set to Disabled on both servers. An audit of other options has turned up no other apparent configuration differences.

So my questions are:

1. Is there any other way that transaction durability could be delayed when that option is set to Disabled, e.g. a master override config file somewhere?
2. Is there any other way that log buffer flushing could be influenced?
3. Are there any other settings I should look into that might explain the wide variance in performance that I am seeing?

N.B. I'm aware that this is a microbenchmark and is not representative of the application workflow - however, real-world testing has shown a performance difference too, and this transaction loop seems to be the minimum reproducible example that shows a difference.

Additional details

Looking at the wait stats, WRITELOG is top of the list.

Answer 1

both running identical hardware (VMs)

If the performance of this minimal test is that drastically different, and you have thoroughly confirmed that the software (and instance / database level settings - for example the checkpoint-related settings like indirect checkpoint, recovery interval, target recovery time, etc might affect a benchmark like this one) is the same, then the statement about the hardware being the same is probably not exactly right.

You mentioned WRITELOG is your highest wait. With this workload, WRITELOG will almost inevitably be the highest wait, since that's all you're doing. Is the difference in WRITELOG waits between the two servers large enough to account for the 10x difference in transactions per second?

If that wait is significantly higher on the slow server, then it's possible that, despite the same hardware specs being specified at the VM level, the "path" to the I/O system is slower on the second VM. Or the underlying storage is slower.

• Check out the sys.dm_io_virtual_file_stats DMV to see if the slow server is experiencing slower writes or more frequent / longer stalls than the fast server.
• Check the SQL Server error log to see if there are any "SQL Server has encountered X occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [M:\SomeDataFile.mdf] in database [SomeDatabase] (5)." error messages.

If you're seeing slowness reported in those places, you'll need to work with the VM administrator - to see if there is a difference in the underlying storage, or the access to the storage (if it's going out to a SAN).

Answer 2

Your VM wares are identical but are they on the same ESX, same storage etc. Start checking if your disk IO is identical on both VM's. You can do this, like already mentioned in previous answers, with the DISKSPD tool. Or even simpler with the CrystalDiskmark tool. If both your SQL Server instances and databases are configured exactly the same, and your OS & machine are identical than the cause is almost certain on a lower level. Check also if the VM's do have exactly the same configuration because it is not the first time that they seem equal, but on the ESX they aren't.

Answer 3

Since the slow one is waiting for WRITELOG, you have a clear indication that it is I/O throttled writing to the ldf file. However did you check wait stats on the other instance? You might very well have the same wait stats ratio on that instance.

I would try to see where the difference comes from. Can you skip the NEWID() and still see a difference? For instance do a RAND, or even the very same value. Can you skip the CONVERT and still see a difference? Etc.

Anyhow, I'd start by taking SQL Server out of the picture. Install DISKSPD.EXE on both machine and let is run for a while. Make sure you configure DISKSPD so that it reasonably matches your load test.

Based on this outcome, you know where to look next.