ms/write on tempdb data files are dead slow I don't know why writes on my tempdb data files are extremely slow. ms/write on each of the tempdb data files reaches to more than 800 ms!!! that's even after I moved the 8 equally sized (7GB) tempdb data files to separate drive on the HP StoreVirtual 4530 40TB SAS Storage SAN we have. The SQL Server we have is Standard 2008R2. hosted on 8 cores 80GB memory virtual machine. I would like to note that the top ten waits I have on that instance are:


CXPACKET avg:0.0011sec
WRITELOG avg:0.0149sec
PAGEIOLATCH_SH avg: 0.0080sec
IO_COMPLETION avg: 0.0103sec
PREEMPTIVE_OS_PIPEOPS avg: 5.1728sec
ASYNC_IO_COMPLETION avg: 32.6180sec
BACKUPBUFFER avg: 0.0144sec
BACKUPIO avg: 0.0113sec
ASYNC_NETWORK_IO avg: 0.0007sec
PAGEIOLATCH_EX avg: 0.0291sec


Most of the other databases data files average around 30ms per write (some of them reaches more than 100ms.

What to do? what to search?
ASYNC_IO_COMPLETION waits occur when a task is waiting for I/Os to finish.  

PREEMPTIVE_OS_PIPEOPS occurs when SQL Server runs code in Windows.  For example, if you run xp_cmdshell, SQL Server spawns a command that is controlled by the Operating System.  While the command is running SQL Server shows PREEMPTIVE_OS_PIPEOPS as the wait since it doesn't know what the OS is actually doing.

The combination of these two waits seems like the operating system is busy.  You mentioned that the server is a virtual machine - if SQL Server itself is not super busy, it's quite likely the physical host server may be very busy servicing other VMs.  Ensure your SQL Server VM has a memory and CPU reservation appropriate for its needs.  Also, if running on VMware, ensure the VM uses the PVSCSI disk controller for the drives where SQL Server data and logs reside.