7

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!

Improve this question
5
  • The first thing that comes to mind is equally sized data files for tempDB. see:Optimizing tempdb Performance msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
    – jsobo
    Commented Apr 10, 2013 at 18:25
  • 1
    Similiar to: serverfault.com/questions/29097/…
    – jsobo
    Commented Apr 10, 2013 at 18:26
  • From what I've read, you do see performance improvements when doing this with tempDBs, however other articles have indicated you will/will not see performance improvements with user DBs thus my confusion.
    – Shadowfoxx
    Commented Apr 10, 2013 at 19:11
  • 1
    "it depends." How many spindles? What's the throughput (Fiber vs. 100MBps Ethernet will make a difference)? Are you using MPIO?
    – swasheck
    Commented Jun 10, 2013 at 15:29
  • This question is old an unanswered - after reading all comments I still wonder what is the best practice configuration for sql datafiles when using a SAN?
    – Magier
    Commented Aug 18, 2015 at 15:10

2 Answers 2

Reset to default
6

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.

Improve this answer
1
  • While this is undoubtedly true in certain circumstances, having multiple tempdb data files can have a big impact when there is a lot of contention for GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), and PFS (Page Free Space) pages on multi-processor/multi-core systems.
    – Hannah Vernon ♦
    Commented May 7, 2014 at 17:15
2

You probably won't see any benefit from having multiple data files on a single LUN for a user database.

TempDB is a special case because of the massive amount of objects which are created within the tempdb database during normal operations.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.