Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Posts
    1

    Unanswered: splitting datafile for SQLSERVER 2000

    Hello everyone,

    Our production database has a single 2 Gbyte in size and I wanted to split this datafile into two or more to increase performance. I wanted to find out whether this is possible and if so, what are the steps to follow

    Thanks in advance
    -Shan

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Exclamation Re: splitting datafile for SQLSERVER 2000

    Originally posted by krishss
    RE: Hello everyone,
    Our production database has a single 2 Gbyte in size and I wanted to split this datafile into two or more to increase performance. I wanted to find out whether this is possible and if so, what are the steps to follow
    Thanks in advance -Shan
    S1 It is possible to have multiple datafiles in a database. For example, each may be placed on different RAID arrays which can increase IO performance in some situations. It is also possible to have multiple datafiles on different "filegroups" which, for example, additionally allows the partitioning of indexes from table data (which also can increase IO performance in some situations).

    S2 For a complete explanations about adding database files and filegroups in Sql Server, (and partitioned viewes / federated servers for the enterprise and dev. versions, which I have not discussed here), and the steps to follow, refer to Books On Line (BOL) which explains each topic in some detail.

    S3 With such a small DB [2GB] I would be at least a little surprised if any file level physical design manipulations would really increase your perfromance all that much. If you are using some very, very old slow drives (that in fact demonstratably are a performance bottleneck in your situation), that would make for a somewhat stronger argument for considering such kinds of physical design changes. (Generally I have not readilly been able to reproducibly measure performance differences until a point when one is dealing with numerous heavily used multi GB tables with large, heavily utilized indexes. At that point, some differences in performance can be noticed.)

    S4 Adding files and filegroups to a DB does potentially have some negative connotations depending on your circumstances (if nothing else it almost certainly will complicate your backup and restore options and also complicate future restore procedures somewhat).

Posting Permissions

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