Multi data files, same drive/FG perf question

Hello. Say you have a SQL 7 database with 1 MDF and 3 NDF
files... all in the same filegroup and drive (and
folder). Beyond the question of WHY it's like that, other
then that's the way thigns were done in that area back in
the 6.5 device days... question...
Since SQL Server will split the data and indexes across
these 4 files evenly, I'd think things would perform
slower then if these were one file. Wouldn't this be a
type of fragmentation since the data/index is not in a
contigous disk area? THanks, BruceThe MDF is the Primary data file and the NDFs are Secondary files. There are
numerous reasons for this type of design. Performance, Securtiy.
1 example might be that you have some lookup tables that are all static, to
ensure nobody is able to change the data you can create a Read-Only data
file. All the lookup tables are then placed on the Read-Only data file
--
HTH
Ryan Waight, MCDBA, MCSE
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:5e2701c37d19$5668d0b0$a601280a@.phx.gbl...
> Hello. Say you have a SQL 7 database with 1 MDF and 3 NDF
> files... all in the same filegroup and drive (and
> folder). Beyond the question of WHY it's like that, other
> then that's the way thigns were done in that area back in
> the 6.5 device days... question...
> Since SQL Server will split the data and indexes across
> these 4 files evenly, I'd think things would perform
> slower then if these were one file. Wouldn't this be a
> type of fragmentation since the data/index is not in a
> contigous disk area? THanks, Bruce|||If the IO subsystem can handle the extra IO, it is possible for things to
run faster... SQL will automatically fire of separate, parallel IO threads
to handle queries, one for each of the data files... Even if you do not get
any performance improvement now, setting up multiple files in a filegroup
will allow you ( at a later time) install a new raid array, backup and
restore ( or sp+_attach) the database into the new raid array and get IO
parallelism...
So this setup could be the result of an *old way* thinker doing things the
6.5 way, OR a forward thinker planning for the future..
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:5e2701c37d19$5668d0b0$a601280a@.phx.gbl...
> Hello. Say you have a SQL 7 database with 1 MDF and 3 NDF
> files... all in the same filegroup and drive (and
> folder). Beyond the question of WHY it's like that, other
> then that's the way thigns were done in that area back in
> the 6.5 device days... question...
> Since SQL Server will split the data and indexes across
> these 4 files evenly, I'd think things would perform
> slower then if these were one file. Wouldn't this be a
> type of fragmentation since the data/index is not in a
> contigous disk area? THanks, Bruce