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.

Currently we got TEMPDB Data (number of data files:2) and LOG file on same drive.

On monitoring we have found I/O issues on TempDB.

We got an extra drive available - should we move the TempDB Log file to new drive or should we move the second TEMPDb data file on the new drive to help us reduce I/O issue?

share|improve this question
    
I have them all - including the main b log files - right now on a mirrored SSD. Nothing breats the IO budget and throughput of SSDs ;) –  TomTom Oct 20 '11 at 13:46
add comment

migrated from stackoverflow.com Oct 20 '11 at 13:56

This question came from our site for professional and enthusiast programmers.

2 Answers

If you're already certain you have IO contention, start by understanding which files are the cause.

SELECT
    DB_NAME(fs.database_id) AS [Database Name]
  , mf.physical_name
  , io_stall_read_ms
  , num_of_reads
  , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
  , io_stall_write_ms
  , num_of_writes
  , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
  , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
  , num_of_reads + num_of_writes AS [total_io]
  , CAST((io_stall_read_ms + io_stall_write_ms) / (1.0 + num_of_reads
    + num_of_writes) AS NUMERIC(10, 1)) AS [avg_io_stall_ms]
FROM
    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
INNER JOIN 
    sys.master_files AS mf
ON  fs.database_id = mf.database_id
AND fs.[file_id] = mf.[file_id]
ORDER BY
    avg_io_stall_ms DESC
OPTION
    (RECOMPILE) ;

With the data from that query you can judge whether the log or data files are the point of contention in tempdb. It's probable you'd be better off with both data files on one drive and the log file separate, but you'll never know without the numbers to back the decision.

share|improve this answer
    
Just curious, why OPTION (RECOMPILE) here? –  Paul Williams Jul 27 '12 at 21:56
2  
@PaulWilliams Means nothing more than "don't cache this plan". –  Mark Storey-Smith Jul 29 '12 at 0:55
add comment

As I understand, you have the local disk instead of SAN. In this case, I think you may use the following recomendations:

  1. As with any other database, placing data file and log file on separate disks may improve performace.

  2. Check the number of processors on the server installed and divide tempdb into multiple data files of equal size. These multiple files don’t necessarily be on different disks/spindles unless you are also encountering I/O bottlenecks as well. The general recommendation is to have one file per CPU because only one thread is active per CPU at one time.

Look ath article "Compilation of SQL Server TempDB IO Best Practices", there is lot of another recomendations.

share|improve this answer
add comment

Your Answer

 
discard

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