Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Westley
Posting Yak Master
229 Posts |
 Posted - 2005-11-17 : 21:24:43
|
| Guys, Just in general, if you have a large DB, say around 50G in size, will you create 5 file (10G each) within 1 file group? or you will go in more details to create different file groups and assign tables to diff file groups? just in more general terms, if 1x 50G mdf file is better (more efficient) or 5x 10G data file will be? as all file in the same SAN (or array) within the same same group, i can't really see it will be more efficient, but just want to know what others think.Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-19 : 08:40:33
|
| Having multiple files is a bit more efficient due to the ways that sql server references data but it means that you need to generate multiple move statements when you restore the database.Also you cannot use sp_attach_single_fike_db to restore the log.I usually stick to a single file if possible.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-11-20 : 19:30:28
|
| Why will it be more efficient since it will be on the same drive, since you will not get any better IO performance? I'm just wondering why is this going to help, I understand that if you have different filegroup and put diff tables (or indexes) in different filegroup which lives in diff phyical HDD that will help, since it can use more disk IO, but on the same drive, can't really see much improvement there, just wondering am I missing anything thats all. |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-20 : 21:33:34
|
| If you're on a SAN and you're not going to spread it over any more drives by breaking it out across multiple arrays, then the benefits of having multiple files is pretty minimal. The big exception to this is tempdb.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2005-11-20 : 22:53:22
|
| why would tempdb be the exception? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-11-20 : 23:42:42
|
Well, you can start by reading here:http://support.microsoft.com/kb/328551 There are all kinds of documents linked in there that have information on concurrency and processor utilization by tempdb.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-11-21 : 06:16:03
|
quote:
Originally posted by Westley
Why will it be more efficient since it will be on the same drive, since you will not get any better IO performance? I'm just wondering why is this going to help, I understand that if you have different filegroup and put diff tables (or indexes) in different filegroup which lives in diff phyical HDD that will help, since it can use more disk IO, but on the same drive, can't really see much improvement there, just wondering am I missing anything thats all.
It's due to the way sql server accesses the data in files. There was a white paper out a while ago that said that the shortest path to the data was if mutiple small files are created (I've never tested this because I suspect the gain would be minimal compared to other things you could spend the time doing). When you work on a system with 20 or so files for a database you tend to realise that performance isn't everything.Fortunately that person has moved everything back into a single files now.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Filegroup qn