Tables on diffrerent files

Question

Tables on diffrerent files

yuvipoy

SSChampion

Points: 10469
More actions
April 25, 2014 at 6:54 am

#292831

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?

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

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

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: 3540 More actions · Answer 2

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: 105732 More actions · Answer 3

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.

-- Gianluca Sartori

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 4

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.

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)