SQL Server Performance Forum – Threads Archive
How many data files should i create for a 300 GB
Hallo there , I hope you can guide me in the following. Scenario: We are installing a new SAP system running Win2000 ADV server ,SQL 7.0 for now(upgrading @ the end of the year) and a 6 Terra Byte SAN of which 800GB is dedicated to the database. The servers are 4 x Xeon 2.8 MHZ possessors with 8GB Memory. The initial database will be +- 300GB. Question: I don#%92t know whether I should create 1 ,6 or 12 Data files for optimal performance : 1 x 300 GB data file or 6 x 50 GB data files etc. Thanking you in advance. PS. I read in a SQL Manuel that 6 data files are optimum but our architect recommend 1 data file. What is your recommendation? Hannes BassonSAP Basis Consultant
[email protected]
are you talking about file or filegroups?
i don’t see the point of multiple files in one filegroup with a SAN. there are several reasons to use multiple filegroups,
if performance for certain operations, backup etc are your concern,
try one file group per fibre channel link to the SAN, ie, if you have 6 single channel adapters or 3 dual channel adapters, distribute your tables and indexes across six filegroups
This is a Text from Inside SQL server 2000- Why Use Multiple Files? You might wonder what the reason would be for creating a database on multiple files located on one physical drive. There’s no performance benefit in doing so, but it gives you added flexibility in two important ways. First, if you need to restore a database from a backup because of a disk crash, the new database must contain the same number of files as the original. For example, if your original database consisted of one large 12-GB file, you would need to restore it to a database with one file of that size. If you don’t have another 12-GB drive immediately available, you cannot restore the database! If, however, you originally created the database on several smaller files, you have added flexibility during a restoration. You might be more likely to have several 4-GB drives available than one large 12-GB drive. Second, spreading the database onto multiple files, even on the same drive, gives you the flexibility of easily moving the database onto separate drives if you modify your hardware configuration in the future. Microsoft’s internal SAP system uses a SQL Server database created on 12 files. Microsoft has found that this provides the ultimate flexibility. They could separate the files into two groups of six, six groups of two, four groups of three, and so on, which would allow them to experiment with performance enhancements gained as files are spread over different numbers of physical drives.
Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Also at another instnace, it has been mentioned that having multiple files gives performance benefits when it comes to Read Ahead – There are two kinds of read ahead: one for table scans on heaps and one for index ranges. For table scans, the table’s allocation structures are consulted to read the table in disk order. Up to 32 extents (32 * 8 pages/extent * 8192 bytes/page = 2 MB) of read ahead are outstanding at a time. Four extents (32 pages) at a time are read with a single 256-KB scatter read. (Scatter-gather I/O was introduced in Windows NT 4, Service Pack 2, with the Win32 functions ReadFileScatter and WriteFileGather.) If the table is spread across multiple files in a file group, SQL Server has one read ahead thread per file. In SQL Server Standard Edition, each thread can still read up to 4 extents at a time from a file, and up to 32 files can be processed concurrently. This means that the read ahead threads can process up to 128 pages of data. In SQL Server Enterprise Edition, more extents can be read from each file, and more files can be processed concurrently. In fact there is no set upper limit to number of extents or number of files; SQL Server Enterprise Edition can read ahead enough data to fill 1 percent of the buffer pool. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
Multiple file groups can make sense for many reasons. One good reason is more threads processing IO. In a situation like SAP, where reads out number writes, this can be a good thing as long as the storage system is capable of handling the load. The disadvantage is fragmentation. You can overcome this by using multiple logicals on the same physical. John Fullbright
MOSMWNMTK
]]>