SQL Server Performance

How to split 400GB database into multiple data files

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Eric72, Mar 7, 2008.

  1. Eric72 New Member

    I have inherited a SQL Server 2005 database that is 400GB in size with 300GB of data on one data file on a RAID 10 disk and a second 100GB data file with indexes only on another drive. The log is on a separate drive as well.
    I would like to split the data data file into multiple files and the index data file into multiple files potentially to span different disks to achieve parallel i/o. I believe this can be accomplished with the creating several data files then using the empty file command or by using the restore method with move.
    Does anyone have any insights into a "best solution" method?
    Thanks.
  2. satya Moderator

    Welcome to the forums!
    What is the furture growth of this database?
    DO you have any performance issues at the moment?
    Why you are thinking about partitioning the data?
  3. Eric72 New Member

    Satya,
    I want to split the one big data file and potentially index file into 4 or more files for file portability and for parallel i/o. These files reside on a SAN.
    Thanks,
    Eric
  4. satya Moderator

    Yes you can achieve that by uisng ALTER DATABASE statment, if you have the data in 1 file already then you should move them to these files in order to see what you want to achieve.
  5. viv New Member

    [quote user="satya"]
    Yes you can achieve that by uisng ALTER DATABASE statment, if you have the data in 1 file already then you should move them to these files in order to see what you want to achieve.
    [/quote]
    How will you actually move data across this newly created datafiles?
  6. rohit2900 Member

    Welcome to the forums.....
    First you need to add those new data files to the database by using alter database as suggested by Satya.
    Then you need to divide your table into groups as what tables you want on which data file. Now you need to move those table data to the respective datafile. And you can achieve this by CREATE INDEX with the DROP_EXISTING clause.
  7. deepakontheweb New Member

    Usually DB Architects consider splitting database files influenced by table size.. rather than actual table usage. for 'example' if a db has 10 tables, its seen in practice you create as following [Opt 1] 5 files having 2 tables each- simply called equal share OR [opt 2] 4 files having 1 table each of larger sizes and 6 tables in 1 file only - called table size distribution OR [Opt 3] this I usually follow, I look into each table importance/usage by application and corresponding size and create files having balanced DISKIO because not all tables are equally important and being used frequently; it helps increasing parallelism on SAN and faster query results. though its difficult to implement as a lot ground work needs to be done before making decision about which table goes where for balanced division. Good luck;
  8. viv New Member

    Thanks a lot for you replies guys.
    I suppose to increasing parallelism on SAN and faster query results you will have to split this datafiles (.ndf files) accross different disk arrays served by an independent disk controller on SAN. Sorry, if i may sound a bit ignorent here but i dont really understand SAN Architecture very well.
    Regards,
    Viv
  9. deepakontheweb New Member

    Yup, database files (mdf/ndf/ldf)should be placed in physically/logically different SAN RAID array's & controllers. by having this placement you will get true parallelism benefits. have a quick look on this article: http://www.novell.com/coolsolutions/appnote/19687.html
  10. cteune New Member

    Sorry if this discussion is already closed but I had a question. Is there any performance improvements gained by splitting a database into separate files if your server is not running an array and is a single drive server? i ask this because the emphasis seems to be on parallel IO as the reason for file splitting but with a single disk this is not going to help.
  11. FrankKalis Moderator

  12. MohammedU New Member

  13. viv New Member

    Hi cteune
    There will not be significant if any performance improvements improvement if you are planning to split database into seperate files on a server with one physical disk.
    As per microsoft, If you had a disk array, Each separate file is physically placed on a disk or set of disks (if thehardware disks are striped). SQL Server maintains a map of each file'slocation on the disk. If one file is created across a hardware stripeof four disks, one map points to the location of data on all fourdisks. If four files are created across a hardware stripe of fourdisks, four maps point to the location of the data on all four disks,one map in each file for each object in that file.Whenever a data object (table) is accessed sequentially, a separatethread is created for each file in parallel. Therefore, a tablescan fora table that is assigned to a filegroup with four files uses fourseparate threads to read the data in parallel.
    So in the the case of disk array you are better off with the multiple file setup.
    I understand this is not all comprehensive message but there whole lot more to this subject. Please feel free to ask questions.
    Vivek
  14. satya Moderator

    To addup you have got many great references out there on that question, but within my experience it depends on hwo the database is normalized. No matter if you sprawl out the db files in multiple disks the performance will suffer if the data modification/insert process hammers the database heavily.
    [quote user="cteune"]Sorry if this discussion is already closed but I had a question. Is there any performance improvements gained by splitting a database into separate files if your server is not running an array and is a single drive server? i ask this because the emphasis seems to be on parallel IO as the reason for file splitting but with a single disk this is not going to help.[/quote]

Share This Page