January 5, 2016 at 1:49 pm
Guys,
I have a requirement to create a database. I've two file groups DATA (default) & INDEX on separate disks. Is there any advantage of creating multiple files for the DATA file group(in the same disk or separate disks). Please share your thoughts.
Many thanks.
January 5, 2016 at 4:42 pm
What does your disk architecture look like?
What do the query load patterns look like for your server?
Would piecemeal recovery be beneficial to you?
Do you have your emergency restore scripts in order to deal with the different files?
...sometimes you just need to do what the vendor says to maintain their support.
January 5, 2016 at 9:42 pm
Having multiple file in a filegroup helps you to increase sql server performance in some secnarios. Take a look at this article may this this will help you: http://blog.idera.com/sql-server/performance-and-monitoring/increase-sql-server-performance-using-multiple-files/
January 6, 2016 at 2:09 am
On the same disk, none at all. On different disks, maybe. Are you seeing IO contention? If so, is the 'different disk' really different hardware? If yes to both, then you may get performance improvements.
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
January 6, 2016 at 2:36 am
GilaMonster (1/6/2016)
On the same disk, none at all.
... in most scenarios.
If you have a database that has a very high amount of table allocations and deallocations, you can get some contention on the GAM, SGAM or PFS pages. By using multiple files, you spread out the load and reduce that contention.
In practice, this usage pattern rarely happens in user database. It is however quite common in tempdb. That is the reason why most best practises guidelines tell you that you need to create multiple data files for tempdb (allthough opinions differ on what the best rule of thumb for the number of files is).
(Also note that using multiple files for tempdb is best combined with (a) making sure that all those files are the same size and have the same autogrow setting, and (b) enabling trace flag 1118 to ensure that they remain equal-sized. You might also want to enable trace flag 1117 to further optimze tempdb usage. Google for the details).
On different disks, maybe. Are you seeing IO contention? If so, is the 'different disk' really different hardware? If yes to both, then you may get performance improvements.
Absolutely agree here. If the database is constantly hitting the same file hard, then you can gain poerformance by ensuring that the same data is spread out over multiple physical devices.
January 6, 2016 at 6:15 am
Thanks everyone!
January 6, 2016 at 6:39 am
Hugo Kornelis (1/6/2016)
GilaMonster (1/6/2016)
On the same disk, none at all.... in most scenarios.
If you have a database that has a very high amount of table allocations and deallocations, you can get some contention on the GAM, SGAM or PFS pages. By using multiple files, you spread out the load and reduce that contention.
In practice, this usage pattern rarely happens in user database. It is however quite common in tempdb. That is the reason why most best practises guidelines tell you that you need to create multiple data files for tempdb (allthough opinions differ on what the best rule of thumb for the number of files is).
(Also note that using multiple files for tempdb is best combined with (a) making sure that all those files are the same size and have the same autogrow setting, and (b) enabling trace flag 1118 to ensure that they remain equal-sized. You might also want to enable trace flag 1117 to further optimze tempdb usage. Google for the details).
On different disks, maybe. Are you seeing IO contention? If so, is the 'different disk' really different hardware? If yes to both, then you may get performance improvements.
Absolutely agree here. If the database is constantly hitting the same file hard, then you can gain poerformance by ensuring that the same data is spread out over multiple physical devices.
Interesting, thanks Hugo.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 6, 2016 at 6:55 am
Hugo Kornelis (1/6/2016)
GilaMonster (1/6/2016)
On the same disk, none at all.... in most scenarios.
If you have a database that has a very high amount of table allocations and deallocations, you can get some contention on the GAM, SGAM or PFS pages. By using multiple files, you spread out the load and reduce that contention.
Don't think I've ever seen a user database manage to do that. I hate to think of the kind of app design/db design that would manage it. :sick:
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
January 6, 2016 at 6:56 am
Can I create just one file for now for a specific file group and create additional files if required in the future? Is this a good approach?
January 6, 2016 at 7:17 am
You can, but proportional fill will mean that the new file will get the bulk of new data. Trying to balance that out won't be trivial (rebuild all clustered indexes)
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
January 6, 2016 at 8:22 am
Here are some published results.
http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/[/url]
http://www.sqlskills.com/blogs/paul/benchmarking-multiple-data-files-on-ssds-plus-the-latest-fusion-io-driver/[/url]
But, don't create a problem trying to solve one you may not have. Do you own benchmarks in your own environment and make changes based on what's relevant to you.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply