3

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?

Improve this question
2

2 Answers 2

Reset to default
5

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.

Improve this answer
6
  • Thanks but these options are out of my reach
    – Gennady Vanin Геннадий Ванин
    Commented Jan 13, 2014 at 7:22
  • Usually index tuning won't void your support agreement if this is a third party application. If the problem is that you don't have a DBA to do the work, that's what consultants are for (full disclosure, I'm a consultant). If the application is something like SharePoint where you can't do anything with it, I refer you to option #2.
    – mrdenny
    Commented Jan 13, 2014 at 7:26
  • 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.
    – mrdenny
    Commented Jan 13, 2014 at 7:27
  • I cannot tune indexes, they are governed and overriden by application platform (translating app's code into its own internal/gibberish T-SQL) and app code+configuration which are out of my reach even for reading.
    – Gennady Vanin Геннадий Ванин
    Commented Jan 16, 2014 at 8:00
  • I can add files, as the only option available to me from all, and probably I, in hurry, have forgotten to specifically ask from the very beginning if this is an option in context of having SQL Server in a virtual machine as one of many - all of servers in each of regional offices are in VMs on the same, single, physical device with a single/the-same RAID)
    – Gennady Vanin Геннадий Ванин
    Commented Jan 16, 2014 at 8:14
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

Improve this answer
1
  • This is the only option available to me. Hope it was clear from the question. Anyway, it is correct for me, might be different for others
    – Gennady Vanin Геннадий Ванин
    Commented Jan 16, 2014 at 7:45

Your Answer

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

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