managing datafiles

  • I have the following problem and I would like to know  your opinion:

    database size= 200 G (data warehouse)

    datafiles : primary(50%)



    My question is:

    -    what is the best to leave on primary datafile.Is it too big ??

    -   how is the performance if  a SQL runs on  1 datafile or many datafiles  at the same  time.

    -   is it better to  have big tables  with the indexes separated on different datafiles?

    -  and if yes, how can I design  it to  increase the performance?


    Thank you



  • Do your datafiles also relate to seperate physical devices?

    Shamless self promotion - read my blog

  • yes


  • primary = F,G,I

    order = G




  • Is there a high number of transactions on a consistent basis? And is the logfile out on another physical device?

    If you have a high number of transactions I would look at moving the indexes off to another filegroup on a different physical disk. It looks like you've done some work to try and get the physical design as highly performing as you can, and if you cannot add more disks then I don't think that you are going to get much better than what you currently have.

    Shamless self promotion - read my blog

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply