May 11, 2011 at 9:22 pm
Comments posted to this topic are about the item Learn to Use Filegroups
May 12, 2011 at 6:22 am
One of the databases I support uses a large number of file groups to seperate different types of data. Base tables are seperated among many of them and several are reserved for the indexes of large tables. What this allows is for us to seperate the data files out onto several different sets of spindles or even completely seperate storage hardware. This allows for much higher performance and throughput. I have seen this return much better results than having a single raid set with 12 or more spindles. It also does allow for putting a Raid 1+0, Raid 1 or a Raid 5 behind differing performance need file groups.
The biggest issue I have noticed with storage is the fact that disks are to large to get adequate spindles for performance needs of many systems. a 500GB database that needs two raid sets will either be using 4 300GB SAS drives in a mirror or more as a R5 or 1+0. The wasted space often is hard to validate for a penny pinching customer so finding the best way to deploy can be a challenge.
May 12, 2011 at 7:52 am
First, let me say I greatly appreciate the content presented on SQLServerCentral.com. As a developer/architect/dba/analyst in a small shop I learn something useful every day from the site.
Great topic for discussion. I have often wondered whether I am foregoing any significant performance benefit by keeping all of my data and logs in the default filegroups. Our SQL Server (2008 R2 Enterprise) runs three 'large' (well, large to us anyhoo) databases (OLTP = 10GB, data warehouse = 20GB, blob storage = 20GB). The OLTP db has two hot tables (read-write on one, all writes on the other (log)). We usually have 15-25 users connected at any given time, mostly to the OLTP db. We are not partitioning any tables at this point. Is it to my significant advantage to move at least the two hot tables to separate filegroups/spindles, if possible? Is the answer "It depends?" I would love to hear opinions on my specific scenario as well as general guidelines.
May 12, 2011 at 8:01 am
This is why I encourage so many people to take a class on SQL Server. As an instructor, I was always explaining feature sets to experienced veterans. I take my awareness of these features a little for granted sometimes. Partitioning tables over filegroups has helped many of my students with performance issues, although they don't like the "wasted" disk space..
Of course, those same veterans would probably do a better job of designing and deploying those features once they know 🙂 That's why I am back in the field now, honing those hands on skills around those features... great to be back in the saddle!!! Theory is great, but nothing beats having your fingers in the guts of the beast!
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
May 12, 2011 at 8:06 am
deanroush (5/12/2011)
Our SQL Server (2008 R2 Enterprise) runs three 'large' (well, large to us anyhoo) databases (OLTP = 10GB, data warehouse = 20GB, blob storage = 20GB).
What I wouldn't give to have my customers put in enterprise edition for our 1TB systems. And we don't even store Blob data in the db :w00t:
May 12, 2011 at 8:35 am
In SQL Server, we can't split a table across filegroups. Or can we?
I was just asked that question! The idea that was being tossed around was to split a transactional table over 2 file groups, 1 of them being a read-only file group, and having the transactional data that was 'completed' being moved to the read-only filegroup while the transactional data that was still being processed be available on the other filegroup.
I said I didn't know but could look into the possibility. I also suggested that there are other ways in which to protect data. Can you point me to some articles on splitting tables across file groups?
May 12, 2011 at 9:21 am
Alan Vogan (5/12/2011)
In SQL Server, we can't split a table across filegroups. Or can we?
I was just asked that question! The idea that was being tossed around was to split a transactional table over 2 file groups, 1 of them being a read-only file group, and having the transactional data that was 'completed' being moved to the read-only filegroup while the transactional data that was still being processed be available on the other filegroup.
I said I didn't know but could look into the possibility. I also suggested that there are other ways in which to protect data. Can you point me to some articles on splitting tables across file groups?
You can't split a table per se across filegroups. If you have partitioning (EE), you can partition in different filegroups.
What I might suggest is that you consider partitioned views, where you have two tables joined with a view and one is a read-only filegroup. That can reduce your backup time/windows as well if the "old data" doesn't change.
May 12, 2011 at 9:24 am
deanroush (5/12/2011)
First, let me say I greatly appreciate the content presented on SQLServerCentral.com. As a developer/architect/dba/analyst in a small shop I learn something useful every day from the site.Great topic for discussion. I have often wondered whether I am foregoing any significant performance benefit by keeping all of my data and logs in the default filegroups. Our SQL Server (2008 R2 Enterprise) runs three 'large' (well, large to us anyhoo) databases (OLTP = 10GB, data warehouse = 20GB, blob storage = 20GB). The OLTP db has two hot tables (read-write on one, all writes on the other (log)). We usually have 15-25 users connected at any given time, mostly to the OLTP db. We are not partitioning any tables at this point. Is it to my significant advantage to move at least the two hot tables to separate filegroups/spindles, if possible? Is the answer "It depends?" I would love to hear opinions on my specific scenario as well as general guidelines.
It depends is true. If you have delays or waits on this filegroup (check virtual file stats) then you might get some performance improvement from moving this to separate spindles. The alternative is to examine the storage subsystem. Is it R1? R5, R10? Might switch there or add spindles. The other thing to think about is adding more memory instead.
May 12, 2011 at 9:43 am
deanroush (5/12/2011)
I have often wondered whether I am foregoing any significant performance benefit by keeping all of my data and logs in the default filegroups.
Just to make sure we're not assuming anything about your setup, if you have the MDF and LDF on the same drive that can be split to two different drives which will help with performance. And recoverability if only one of the two drives goes.
Always keep in mind that there are multiple ways to tackle any problem and splitting file groups may not be the best solution. If you don't have the optimal storage subsystem configuration (or hardware) it'll be easier to tackle that to get the performance gain you're looking for, or at least some of the gain you're looking for.
May 12, 2011 at 2:13 pm
I'm wondering just what the benefit is to filegroups when your data is stored on a SAN or NetApp appliance? I believe I read something about a benefit when restoring a corrupt database, but otherwise are there benefits?
May 12, 2011 at 2:17 pm
There are benefits however SAN technology can sometimes still make them moot in my opinion. It all comes down to configuration. If you use a single path to the SAN and use 1 large raid 5 of ALL the disks with multiple luns then no, I don't see a benefit from a performance standpoint.
If however you are able to segregate IO to different controllers|paths|Raidsets then yes you could see a performance benefit. This was much more noticeable on older SAN hardware which did not have the massive cache and controller performance of today's SANs however depending on the needs can still be noticed.
We still even in a SAN environment will ask for 4 physically seperated containers. I would love to have a sandbox to test some ideas of mine with streamlining and where critical mass exists but have not had that opportunity.
May 16, 2011 at 10:19 am
I like using filegroups. I think there is great merit to learning how to use filegroups and how to gauge when it is appropriate or not to use them.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 13, 2015 at 6:55 am
I use filegroups with my large OLAP database to group the tables by data source. Especially as one source loads only once per month, it allows me to load it, DBCC check it, and forget about it. It may become impossible to DBCC check a large database, but if broken into filegroups they can be checked individually.
A downside I've seen is that when I adopted this method I had to stop putting my indexes in a different file group because of the restore issues.
October 13, 2015 at 7:11 am
don.schaeffer (5/12/2011)
I'm wondering just what the benefit is to filegroups when your data is stored on a SAN or NetApp appliance? I believe I read something about a benefit when restoring a corrupt database, but otherwise are there benefits?
I keep meaning to write a nice complex article on splitting across filegroups, looking at splitting for performance and splitting for recoverability. I keep not getting around to it. Maybe someday.
Short answer to your question is 'maybe' 🙂
If all the filegroups are on LUNs from the same disk pool, using the same storage path, then probably you won't see a performance benefit. Recoverability-wise, it's about whether you can restore sub-sets of the database in case of either corruption or a complete DB loss. That kind of filegroup split tends to need to be carefully designed to get the benefit.
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
October 13, 2015 at 10:32 am
Just wanted to point out that you can still use partitioned views across multiple filegroups without enterprise edition.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply