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:
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 makes it seem 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.