I'm currently in the process of creating a new database, and have previously only ever used a single data file and a single log file. I've done some research online regarding the benefits of multiple data files, but I've found mixed opinions regarding the benefits of multiple data files on a single disk. So my question is: if the only disk available to me is a SAN disk would I see any performance benefits (such as multithreading/parallel processing) from splitting the data into 4 separate files? Would I be better of with a single file?
Also would there be any performance benefit of separating the indexes (and possibly an archive table) into their own filegroups on separate files on the same disk? Would this only provide administrative benefits?
Thanks!
Best Answer
Even multiple tempdb files on a single LUN should be a last resort compared to one tempdb per LUN. If there are multiple database files on a LUN, and they are allowed to auto-grow, their contents will be interleaved in the filesystem and on the storage. Hit them both heavily with read or write requests, and a situation known as IO weaving, or disk head thrashing will result. Even though writes are generally cached, writes are not immune to io weaving, because eventually the writes need to destage from cache. If tempdb is used heavily by query spill, there will be a lot of sequential write activity. Put multiple tempdb on the same LUN, and there can be multiple streams of sequential write activity at different locations on the storage, all accessed through the same LUN queue. Sequential reads such as a table scan introduced on top can encounter worse-than-random read latencies.