What are the options of reducing tempdb latency?

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?

You've got two basic options.

• Reduce the workload required by the tempdb database

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

StackExchange Database Administrators Q#56655, answer score: 5