March 30, 2011 at 3:09 pm
Hello All,
I"ve been doing a lot of research on SQL DB file architecture, but haven't been able to find an answer the following questions. Most of what I have found is about filegroups, not file inside filegroups.
1. When should I use multiple files inside of a file group for a database and how many or what size is a rule of thumb?
I know that this can very from db to db, but is there any rule of thumb anyone uses. For instance, if I have 1TB filegroup, should I leave it one file or split it up even though the files will reside on the same file group. What about 500GB or 2 TB? Basically what size is too big.
Another related question is.
2. Is there any performance benefit to have multiple files in 1 filegroup on the same raid group?
I konw that this can sometimes help tempdb, because of pagelatch waits on the IAM page when new working tables are simultaniosly being created, but I'm guessing this exact situation doesn't relate to regular user databases.
In my current case, we have a DB that is 200GB all in one file in one filegroup. I'm trying to decide how or if I should split this up considering we only have one SAN Raid Group available for it. I also have another DB, that is 2+ TB that I currently have split into 2 file groups with 8 files each. One of the filegroups files are now 250GB a peice. I can't decide if I just let them grow or add more files to the filegroup to keep file size (and possibly corruption) down.
Thanks in advance for any guidance, this site rocks!
March 30, 2011 at 3:17 pm
The answers to your questions are not straight forward. It depends on your DB, usage, and environment.
I'd recommend reading this blog by Paul Randal on the topic.
The best answer would be to do a lot of testing and find the best configuration for your setup.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2011 at 3:51 pm
Thanks for the link, that is what I was looking for (I can't believe I missed that, I was just on thier site yesterday). And that is the answer I was afraid of as well, without the time and hardware to test more, I'm just stabbing in the dark.
One thing Paul doesn't mention in his post though is the possibility of corruption. I would imagine the larger the file sizes the higher the possibility of file corruption at the OS level?
March 30, 2011 at 3:56 pm
Tough to say on that one. If you figure that a larger file will cover more sectors than a smaller file - then the possibility of corruption from a disk sector going bad could be increased. But I haven't seen anything that would substantiate that hypothesis.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply