patternsqlMinor
Does SQL Server redistribute data after adding an additional data file?
Viewed 0 times
afterfilesqladdingdoesserverredistributedataadditional
Problem
Imagine a database about 30GB in size using 3 data files on the same disk.
Someone told me to have one data file per processor implemented to get full performance on all cpus. So if I now add three more data files, how's the data redistributed to the new files? Do I have to do something?
Someone told me to have one data file per processor implemented to get full performance on all cpus. So if I now add three more data files, how's the data redistributed to the new files? Do I have to do something?
Solution
The way that datafiles work, any new data will, mostly, get put in the new data file you added. Or in your case the two new data files.
However, SQL Server will not try to re balance your data. This would't make sense either as you will be causing a lot of IO / load on a database for, as far as SQL server knows, no reason.
You can however rebalance your data by recreating your indexes on a new filegroup while dropping the old indexes. The advantage being that you're allocating space in small chunks, getting unfragmented indexes and generally have more control.
You will however have to do this largely manually. And you'll probably want to create full backups before and after each operation.
However, SQL Server will not try to re balance your data. This would't make sense either as you will be causing a lot of IO / load on a database for, as far as SQL server knows, no reason.
You can however rebalance your data by recreating your indexes on a new filegroup while dropping the old indexes. The advantage being that you're allocating space in small chunks, getting unfragmented indexes and generally have more control.
You will however have to do this largely manually. And you'll probably want to create full backups before and after each operation.
Context
StackExchange Database Administrators Q#89146, answer score: 3
Revisions (0)
No revisions yet.