question

oallain avatar image
oallain asked

Define number of datafiles

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
datafiles
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
0 Likes 0 ·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DenisT avatar image
DenisT answered
Paul has a good blog about it -- [Benchmarking: do multiple data files make a difference?][1] and Jes @ Brent Ozar has a good video about the Filegroups -- [How SQL Server Stuff Works: Filegroups (Video)][2]. I agree with both of the answers but just keep in mind for the "The most common is DATA on one and INDEXES on another" design that @Blackhawk-17 pointed out that if the INDEXES filegroup is offline -- INSERTs, UPDATEs, and DELETEs will fail on the DATA filegroup. [1]: http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/ [2]: http://www.brentozar.com/archive/2012/10/how-sql-server-stuff-works-filegroups-video/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.