Sql-server – Tempdb with Multiple mdf and Renaming

Correct. In theory, if you have 100s of DBs you need 100s of drives, one for each log. In practice though one does not care for such case, cause when you have 100s of DBs you obviously don't expect top-notch TPC performance for each DB. You will likely have some DBs with high throughput and stringent SLAs and you could have them each on separate spindles, while the many lower level SLAs cram on a few shared disks.

I just tried quickly to find an answer because there is a lot here, but this is a common question. Didn't find quite what I wanted yet so a few thoughts to guide you:

First: It depends is the default answer here.

It really depends on what the performance characteristics are of your system. You should review what your waits and filestats look like and consider where your pain points are and build according to that. There are sizing concerns and performance concerns.

A few thoughts to guide you:

• RAID 5 can be fine for data files unless you are doing some heavy writes and performance becomes a concern. So this isn't always good, but a place I feel safe about RAID 5, again your mileage really varies based on your performance needs.

• Mirroring or RAID 10 is where I tend to like to go with log files and TempDB.

• In some environments, TempDB needs to be isolated onto it's own first because it is a shared resource and used when queries spill over from memory to disk as well.. In some environments, log files need to be isolated and get better performance because of the insert activity.. In some environments, data files need the best IO (heavy read/reporting).

• You need to look at Filestats and Waitstats and Perfmon and really see where your pains are.

Using multiple file groups is a great approach/answer for certain workloads. Separating TempDB is usually a best practice though I'd never put TempDB on RAID 5 typically.

And if the environment is pushing no IO? Having no IO waits? I've seen and placed environments all one one big happy set of drives and things have been fine. It really depends on a lot.

I'd spend some time profiling your workload. Look at the PAL tool and run some perfmon scripts on your existing environment. Look at your SQL Server File Statistics. Read about some best practices in SQL Server IO, and make informed decisions and monitor to see how they look as you go.