In this post I want to explain why to create multiple data files per SQL Server database
Note: this is only relevant for data files, not for log files. Log files always have one file!
Microsoft documentation says:
• The number of data files within a single filegroup should equal to the number of physical CPU cores (hyper threading cores should not be counted)
This is also true for TempDB
So create all the files in your new database with the same initial size – make an estimate on how big you want the files to be- and set a reasonable growth factor. Do not set it to a percentage, but on the same fixed size in MB for all data files. Use the same growth factor for all the files you have created.
SQL Server will fill the data files in a round robin way, meaning that data is spread equally across all the files as the database grows.
Tip!
Another advantage from this approach is that if you have performance issues and you can pin this down to an disk I/O related problem
(see my http://sqlserveradvisor.blogspot.com/2009/02/microsoft-included-fnvirtualfilestats.html on fn_virtualfilestats() post for this), it is then an easy change to add a new raid disk configuration to your server and then spread the data files evenly over both the raid arrays.
Giving you an instant performance boost!!
