By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,383 Members | 2,063 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,383 IT Pros & Developers. It's quick & easy.

Physical setup: 1 data file vs multiple smaller data files

P: n/a
Hello all. Before my arrival at my current employer, our consultants
physically set up our MSSQL 7 server as follows:
drive c: contains the mssql engine
drive d: contains the transaction log
drive e: contains the data files

No filegroups were set up and the data files consist of only 1 large
physical file. Currently, our data file is >10GB. When I was trained on
the physical aspects of sqlserver, I was told to never create physical files
2048MB each. If I did, I could expect inefficient physical storage of

data and slower performance (due to the OS).

Our server has 2 RAID-5 arrays. Drive c: and e: are located on the first
array and drive d: on the second. We're running Windows 4.0 NT Server SP6
with NTFS.

Can someone comment on the use of 1 single large data file vs. more smaller
data files?
Jul 20 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"Domenico Discepola" <jo******@yahoo.com> wrote in message
news:7O*********************@nnrp1.uunet.ca...
Hello all. Before my arrival at my current employer, our consultants
physically set up our MSSQL 7 server as follows:
drive c: contains the mssql engine
drive d: contains the transaction log
drive e: contains the data files

No filegroups were set up and the data files consist of only 1 large
physical file. Currently, our data file is >10GB. When I was trained on
the physical aspects of sqlserver, I was told to never create physical files
2048MB each. If I did, I could expect inefficient physical storage of

data and slower performance (due to the OS).


I've never heard that, can anyone else comment?

Prior to SQL 7.0 there was some performance gain from multiple files since
there would be one (and only one) pointer moving within a file, so multiple
files would gain you performance with multiple file pointers. But I believe
that was resolved as of SQL 7.0

Our server has 2 RAID-5 arrays. Drive c: and e: are located on the first
array and drive d: on the second. We're running Windows 4.0 NT Server SP6
with NTFS.

Can someone comment on the use of 1 single large data file vs. more smaller data files?
They did the biggest bang, which was putting their data files and logs on
separate physical devices (though I'd recommend RAID 1 or 1+0 for the logs).


Jul 20 '05 #2

P: n/a

"Domenico Discepola" <jo******@yahoo.com> wrote in message
news:7O*********************@nnrp1.uunet.ca...
Hello all. Before my arrival at my current employer, our consultants
physically set up our MSSQL 7 server as follows:
drive c: contains the mssql engine
drive d: contains the transaction log
drive e: contains the data files

No filegroups were set up and the data files consist of only 1 large
physical file. Currently, our data file is >10GB. When I was trained on
the physical aspects of sqlserver, I was told to never create physical files
2048MB each. If I did, I could expect inefficient physical storage of

data and slower performance (due to the OS).


I've never heard that, can anyone else comment?

Prior to SQL 7.0 there was some performance gain from multiple files since
there would be one (and only one) pointer moving within a file, so multiple
files would gain you performance with multiple file pointers. But I believe
that was resolved as of SQL 7.0

Our server has 2 RAID-5 arrays. Drive c: and e: are located on the first
array and drive d: on the second. We're running Windows 4.0 NT Server SP6
with NTFS.

Can someone comment on the use of 1 single large data file vs. more smaller data files?
They did the biggest bang, which was putting their data files and logs on
separate physical devices (though I'd recommend RAID 1 or 1+0 for the logs).


Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.