Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    1

    Unanswered: Recommended db structure for Ms SQL

    I'm seeing a lot of databases for SQL in producstion that just create databases using one filegroup in Primary and no secondary filegroups. Even indexes are in the same filegroup. Will segmenting tables and indexes and separating busy tables from each other still give a lot of performance enhancement or just keep them in the same filegroup and just create multiple datafiles for primary just as effective than segmenting? I'm an Oracle DBA working on implementing MS SQL 2000 and being tasked to design the structure. Any input would be greatly appreciated.

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    What you are seeing is a hold over from earlier versions of SQL where you were limited on how you could seperate multipule data files and index files. SQL 2K acts more like Oracle. If you want an impressive server, just think Oracle in your planning. As I am sure you have figured out, filegroups are the mechanism for placing data files and indexes on seperate drives.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    Prior to SQL Server 7.0 you use to create devices, left over from Sybase. Which would allow you to create objects on seperate devices like filegroups. However filegroups now give you the flexibility to backup just the filegroup something you couldn't do with devices.

    I always have this question. If you have a machine that has 2 drives C: (mirrored) and D: (RAID 5) then there really is no performance benefit to filegroups is there? There is maintanence benefit, you can place all static tables on it own filegroup and dump that filegroup only when the static data is changed, while dumping the rest of the database nightly as an example.
    MCDBA

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    No there isn't a performance benefit, however as you pointed out there are some maintance advantages.

    Regardless, I still try to use filegroups.
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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