Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Impact of multiple user database files?


Impact of multiple user database files?

Author
Message
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47133 Visits: 44346
Jeff Moden (3/17/2014)
First, having the 80 files won't degrade performance. It won't enhance it, but it won't degrade it.


Too many data files can degrade performance. What too many is depends on a bunch of factors.
See http://www.sqlskills.com/blogs/paul/benchmarking-do-multiple-data-files-make-a-difference/, where adding files improved performance up to 8 files, then degraded it after that point.

It fact, it could reduce backup times if your automation checks to see if there have been inserts, updates, or deletes and backing up only those files that have actually experienced a change.


That's feasible with lots of filegroups, not so much with lots of files in a small number of filegroups. Can greatly complicate restores, especially if the backups are purely based on changes. Log backups will have to be retained for long periods (from the least recently backed up file right up to present) to allow for a restore.

It will also make dropping a customer easier in the future if the system was setup to tolerate just dropping a file.


If the database had been set up with a small number of files per filegroup and one customer per file group, then yes (kinda, still have to empty the filegroup before dropping the files), but in this case with 40 files per filegroup across two filegroups, that's not going to be the case.

Last but not least, if will also allow for "piecemeal" restores if that was a consideration in the grand design of things.


Again, if there were lots of filegroups, yes. Less useful when there's loads and loads of files in a single filegroup as all files in a filegroup have to be restored for the filegroup to be available.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44805 Visits: 39847
To be clear, my response was based on the OP's claim of having a single file per filegroup. I absolutely agree that having a shedload of files per filegroup can cause performance problems when there are too many.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47133 Visits: 44346
Jeff Moden (3/17/2014)
To be clear, my response was based on the OP's claim of having a single file per filegroup.


He corrected that statement in a later post

msmithson (3/14/2014)
Sorry I wasn't very clear. There are four file groups all residing on one RAID10 volume:

Primary: one file
Filegroup2: forty files
Filegroup3: forty files
Filegroup 4: one file


A small number of files per filegroup with multiple filegroups is useful for recovery, possibly backups, performance only to a point. Typically splitting to multiple filegroups for performance and splitting to multiple filegroups for recovery/restore options result in quite a different table-to-filegroup design. My personal preference is to split once it's been decided which one we're after, performance or restore/recovery options.


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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44805 Visits: 39847
Ah. That makes all the difference in the world. I also see where I missed two key words in the original post. Blush "per core".


Thanks for the feedback, Gail.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
     Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
EdVassie
EdVassie
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3140 Visits: 3815
One of the things you are doing with a large number of physical files is forcing SQL Server to use at least one IO stream per file (assuming all files are needed to answer your query). SQL may decide that your access plan would benefit from multiple IO streams to some or all of the files.

Therefore if your database is held in 80 files instead of 1, then you are forcing SQL Server to use at least 80 IO streams. This could be good for high-speed parallel access to data, but only if your storage subsystem can handle the load.

If you plot response time to % busy on your storage system, you are almost certain to find a hyperbolic curve, where response time looks OK up to maybe 95% busy but then deteriorates rapidly.

If you force too many IO streams into your subsystem, you may find one or more components get too near 100% busy. Your IO performance becomes far worse than if you had fewer IO streams and maybe 80% busy storage subsystem.

What is 'too many' will depend on your storage subsystem. It may be worth working with your storage expert to model the potential IO rate if no bottlenecks existed compared to the physical characteristics of the data transfer rate on your motherboard, NIC, and external storage subsystem capabilities.

You can then have a better idea of the maximum throughput possible with a given response time. From there you can look at either getting investment to improve the hardware to cope with the requested IO or spending time reducing the file count to match what the hardware can do.

Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search