Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
    
@Aaron-Bertrand, thanks for helpful references –  Gennady Vanin Novosibirsk Jan 16 at 8:10
add comment

2 Answers 2

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.

share|improve this answer
    
Thanks but these options are out of my reach –  Gennady Vanin Novosibirsk Jan 13 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 Jan 13 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 Jan 13 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 Novosibirsk Jan 16 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 Novosibirsk Jan 16 at 8:14
show 1 more comment

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

share|improve this answer
    
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 Novosibirsk Jan 16 at 7:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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