-1

Have a dedicated DB server with 16 cores and 200+ GB of memory.

Use tempdb a lot but it typically stays under 4 GB

Recently added a dedicated SSD stripe for tempdb

Based on this page should create multiple files

Optimizing tempdb Performance

Understand multiple row files.

Here is my question:
Should I also create multiple tempdb log files?

It does say "create one data file for each CPU".
So my thought is that data means row (not log) files.

paparazzo
  • 44,497
  • 23
  • 105
  • 176

1 Answers1

2

No, as with all databases, SQL server can only use one log file at a time so there is no benefit at all in having multiple log files.

The best thing you can do with log files really is keep them on separate drives to the data files as they have different IO requirements, pre-size them so they don't have to auto grow and if they do have to autogrow, make sure they do so at a sensible level to manage the number of virtual log files that are created inside them.

steoleary
  • 8,968
  • 2
  • 33
  • 47
  • I believe you but what is strange is SSMS will let me create addition log files. – paparazzo Oct 30 '13 at 19:46
  • 1
    Yep, it will let you, but you really shouldn't do it, have a look at this article by Paul Randall for an explanation http://www.sqlskills.com/blogs/paul/multiple-log-files-and-why-theyre-bad/ – steoleary Oct 30 '13 at 19:51
  • Sorry to keep this going but if I only had one SSD stripe would it still be better to have a separate log? I have 4 SSD in the stripe. Could put 3 in the stripe and one for the log. My thought is a bigger stripe and let it manage the load. – paparazzo Oct 30 '13 at 20:09
  • I can't really answer that as I don't know your IO requirements, once you know these it will be clearer where you need to put these files, whether a shared drive for tempdb log and data is ok or not. What I wouldn't suggest is using a single drive for the tempdb log as if that one drive fails, your whole server goes down. – steoleary Oct 30 '13 at 21:13
  • But right now tempdp is on a stripe so if one drive goes down so does the db. – paparazzo Oct 30 '13 at 23:49
  • You're not talking RAID 0 right? On a production system? Please say no. Get that changed, it's a terrible setup. – steoleary Oct 31 '13 at 07:09
  • Yes it is RAID 0. It is tempdb so what if it fails. I want speed. – paparazzo Oct 31 '13 at 12:43
  • You know if tempDB fails that your SQL server goes offline right? – steoleary Oct 31 '13 at 12:50