Perry Whittle (3/3/2015)

---

Bouke Bruinsma (3/3/2015)

---

One of our customers asked us why if having multiple datafiles for tempdb is beneficial

can be beneficial, in most cases you may not even need it, the key is to monitor the system to check if allocation contention is a problem. The recommendation is to put multiple files onto the same volume.

Bouke Bruinsma (3/3/2015)

---

why not more than one transaction log file?

My answer was this:

Transaction log files are written sequentially. The data in this file represents a logical, chronological chain of all transactions in the database and therefore only one file will be written at the same time. The only reason for having multiple transaction log files that I can think of is space considerations (the 2nd tranlog can be on another disk with more space).

That's pretty much about the size of it, unlike Oracle systems you cannot switch logs manually either.

Bouke Bruinsma (3/3/2015)

---

Multiple tempdb datafiles will reduce storage contention. The database workload, especially reporting, uses tempdb intensively so having multiple datafiles will reduce the time needed for storage allocations in tempdb. Initial sizes of all datafiles need to be the same as well as the growth rate.

Now it sounds like you're discussing I\O contention and that's totally different to tempdb allocation contention. To alleviate I\O contention you must place files across multiple arrays. It may not necessarily reduce time needed, if all the arrays operate at different speeds\rates you may have an issue and don't forget the more files you put in the filegroup the more work the database engine has to track the free space and perform extent allocations.