Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Impact of multiple user database files? Expand / Collapse
Author
Message
Posted Monday, March 17, 2014 9:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 42,774, Visits: 35,872
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 2008, MVP
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

Post #1551794
Posted Monday, March 17, 2014 9:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 36,959, Visits: 31,472
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1551811
Posted Monday, March 17, 2014 9:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 42,774, Visits: 35,872
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 2008, MVP
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

Post #1551814
Posted Monday, March 17, 2014 10:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 36,959, Visits: 31,472
Ah. That makes all the difference in the world. I also see where I missed two key words in the original post. "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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1551827
Posted Tuesday, March 18, 2014 5:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:18 AM
Points: 2,869, Visits: 3,210
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 2014, 2012, 2008 R2, 2008 and 2005. 28 July 2014: now over 30,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Concept: "Pizza Apartheid" - the discrimination that separates those who earn enough in one day to buy a pizza if they want one, from those who can not.
Post #1552115
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse