4

I'm getting a new 16 core server for my SQL Server 2005 database. I would like to create 16 files for my temp DB, and put each one on a separate drive. If one of these drives fails, can SQL Server continue to function using the other 15 files?

4

No. Your database becomes corrupt and SQL Server stops.

Last I checked, it was one file per socket not per core nowadays.

Note, unless you've RAIDed each volume (or have separate SAN LUNs attached) having multiple files on the same drive is mostly pointless: you'll cannabalise IO on that drive trying to spread the load.

1
4

Nope, I'm fairly confident SQL Server will fall over - I've had a few SAN attached drives disappear with SQL Server 2005. It's possible that SQL Server may not realise until it accesses TempDB on the drive that's failed.... in pre-prod, you should be able to simulate this by dropping the drive.

Unless you've carefully assessed this, I would also suggest you carefully reconsider your 16 TempDB datafile approach. The smart advice these days is to add TempDB datafiles only when you see TempDB contention (wait stats etc). If you really want to, start with 2 datafiles on the same drive.

The most recent TempDB recommendation I saw - it's Paul Randal, so essentially Mr SQL Server himself: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

1

Benchmark your tempdb, and if you are seeing a lot of pagelatches, then adding more files to the tempdb will help performance. If you are seeing pageiolatches, then you may need to put your tempdb files on different physical disks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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