March 15, 2011 at 10:55 am
I have a 250 GB database and all the data resides in a single file.
I want to split the data in multiple files.
Can I do that by creating a file group and then create a clustered index on the file group?
But considering the huge size what is the best possible option?
March 15, 2011 at 11:10 am
There are lots of strategies to use and testing will yield the best results in your environment.
One method to use may be to create 2 FG's and relocate some Clustered Indexes (tables) to FG1 and relocate it's associated NonClustered indexes to FG2. Separate the FGs on different LUNs for better recoverability and performance.
Also, a word of caution, Be careful to not double up your data and your log files and your backups on the same physical media or recoverability could be an issue.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 15, 2011 at 1:28 pm
I typically try to keep ALL user data in a file group and only system data in the primary group. We setup file groups for indexes, dynamic data and static data. We create objects in the corresponding file group.
March 15, 2011 at 2:10 pm
First, just to be clear, the clustered index isn't on the filegroup. You create a clustered index on a table, and place that in a filegroup to move the table to that group.
The primary filegroup and log are critical. If either of those are damaged, you need to restore. People to move all user data/objects to a secondary filegroup, but that really only helps, IMHO, if you have Enterprise Edition and can be partially available. In Standard, I don't see it helping.
The other reason for filegroups is administration and possibly ensuring you have space on the various drives your server sees.
If you have Enterprise, and want to be partially available, make sure that what comes up works. So don't necessarily move indexes separate from data if that doesn't help your application work.
Plan filegroups around recovery and administration.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 15, 2011 at 2:15 pm
Yep. In other cases, it is good to throw read-only tables (which don't get updated very frequently if at all) in a read-only file group.
In still other cases, testing may prove that creating separate file groups with several files each - all on separate IO, allows the load to be balanced best. If you have that many drive sets to choose from.
Really, the 'best' strategy depends on how your data is accessed, which tables/indexes are hottest (busiest), which RAID you have, which logical drive letters are fastest, isolating and separating TempDB because THIS may actually be the largest bottleneck.
Really, there are a lot of moving parts to start learning about. Spend some time on this site digging through the Forums and reading Articles and Blogs for methods that people have found to figure this out. Search for SQLIO as a starting place so you can figure out how/what to test for and what the results mean. Then you start building a baseline of they way your environment currently is. Only then will you be able to make an educated guess at a new strategy. Then implement in a test environment and test to see the results. Rinse and Repeat as necessary.
Jim
Jim Murphy
http://www.sqlwatchmen.com
@SQLMurph
March 15, 2011 at 2:34 pm
What's your reason for spitting the files?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply