we have recently upgraded from SQL server 2008 to new servers and upgraded to sql server 2016 (SP1 CU3)
new servers hardware hp proliant 580G8 , 4 socket intel xeon E7 processor, total 60 cores, 640GB ram, 8 intel SSD S3700 Raid 10, local
the environment is a busy OLTP system with around 3K batch request/second mostly insert and to a lesser degree updates. as an OLTP system there is mostly 1 insert/transaction so there are alot of transaction commits. there are some reporting queries for system purposes(queue readers, monitoring, etc)
the problem is log flush performance is slow and we get writelog waits. i have tested I/O and it can manage over 100K iops/sec in 4k random write test, sequential writes up to 2500MB/sec
databases compatibility level = 130(sql server 2016)
indirect checkpoint = on,
VLF count = 80-120
processor cpu usage of 3%-8% , average of 5%, maxed under 10% when log backups runs(compressed backups) total signal time/total wait time = 6% - 94% resource wait time
memory PLE is about to tick every second, currently stands for 584000 secs there is very little page swap observable (pages/sec)
this is the real problem i found
"log flushes/sec" = 1000-2000 /sec
"log flush waits time" = 1000-4000 ms/sec
"log flush write time" = 0-1 ms/sec with spikes of 10-15 ms/sec
"AVG disk sec/write" = 0
"current disk queue" = 0 (with spikes of 2 every few minutes)
"Disk idle time" = 90%-100%
so it seems there's something wrong as sql server is unable to use ultra low latency that SSD array provides...
i tried to disable log writer threads from cpu affinity mask , chris adkin wrote in his blog with no success, also used extended events to monitor log flushes, and saw multiple waits happens for a single log flush.
i don't know if this issue exists on previous servers because on that platform I/O was kinda problem and "log flush write time" was alot higher...
so did you ever see this issue in the field and what is the solution from now on thanks