Advantages to have mutiple data files

Question

Advantages to have mutiple data files

SQL ORACLE

One Orange Chip

Points: 27807
More actions
August 7, 2008 at 2:31 pm

#211036

Would anyone like to share his/her knowledge on what advantages we have to use mutiple data files and under what conditions?
Any input is greatly appreciated in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Having multiple datafiles where the files are of different physical disks can improve the speed of data reads and writes since the data can be read from the different drives in parallel.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 2

If you have multiple datafiles, new data will be spread over the datafiles, see this article:

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/08/17/948.aspx

Wilfred
The best things in life are the simple things

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 3

Update:

I'm also a fan of multiple datafiles, but I'm a little confused when I saw this article:

http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

Wilfred
The best things in life are the simple things

GSquared SSC Guru Points: 260824 More actions · Answer 4

On very large databases, it can also have the advantage of doing smaller, file-by-file backups, instead of having to back up the whole database all at once.

But the main advantage is when you can split the files across multiple disks. If you do that right, you'll end up with better speed.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ray Mond SSCertifiable Points: 7159 More actions · Answer 5

Having multiple data files on physically different disks will result in faster read throughput during backups(http://www.sqlbackuprestore.com/backupreadsandwrites.htm).

SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

as mentioned the primary reason for filegroups is to spread the database across multiple disks\arrays to increase performance. Placing filegroups on the same array is really pointless

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Gordon-265412 SSCrazy Points: 2898 More actions · Answer 7

To add..

When you create multiple files (in the same filegroup) you will increase the number of IO threads that the query will use (1:1 ratio).

So if you have one FG with one file, the server will only use one IO thread. If you add multiple files to the FG, it will create one IO thread per physical file. By using a combination of seperate files in a FG and physically seperate disks, you can dramatically improve (in most cases) your IO operations. You can also oversaturate your hardware if you're not carefull. YMMV. 🙂

Edit:

Interesting article from Bob Dorr as referenced earlier.... I wasn't aware that multiple files on a single drive wasn't a criteria for multiple IO threads. However, I'm fortunate enough to have the hardware to spread across physically seperate drives. Gotta remember that!

Your friendly High-Tech Janitor... 🙂