Performance question : One single MDF file ? Or multiple MDF file ?

Dear,
My friend told me : using multiple MDF files is much better than use one
single MDF file even within the same hard disk as SQL Server's multi thread
feature can handle multiple files concurrently.
My argument : Within the same hard disk, we should use one single MDF file,
but we can split the MDF file into small files if there are different
physical hard disks.
How do you feel ?
In order to have better performance, we should use 1 or multiple MDF files
(I mean within the same hard disk) ?
Any Microsoft documents supported ?> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
This is an urban legend:
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx.
Also, there should only be one MDF per database, which is the primary data
file. The file extention NDF should be used for the secondary
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
Yes, it's best to distribute the workload over as many disks as possible.
For large data files, you might want to create multiple files even if on a
single physical disk for manageability.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||I would say that if you aren't seeing I/O issues (disk queue length, avg ms
per read/write, etc) with a single file then you can increase to more than
one file. Be aware however that when you do hit that break-over point and
have too many files, performance can decrease dramatically and quickly due
to disk head thrashing.
Assuming you have well-indexed structures, I would recommend first simply
moving all non-clustered indexes off to a separate file and see how that
works. You could also consider moving a very large table off to it's own
filegroup. But if you have that you really should be on multiple disks.
Also, what is your tempdb and transaction log file placement? If everything
is one same drive, separating those is job number 1.
BTW, you do have a baseline for your performance to compare to, right' :-))
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"cpchan" <cpchaney@.netvigator.com> wrote in message
news:4732f819$1@.127.0.0.1...
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi
> thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF
> file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||See also
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-should-i-create-for-a-user-database.aspx
Linchi
"cpchan" wrote:
> Dear,
> My friend told me : using multiple MDF files is much better than use one
> single MDF file even within the same hard disk as SQL Server's multi thread
> feature can handle multiple files concurrently.
> My argument : Within the same hard disk, we should use one single MDF file,
> but we can split the MDF file into small files if there are different
> physical hard disks.
> How do you feel ?
> In order to have better performance, we should use 1 or multiple MDF files
> (I mean within the same hard disk) ?
> Any Microsoft documents supported ?
>
>|||noted and thanks.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:45ECE387-9152-4E2A-8B07-31572C29152B@.microsoft.com...
> See also
>
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/29/how-many-data-files-
should-i-create-for-a-user-database.aspx
> Linchi
> "cpchan" wrote:
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
> >|||noted and thanks.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:5764EB3B-20BA-4663-8ADF-F7BDE6757654@.microsoft.com...
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> This is an urban legend:
>
http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-dis
cussed.aspx.
> Also, there should only be one MDF per database, which is the primary data
> file. The file extention NDF should be used for the secondary
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> Yes, it's best to distribute the workload over as many disks as possible.
> For large data files, you might want to create multiple files even if on a
> single physical disk for manageability.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
>|||noted and thanks.
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13j64cso0q4stec@.corp.supernews.com...
> I would say that if you aren't seeing I/O issues (disk queue length, avg
ms
> per read/write, etc) with a single file then you can increase to more than
> one file. Be aware however that when you do hit that break-over point and
> have too many files, performance can decrease dramatically and quickly due
> to disk head thrashing.
> Assuming you have well-indexed structures, I would recommend first simply
> moving all non-clustered indexes off to a separate file and see how that
> works. You could also consider moving a very large table off to it's own
> filegroup. But if you have that you really should be on multiple disks.
> Also, what is your tempdb and transaction log file placement? If
everything
> is one same drive, separating those is job number 1.
> BTW, you do have a baseline for your performance to compare to, right'
:-))
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "cpchan" <cpchaney@.netvigator.com> wrote in message
> news:4732f819$1@.127.0.0.1...
> > Dear,
> >
> > My friend told me : using multiple MDF files is much better than use one
> > single MDF file even within the same hard disk as SQL Server's multi
> > thread
> > feature can handle multiple files concurrently.
> >
> > My argument : Within the same hard disk, we should use one single MDF
> > file,
> > but we can split the MDF file into small files if there are different
> > physical hard disks.
> >
> > How do you feel ?
> >
> > In order to have better performance, we should use 1 or multiple MDF
files
> > (I mean within the same hard disk) ?
> >
> > Any Microsoft documents supported ?
> >
> >
> >
>sql