0

I'm setting up my production SQL server. I have two 800GB SSD disks available to me (for Windows and SQL Server).

Where should I put databases' data and log files?

How many tempdb files should I create and what size should they be?

Some recommendations are to create 1 tempdb file for 1 processor core. That would mean 16 files in my case (2 processors with 8 cores each). That seems too many to me... Does this recommendation make sense on SSD?

Should I put log and data files into different SSD?

I use SQL Server 2008 R2 and server has 32GB RAM.

Dennis Gorelik
  • 331
  • 2
  • 8

1 Answers1

1

If you only have two disks and this is a production server, you should be using RAID 1 and having a single 800GB mirrored volume, so database, log, and tempdb placement is relatively immaterial. Separating these files allows for predictable IO utilization, but with only two disks you want redundancy in production servers over performance.

As for how many total tempdb files you should have, SQL Server voodoo wizard Brent Ozar recommends starting with 4 and profiling your performance from there. In modern systems with 16+ CPU cores, it usually doesn't make sense to have one tempdb per core.

MDMarra
  • 100,734
  • 32
  • 197
  • 329