We are provisioning a database which is expected to growth rate 2 TB per year. Sql server 2016 SP2 Enterprise
question related to creating multiple files with in primary file group to re-balance the data allocation to better performance of single file and adding multiple data files after grow 500GB). initially start with 4 vcpus and 128G memory. Current
storage with Netapps storage given volumes created as D : X: S; etc
1. How many data files will be good for 2TB database?(I assumed 4 in line with 4 cpus)?
2. Its advice to create multiple data files at the begging of the database creation or later.
3. DBCC SHRINKFILE (N'datafile1' , EMPTYFILE) mean to empty the datafiles . so if we do that to .MDF file which is originally created ( not for any NDF) , then the file will make zero data. Then by leaving datafile(MDF) the new data fill add to the
empty MDF file again ?
3. Is that to be considered uniform / fix/mix extends in mdf level
Increase the cpus later 4 to 16 . Do we need to match datafiles with number of cpus in the bos.
furthermore allocation with mix extend sizes will that matter on the performance?.
thanks your for the advice
regards
Ashwan