April 5, 2007 at 8:24 am
I believe it writes to one at a time, though there are multiple threads, one per file. So if you have a large or open tran, it could be writing to a previous file while new tran information is being written to a second file.
April 5, 2007 at 8:29 am
I found this in BOL.
"If the log contains multiple physical log files, then the logical log will move through all of the physical log files before it wraps back to the start of the first physical log file."
This statement seems to indicate that it uses 1 log file, and writes to it sequentially. I can't get over the fact that writing to more than 1 log file simultaneously, regardless of threading and processes, would require the read/write head on a disk drive to jump back and forth, destroying any benefits of sequential writes.
April 6, 2007 at 10:05 am
SQL Server will write to the transaction log sequentially so it writes to only 1 log file at a time.
Microsoft recommends that you have 1 drive per CPU core for data drives with a single data file on each drive for the database. This way SQL Server can read / write to the database faster because there are multiple entry points into the database when files are spread out across the disks.
Also they recommend that you maintain a proportional fill for each data file such that the amount of free space in the data files are the same. This is where autogrow can get you in trouble because if the free space changes on only 1 file and SQL Server determines that file has more available free space in it, SQL Server will write to only that file instead of spreading the load across the drives. This create a data hot spot and can adversely affect performance.
Jason
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply