We are storing all our SQL 2000 databases on SAN LUNs, and one of our databases currently uses a single 40GB file which is approaching capacity. If we add further files using different LUNs, the data will start being added to these new files. My questions are these: if we were to add a number of new LUNs to this database, is there a way to redistribute the existing data so it is balanced across all files in order to gain the most benefit from having multiple files, rather than just dispersing the additional fragments across the new files?
Will the optimise feature of the maintenance plan do this automatically during the index rebuilds?
Is it better to add more files to the PRIMARY filegroup, or add a number of filegroups with single files in each? We aren't looking to use filegroups for fiddling with our backups by the way.
Many thanks for any recommendations offered.I would opt for more spindles and heads to move the data quicker. Best way I have found is to create new filegroups, then drop primary index on old filegroup and then recreate primary index on desired filegroup. This accomplishes two things.
First, it forces the move of the table data to the new filegroup since the leaf node of primary index **IS** the data page. Secondly, you get not only an index reorg, you get a contiguous page allocation based on the primary index.
Prior to this move, you might want to look at your fill factors to see if they need to be adjusted, because this would be a great time to do that too!|||Also, moving nonclustered indexes in the way described by tomh53 is a great idea as well - not only do you equally distribute your data, but also separate table and its indexes onto different devices, which is generally a good thing to do.sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment