Results 1 to 11 of 11

Thread: Table data file

  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Table data file

    Hi,
    How to make the diffrent data files for different table in a database (SQL SERVER 2K)? By default it is the single data file and log file for the data base. But I wnat the separate data files for all my table in the database. So, any one can help me out in this regard......................???

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by DBA_Rahul
    Hi,
    How to make the diffrent data files for different table in a database (SQL SERVER 2K)? By default it is the single data file and log file for the data base. But I wnat the separate data files for all my table in the database. So, any one can help me out in this regard......................???

    I am just curious why exactly you want to do this?

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Mr. harshal_in,

    The Reason behind this lies in the speed factor. If there are diffremt files for each table, then its obvious that retreival will be more faster then searching the data from the single data file......................

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Talking

    Quote Originally Posted by DBA_Rahul
    Mr. harshal_in,

    The Reason behind this lies in the speed factor. If there are diffremt files for each table, then its obvious that retreival will be more faster then searching the data from the single data file......................
    r u planning to keep all the files on different disks ?
    if u keep them on the same disk then i believe it will hardly matter.
    the different approach would be to have all the tables on one disk an dthe indexes used on teh tables on other disk .

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you can't move each file to its own physical disk drive, you'll incur more overhead due to the file handling and performance will slow down. Look at some of the whitepapers behind Terra-Server for some of the research on this problem.

    -PatP

  6. #6
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Yor are on right track, in getting my problem. I awnt to store the diffrent files on diffrent disks. Now please tell me the overheads.

  7. #7
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Read the white paper he pointed you to. Also, look at www.sql-server-performance.com and msdn.microsoft.com for more information. Also, from your other posts it looks like your selling this application, along with the database, to your clients. Are you going to have them set up and manage the backups for all these different files you're creating? That probably wouldn't be the best idea on earth.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  8. #8
    Join Date
    Sep 2003
    Posts
    522
    it sounds like there are some performance issues either related to the database design, or to the app architecture. an attempt to patch such situation by creating dedicated file groups will produce marginal results even if you put the files on different disks. in order to see a more noticeable improvement you'll have to also have dedicated disk controllers. but you may find yourself very quickly out of luck, because you'll run out of pci slots. of course, the alternative would be to put it all on san...but how much do you want your clients to pay for your app to run? how about reviewing the design at least?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Buy a nice SAN. The EMC systems work wonders. You can buy the smaller ones (1 to 6 Tb) now for under $250,000 and they'll give you much better performance than hard drive combinations or any other hardware tweaking can.

    Fixing the underlying problems in the application is probably both cheaper and more effective in the long run.

    -PatP

  10. #10
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Thnx all, for your kind contribution towards getting the solution............
    lemme check the steps mentioned, and ofcourse get back to you all.......


    thnx 1nc again

  11. #11
    Join Date
    Mar 2004
    Location
    London, UK
    Posts
    71
    sounds like a bad idea to me,

    honestly couldn't see the point in storing individual tables on individual files unless we're talking a massive amount of data and i do mean massive.

    what i'd do if you've got spare disks free is have:

    1) store the O/S on disk A
    4) system swapfile on disk B
    2) data files on disk C
    2) indexes on disk D
    3) log files on disk E

    then grab a new set of disks to implement RAID 5, however as i hear, microsoft are saying RAID 5 is not the way to go any more

Posting Permissions

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