What are the options of reducing tempdb latency?

Question

Took the job of monitoring and tununing a SQL Servers 2012 Enterprise Ed. SP1 (64 bit) on Windows Server 2008 R2 Standard Ed.

All of them are running inside virtual machines. One SQL Server per a regional office on a single RAID10 or RAID5. In other words, I do not have possibility to configure an SQL Server using different physical units (hard drives).

I observe an elevated reading and writing latency on tempdb system database.
What are my options in reducing this latency of tempdb database?

Answer 1

You've got two basic options.

1. Reduce the workload required by the tempdb database
2. Get faster hard drives

As for #1 look for indexes in other databases which are missing. Fixing missing indexes will cause less spill to the tempdb database which will reduce the workload.

If the applications on the server are using temp tables a lot there's nothing you can do but get faster hard drives.

Now all that said, if the latency problem is on the PFS pages in the tempdb database (you'll need to find the specific page IDs which are having latency issues) then the fix is easy, add more tempdb database files.

Answer 2

Option 3.

Reduce allocation contention of temp db. Microsoft recommends setting up multiple tempdb files. General recommendation is 8 files for machines running more than 8 cores. and after than increase by 4 files until you are able to reduce it.

Symptoms: "You observe severe blocking when the SQL Server is experiencing heavy load. When you examine the Dynamic Management Views [sys.dm_exec_request or sys.dm_os_waiting_tasks], you observe that these requests or tasks are waiting for tempdb resources. You will notice that the wait type and wait resource point to LATCH waits on pages in tempdb. These pages might be of the format 2:1:1, 2:1:3, etc."

More details here