Hello, I have a database on sql server 2008 R2. It's for SAP. The size is : 719 601 Mb. At this moment, i have 5 datafiles. Can you say to me if it's enough or not ? How can i calculate the number of datfiles ? Best regards O.Allain
The number of datafiles doesn't have that much to do with the size of the database, but more with the architecture of the storage. I have heard different recommendations about number of database files. One recommendation is to use as many datafiles as you have CPUs (or cores if you wish) in your server. Another is to use as many files as you have disks in your RAID5-array (if using RAID5). Perhaps some ninja DBA can answer why one or the other option is "best". But for a big database, I would look more at seperating objects to different physical storages (disks). If you are reading a lot from one table and writing a lot to another table at the same time, you may gain performance by creating two different filegroups, using different disks for these objects, so that heavy writing doesn't cause IO waits for the reading. As far as I'm concerned, I can't say that there is one single "rule" to follow when it comes to number of files. It all depends on how the data is distributed and how it's used. If you have a large database, where most data is never touched (archived, old production/order/invoice data), and a small portion of data which is heavily used, you probably want to move the archive data to cheap disks and put the transaction heavy data onto faster disks, like SSD. To summarize my answer: It depends.
Do all of your datafiles belong to the Primary file group? Are these files on different disks? For databases under a couple of hundred gigabytes SQL Server can ususally run with a single file quite comfortably. As @Magnus Ahlkvist has said, if you have different file groups and/or disks supporting different logical objects within your dB you may see benefits. The most common is DATA on one and INDEXES on another. Observe your disk latencies for reads/writes and see if they are within tolerance of expectations. You may find that regular index & statistics maintenance will provide all the benefit you require. It can be a big subject but you can't base it on a simple number without testing. Throwing more files on doesn't necessarily provide any benefit and may actually decrease performance if not thought out/implemented well. As I said at the outset, large databases can run on a single file - and some small ones need a designed approach to maintain reasonable I/O.