Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Apr 10 '13 at 20:51

This question came from our site for professional and enthusiast programmers.

    
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 Apr 10 '13 at 18:25
1  
Similiar to: serverfault.com/questions/29097/… –  jsobo Apr 10 '13 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 Apr 10 '13 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 Jun 10 '13 at 15:29

2 Answers 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.

share|improve this 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.

share|improve this answer
    
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. –  Max Vernon May 7 at 17:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.