Physical setup: 1 data file vs multiple smaller data files

[Domenico Discepola]

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?

[Greg D. Moore \(Strider\)]

"Domenico Discepola" <jo******@yahoo .com> wrote in message
news:7O******** *************@n nrp1.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).

[Greg D. Moore \(Strider\)]

"Domenico Discepola" <jo******@yahoo .com> wrote in message
news:7O******** *************@n nrp1.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).