• Exactly. If the drive space is there, why not allocate it? I'm not going to use it for anything else.

    The reason for allocating multiple files is to avoid tempdb contention, and overloading the drive with IO is rarely a concern. On those rare occasions, where IO becomes a problem on the drive, I will separate the files to multiple drives. simply writing the same amount of IO to more files does not greatly increase the amount of IO going to the drive. There is some increase because more files affect the size of I/O block you can write. If writing to one file, it will write a larger block (when it's writing enough to need to write the larger block) than when to multiple files. That IO block size decreases a little each time you add another file, but it flattens out at 8 files. So the IO block size will be the same for 8 data files as it is for 16 files or 24 files or more.

    The SQLCat team measured this in benchmarking tests (specifically driving a workload that would take advantage of large writes):

    1 file = 256 KB max IO block size

    2 files = 196 KB max IO block size

    4 files = 132 KB max IO block size

    8 files = 64 KB max IO block size

    16 files = 64 KB max IO block size

    32 files = 64 KB max IO block size


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]