locked
Adding mulitple MDF /NDF files to large databases RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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







    • Edited by ashwan Friday, August 14, 2020 2:07 AM
    Thursday, August 13, 2020 6:28 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi ashwan,

    >>How many data files will be good for 2TB database?(I assumed 4 in line with 4 cpus)?

    It is related to amount of read-write operations per second and your disk subsystem.

    Please refer to this thread  and this article which might help.

    >> DBCC SHRINKFILE (N'datafile1', EMPTYFILE) mean to empty the datafiles.

    EMPTYFILE migrates data from a specified file to other files in the same filegroup. EMPTYFILE assures you that no new data gets added to the file, despite this file not being read-only. If you use the ALTER DATABASE statement to change file size, the read-only flag is reset and data can be added.

    Please refer to DBCC SHRINKFILE for more details.

    >> need to match datafiles with number of cpus in the bos. 

    Please refer to this thread which might help.

    Best Regards,

    Amelia

    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.


    Friday, August 14, 2020 8:41 AM