December 22, 2014 at 1:47 pm
(SQL 2012 Standard)
I have a database with one 320 .mdf file and one 200GB .ndf file.
I would like to reconfigure my database to have four 200GB .mdf files. How do I get from here to there?
Thanks,
Kevin
December 23, 2014 at 1:42 am
First things first.
Why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 23, 2014 at 7:06 pm
It is my understanding that is a best-practice. To have multiple data files which are the same size. Performance should be better, right?
December 24, 2014 at 7:17 am
Do you want these 4 files to share the same filegroup or to have dedicated filegroups?
For the former just add 3 more files to a primary or another filegroup that you have.
For the latter :
1. Create 4 filegroups
2. Create 4 files mapping 1-1 to filegroups
3. Re-create clustered (and optionally non-clustered, depending on your strategy) indexes on new FG, use ON ... clause for this.
Gain in performance can be achieved only if you spread them on separate physical spindles and make research and calculations of I/O usage statistics beforehand.
December 25, 2014 at 11:14 am
Firstly, are both files in the same filegroup??
sextonk (12/23/2014)
It is my understanding that is a best-practice. To have multiple data files which are the same size. Performance should be better, right?
It's amazing how this myth just keeps getting misconstrued and twisted out of all proportion!!
No, that's actually a best practice to do with TempDB only, designed to overcome allocation contention.
Allocating multiple files in the same filegroup will have some overhead due to the proportional fill algorithm which has to manage the file rates and ultimately the file sizes.
What is it you are trying to achieve, it may be more pertinent to create multiple filegroups and spread objects between said filegroups, but don't just rush into this
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
December 25, 2014 at 2:38 pm
sextonk (12/23/2014)
It is my understanding that is a best-practice. To have multiple data files which are the same size.
It is, for TempDB and only for TempDB. It is not, and never has been, a recommendation for user databases.
Deciding on file splits for user databases are far more complex and should not be done without first deciding why (performance or partial backup/restore are the usual reasons), testing to ensure that the design meets the requirements and doing sufficient monitoring and analysis to ensure that the changes are necessary in the first place.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply