Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Red face Unanswered: Filegroup confusion help

    I've become quite confused with filegroups after reading so many articles so I'm hoping maybe I can understand with something simpler.

    Here's the scenario

    I have a large database with a few tables in particular that are heavily read
    and written. I have three separate disk. One for the log file, one for the
    data file, and the third is yet to be used. (We're on a raid 1+0).

    What would be a good recommendation?

    1. Create an .NDF file on the third drive and point it to the Primary
    filegroup (let the raid automatically sort it out)?

    2. Create an .NDF file and filegroup on the third drive and point one of our
    most heavily read and written tables to it (data and index's included)?

    3. Create an .NDF file and filegroup and point ALL table non-clustered
    index's to the this filegroup?

    Thank you in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I can see the upside to 2 and 3. why would you do 1?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Nov 2004
    Posts
    2
    I read about option one here: http://www.sql-server-performance.co...d=123&type=tip

    "As a general rule of thumb, don't try to manually assign tables and other objects to filegroups to reside on specific physical drives. Instead, let RAID 5, RAID 10, or virtual SAN devices do this for you automatically. In the long run, you will probably get better overall performance. [7.0, 2000, 2005] Updated 9-19-2005"

    So that's why I considered it... is that wrong?

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The MS line is, if you can you should try and separate your NC indexes and their respective tables on separate file groups that reside on separate physical drives. I do this when I can as one of those blindly accepted rules of thumb from on high, but I have done no metrics myself.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by elsalsero
    So that's why I considered it... is that wrong?
    No it's not.

    Up to 2K a table could reside ONLY on 1 filegroup. It may or may not be the same as its non-clustered indexes. The advantage of having more than 1 file per filegroup is in the GAM/SGAM/IAM contention in busy environments. To relieve that contention we sometimes recommended to add aditional files to an existing filegroup.

    With 2K5 the concept of data partitioning was introduced (also known as table and index partitioning). This concept allows placement of a table or a part of a table onto a partition, that is comprised of 1 or more filegroups, that are in turn comprised of 1 or more files.

    In your scenario I don't see a "pain" that you experience. Besides, you did mention your intent to separate logs and data devices, but you didn't mention tempdb. I'd put tempdb devices (both data and log) onto the drive that is not being used yet.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Thrasymachus
    The MS line is, if you can you should try and separate your NC indexes and their respective tables on separate file groups that reside on separate physical drives...
    In 2K5 you should keep them storage-aligned.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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