ms/write on tempdb data files are dead slow

by abutmah   Last Updated April 11, 2018 19:06 PM

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?

Answers 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 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.

Max Vernon
Max Vernon
April 11, 2018 18:48 PM

Related Questions

Unable to remove additional tempdb file

Updated February 07, 2018 02:06 AM

TempDB data files don't shrink well on SQL 2008

Updated April 03, 2018 08:06 AM