Tables on diffrerent files

  • yuvipoy

    SSChampion

    Points: 10433

    Is it a good practise to have my tables in different files.

    Say i am having 10 master tables.

    Can i create tables each one on 10 secondary files(.ndf) in a single filegroup so that the data writing and reading will be faster?

  • gbritton1

    SSCertifiable

    Points: 6520

    If you're I/O bound, start with the log files. Put them in separate filegroups, preferably on separate spindles.

    If your tables are growing rapidly (e.g. transaction data) and most of the queries touch recently-added rows, consider partitioning. Seldom-accessed data can even be put in filegroups on slower (== cheaper) media. Good for the budget!

  • humbleDBA

    Hall of Fame

    Points: 3440

    yuvipoy (4/25/2014)


    Is it a good practise to have my tables in different files.

    Say i am having 10 master tables.

    Can i create tables each one on 10 secondary files(.ndf) in a single filegroup so that the data writing and reading will be faster?

    Hi yuvipoy,

    If I understand you correctly, by creating 10 ndf data files in the same filegroup you will have no control as to where the tables will sit within each of those files, SQL Server will spread out the data pages as required. To separate out tables you would need to make use of filegroups. I'm not saying this is good or bad, just unusual unless you have large tables that you know you can segregate out and/or maybe making use of table partitioning and/or using for backup purposes and/or for extreme performance issues or reasons.

    Paul Randal did a blog to do with multiple data diles (other than TempDB), see link below...

    PaulRandal_benchmarking-do-multiple-data-files-make-a-difference[/url]

    gbritton1 (4/25/2014)


    If you're I/O bound, start with the log files. Put them in separate filegroups, preferably on separate spindles.

    ...

    gbritton1,

    not sure what you're saying here, but if you are talking of transaction log files, they cannot participate in filegroups and the transactions cannot be separated by table, and it is very rare that you whould make use of multiple transaction logs files for a database. Again, Paul Randal has written a blog about why multiple transaction logs are not necessarily a good idea...

    PaulRandal_multiple-log-files-and-why-theyre-bad[/url]

    Hope this helps

  • spaghettidba

    SSC Guru

    Points: 105661

    yuvipoy (4/25/2014)


    Is it a good practise to have my tables in different files.

    Say i am having 10 master tables.

    Can i create tables each one on 10 secondary files(.ndf) in a single filegroup so that the data writing and reading will be faster?

    That will work only if you have separate physical disks. Multiple files in the same drive won't help performance.

  • Jeff Moden

    SSC Guru

    Points: 995608

    spaghettidba (4/26/2014)


    yuvipoy (4/25/2014)


    Is it a good practise to have my tables in different files.

    Say i am having 10 master tables.

    Can i create tables each one on 10 secondary files(.ndf) in a single filegroup so that the data writing and reading will be faster?

    That will work only if you have separate physical disks. Multiple files in the same drive won't help performance.

    +1000. Make that a million.

    Multiple files in the same drive could actually hurt performance with the possible exception of TempDB which is a different animal altogether.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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