March 3, 2015 at 12:08 am
One of our customers asked us why if having multiple datafiles for tempdb is beneficial, 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).
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.
Any thoughts or additions to this?
March 3, 2015 at 1:59 am
Spot on. Nothing to add IMHO.
-- Gianluca Sartori
March 3, 2015 at 6:03 am
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.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 3, 2015 at 6:09 am
Perry Whittle (3/3/2015)
Bouke Bruinsma (3/3/2015)
One of our customers asked us why if having multiple datafiles for tempdb is beneficialcan 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.
My focus was on tempdb allocation contention and not I/O contention although that is a topic worth considering also. Thanks for pointing it out.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply