Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Location
    Australia
    Posts
    8

    Question Unanswered: Balancing data between files

    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.

  2. #2
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    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!

    -- This is all just a Figment of my Imagination --

  3. #3
    Join Date
    Mar 2005
    Location
    Kiev, Ukraine
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •