Results 1 to 5 of 5

Thread: FileGroup

  1. #1
    Join Date
    Aug 2006

    Unanswered: FileGroup

    There is a table which is about 50GB is size.
    I am thinking of placing the database of this table into a separate .ndf on a separate drive on the server.
    Is it possible to place this particular table into a searate drive to increase the performance even more?
    Basically my question is: is it possible to place tables into separate .ndf files?

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Yes, Microsoft did a great whitepaper about exactly this thought a few years ago. I'm not sure if they made it public or if it was "restricted publication" due to containing customer specific details.

    The general gist of the idea is to create specific filegroups for specific devices, then put the SQL objects on those filegroups.

    One area that often helps more than moving specific tables to a given drive is to segregate data pages (either the heap or the clustered index) onto one set of spindles (aka drive) and the indicies onto a different filegroup. This allows parallel IO to occur for even single table changes (assuming you have enough CPUs and IO channels)!


  3. #3
    Join Date
    Aug 2006
    I think I am now confused.
    Could you please elaborate further and do you know of a link regarding this issue?

  4. #4
    Join Date
    Aug 2006
    We can create two filegroups or more while creating database. The following SQL Statements maybe help you:

    CREATE DATABASE database_name
    [ ON
    [ PRIMARY ] [ <filespec> [ ,...n ]
    [ , <filegroup> [ ,...n ] ]
    [ LOG ON { <filespec> [ ,...n ] } ]
    [ COLLATE collation_name ]
    [ WITH <external_access_option> ]

    Here in the block <filespec>,you have to give the file path,then you can locate the files into different disk.

    Then referencing the following SQL statements:

    [ database_name . [ schema_name ] . | schema_name . ] table_name
    ( { <column_definition> | <computed_column_definition> }
    [ <table_constraint> ] [ ,...n ] )
    [ ON { partition_scheme_name ( partition_column_name ) | filegroup
    | "default" } ]
    [ { TEXTIMAGE_ON { filegroup | "default" } ]
    [ ; ]

    So you store datum in different disk.

    Good luck!

  5. #5
    Join Date
    Aug 2006
    Yes, this is what I was after.
    This seems to be a very powerfull stuff.
    Thank you

Posting Permissions

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