Size of MDF and LDF files
I was wondering if the recommended maximum size for MDF and / or LDF files was for a SQL server instance.
For example, if I want to create a 400 GB database, is there a rule to help me decide how many mdf files I should create? or should I just go ahead and create one giant 400GB PDF file?
If this will affect the performance of the database?
a source to share
What you do will depend on your disk system. You need to figure out what transactions your application will perform and configure your disks to handle those transactions. The I / O system is the bottleneck in most systems, so it will definitely have an impact on performance. Isolate serial I / O and distribute random I / O.
Some guidelines from the book SQL 2000 Tuning:
Isolate the transaction log on your own RAID 1 or RAID 10 drive.
Configure enough disks in your RAID array or partition the database into filegroups on separate disks so that you can store volumes at less than 125 IOPS (this number may be outdated).
Configure datafile volumes as RAID 5 if transactions are expected to be mostly read.
Configure data volumes as RAID 10 if more than 10% write is expected.
a source to share