Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2003
    Posts
    12

    Question Unanswered: Splitting datafile

    Hello DB gurus,

    I have a database with one datafile of size 40GB. Can some one tell me how to split that one datafile into four datafiles of equal size.

    Thanks

    Rachael

  2. #2
    Join Date
    Jul 2002
    Location
    IA
    Posts
    28
    Are you talking SQL 2000?

    Filegroups is what you want. BOL has a ton of info on Physical Database Design. Do you have seperate disks for each of these new 4 files?

    What are you really trying to do? With filegroups you could speed up backup times if you have some tables that are rarely updated, so don't back up that filegroup as often, etc. If you have more disks in your server each filegroup should go on a seperate disk to get a performance gain.

    ALTER DATABASE has a MOVE FILEGROUP clause.
    BACKUP DATABASE has a FILEGROUP clause you may need to use.
    CREATE DATABASE using the FILEGROUP keyword to get this all started.

    Give us more info, using FILEGROUPS can cause more headaches for backup and restore. Spliting a 40GB file for the sake of splitting a 40GB file may not be the way to go. If I had the hardware and needed more performance I'd definatly give it a spin.
    Thanks,
    Jason

  3. #3
    Join Date
    Jan 2003
    Posts
    12

    Question

    Originally posted by barneyrubble318
    Are you talking SQL 2000?

    Filegroups is what you want. BOL has a ton of info on Physical Database Design. Do you have seperate disks for each of these new 4 files?

    What are you really trying to do? With filegroups you could speed up backup times if you have some tables that are rarely updated, so don't back up that filegroup as often, etc. If you have more disks in your server each filegroup should go on a seperate disk to get a performance gain.

    ALTER DATABASE has a MOVE FILEGROUP clause.
    BACKUP DATABASE has a FILEGROUP clause you may need to use.
    CREATE DATABASE using the FILEGROUP keyword to get this all started.

    Give us more info, using FILEGROUPS can cause more headaches for backup and restore. Spliting a 40GB file for the sake of splitting a 40GB file may not be the way to go. If I had the hardware and needed more performance I'd definatly give it a spin.
    Thanks Jason.

    Yes I am talking about SQL Sever 2000.
    I have a db in production with one datafile of size 40gb. My plan is to split this datafile and create four datafile which will be placed in seperate hard-disk. As you mentioned there will be performance gain by having datafile in its own hard-disk spindle.

    My problem is I already have database created in production with primary group with only one data-file. If I add another datafile to primary group, will it distribute the data over to the new datafile(s).
    In other words what is the way to spread the data from one datafile across different datafiles.

    The restore process does not take a logical_file_name and move to different multiple physical datafile. Thats pretty much I am looking for.
    Any help will be appreciated. I looked at Books-Online for help but not able to find what I am looking for.

    Rachael

  4. #4
    Join Date
    Apr 2003
    Posts
    30
    [Book online]

    Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, Microsoft® SQL Server™ 2000 writes an amount proportional to the free space in the file to each file within the filegroup, rather than writing all the data to the first file until full, and then writing to the next file. For example, if file f1 has 100 megabytes (MB) free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. This way both files become full at about the same time, and simple striping is achieved.

    It will be good to create another filegroup in different hard drive, put your transaction log into this filegroup, separeted from your datafile.

    If you want to move some of your existing data to the new filegroup (in different drive), you can rebuild your clustered index into the desired filegroup. Separate the tables often doing joins into different hard drive will improve the performance.
    Last edited by jzhu; 05-29-03 at 15:15.

  5. #5
    Join Date
    Jul 2002
    Location
    IA
    Posts
    28
    You can also use the ON filegroup to just move a specific table to the new filegroup. Otherwise I think I would just use hardware RAID.

    Does anyone know if the file striping jzhu wrote about is faster/slower than just having a hardware RAID set dedicated to the database? I would think hardware is faster.
    Thanks,
    Jason

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    a hardware solution (RAID) is normally faster, there are always exceptions though.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Apr 2003
    Posts
    30
    Even you use RAID, a common installation technique for relational database management systems is to configure the database on a RAID 0 drive and then place the transaction log on a mirrored drive (RAID 1).

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    ah, No!

    most shops put the data on RAID 5 array unless they need performance then they will use RAID 1+0. The trans logs endup on what ever is available, but for performance should be place on a RAID 1 array.


    RAID 0 is a no no due to the lack of fault tolerance.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Jan 2003
    Posts
    12
    So the best way here is to rebuild clustered index on new datafile and that way that table will be moved to new datafile. Then shrink the old datafile to reclaim unused space ?. Well then it wont be equally distributed though.

    Is there any way where I take a backup of existing production db and stripe across multiple datafiles while restoring it to get equal distribution of data.

    Thanks,
    Rachael

  10. #10
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Racheal, Not sure of your hardware configuration so simply suggesting you split your 40gig file up would be un wise.

    To answer this question I would want to know what problem you are solving, and what you have available, hardware wise.
    Paul Young
    (Knowledge is power! Get some!)

  11. #11
    Join Date
    Jan 2003
    Posts
    12
    I was doing performance optimizations on bunch of queries. Its working great. I wanted to see if any performance gain can be achieved by having striped datafiles. I was reading that having multiple datafiles can improve throughput.

    The hardware is EMC Symmetrix. Each volume is built with 10 hardware striped storage units.

    Rachael

  12. #12
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Multipule datafiles won't get you anywhere if they are not set up to take advantage of your hardware configuration.

    I personnaly would create a filegroup for each volume. Store your data on a few of the file groups, store your indexes on a diffrent set of file groups and your log files a diffrent set of file groups.

    What this does is split up all the I/O needed to search the indexes and read the data.

    Does this make sense?
    Last edited by Paul Young; 05-29-03 at 16:47.
    Paul Young
    (Knowledge is power! Get some!)

  13. #13
    Join Date
    Jul 2002
    Location
    IA
    Posts
    28
    I've also had problems with DBs that are mixed with OLTP and OLAP. If you have tables that are mostly for reporting, you could get them out of the way of the hot I/O tables.

    I would have tempdb on it's own disk and the OS's swap file on it's own disk first. If you have a lot of jobs or are using replication get MSDB on it's own as well.
    Thanks,
    Jason

  14. #14
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565

    Filegroups

    Issues issues issues

    filegroups should not be implemented as a performance enhancing process, the prevelent reason for implementing the FG process is maintenance. specifically backup of VLDB's (Very Large DataBases i kid you not). if you stripe the datafiles accross multiple filegroups you will be able to perform filegroup backups which will make your nightly backup ordeal a bit more manageable.
    another reason for multiple filegroups is that you can change the default filegroup to another FG on another disk. the advantage is that all objects that you create, by default are created on the default FG which contains the MASTER MODEL MSDB (DISTRO) and as you know, all of your system catalog requests come from MASTER so you will be contending on disk for queue space with dictionary queries to master..sysobjects, master..sysxlogins etc..... Moving the default FG to another disk will separate the IO for Catalog Requests from User Data Requests.
    another reason to use the FG's is the place very high maintenance tables and their indexes in their own datafiles so as to allow you to backup an individual table and and maintain it's size relative but sepreately from the database.
    as a performance unit the only benifit you will see from FG's is the reduction in contention for disk files.

    if you are trying this for performance reasons is suggest you dont do it simply because of the added effort and complexity that it adds to your database. if your nightly maint processes are taking longer then they should then maybe this is for you..

    just some food for thought.
    Last edited by Ruprect; 02-24-04 at 22:32.

Posting Permissions

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