1

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:

  1. CXPACKET avg:0.0011sec
  2. WRITELOG avg:0.0149sec
  3. PAGEIOLATCH_SH avg: 0.0080sec
  4. IO_COMPLETION avg: 0.0103sec
  5. PREEMPTIVE_OS_PIPEOPS avg: 5.1728sec
  6. ASYNC_IO_COMPLETION avg: 32.6180sec
  7. BACKUPBUFFER avg: 0.0144sec
  8. BACKUPIO avg: 0.0113sec
  9. ASYNC_NETWORK_IO avg: 0.0007sec
  10. 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?

Improve this question
2
  • To troubleshoot VM issues, you need to talk with virtualization and storage admins. Overcommitting resources on a VM environment is a frequent cause for problems that are impossible to solve from the VM side only.
    – vonPryz
    Jun 20, 2016 at 6:45
  • Agree with vonPryz talk to your vm admin and your storage people, reads usually perform better on RAID5 and writes on RAID10. This is also true with tempdb files. There are also different levels (tiers) of storage you can assign to the luns from the SAN side. You would want your tempdb and db log files to be on on a higher tier storage, like a silver, gold, or ssds for better performance.
    – rvsc48
    Jun 20, 2016 at 14:03

1 Answer 1

Reset to default
1

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.