I’ve been struggling with this one for a while. We have several VMs running SQL 2017 Enterprise (on Win 2012 R2) that experience high write latency (ranging from 15-45ms) but have great read latency (2-4ms). I’ve looked at all of the real-time stats from the OS (perfmon) to the hypervisor (ESXTOP) back to the storage itself and never see write latency higher than 5ms – the ESXTOP GAVG is a great stat for this. In general, read latency would be higher in most systems due to having to search for random data, whereas writes are typically asynchronous and simply handed off to be written to disk. There are no queueing issues with the SCSI controllers either – the data files, tempdb and log files are separated on different virtual controllers and have separate datastores .. plus, any queuing would also affect read latency. SQL is running on the latest compatibility level and I don’t see any write latency for the tempdb or the write log .. only the data files!
Anyone else have this issue where SQL reports high latency but you can’t actually see any from the OS back to the Storage? Surely the latency must be somewhere within SQL, but I can’t find it. Thanks in advance!
Compute – Cisco UCS, M5 blades
VMware – vSphere 6.0 U2 (upgrading soon)
VM SCSI Controllers – LSI for OS disk, Paravirtual for all others (data files, log, tempdb) with separate datastores
VMDKs – Thick Prov Eager Zero (Data drives)
Storage – All Flash via iSCSI (dedicated storage vlans)
Network – All Cisco Nexus 10GB+
SQL – 2017 CU19