stevro (5/13/2012)
I fail to fully understand why minimally logged operations can be slower . With the eager write process, should it not ease the burden on the transaction log by writing the dirty minimally logged records only to the data file? By splitting the writes as opposed to the fully logged model should it not be faster, especially if the log and data are on separate drives? Is flushing to the data files slower than writing to the log if both are sequential? I can understand it if the writes are random, but are minimal logged operations not all sequential in nature when writing to the data files?
With a fully logged operation only the log records have to be written to disk before the transaction is complete, the data pages can follow later.
If you've got a database where the data file is optimised for reading (say raid 5 and read caches) and a transaction log optimised for writing (say raid 10 and write caches), then it is possible that writing X GB of data to the data file will be slower than writing X GB of data to the log file would have been, and the transaction commit will have to wait for the write to the data to the data file to finish where normally it only has to wait for the write to the log.
I didn't say it will be slower, but it can be, maybe not usually, but it is a possibility.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability