filegroup information
-
I have a database designed by a db2 dba that has over 70 filegroups. I am trying to convince them that this is excessive and more harmful than benefical to performance, but I need supporting documentation. There isn't enough in the BOL, can anyone point me to specific material illustrating the downsides of too many filegroups?
TIA,
Linda
-
Here are a couple of links you might check out:
http://www.sql-server-performance.com/filegroups.asp
and
http://www.sql-server-performance.com/ac_filegroup_performance.asp
There probably aren't any large penalties from so many filegroups until you run out of available threads to read them, but depending on how the disks are arranged, you may be bottlenecking the IO and not reaping enough benefit to justify the maintenance headache.
And then again, I might be wrong ...
David Webb -
Also, if you want to move objects off these excess groups later, here a good article from this site on how to do it efficiently.
And then again, I might be wrong ...
David Webb -
Thanks, I've seen those but was looking for a little more. The vendor is creating a series of 5 new tables each month with one filegroup for the table and one for the index. This thing is growing exponentially and this many filegroups is just crazy.
I guess I'll just keep looking...
Linda
-
The reason he/she is doing this because they are equating the performance gains that they can get by splitting the table and indexes onto different tablespaces in db2 to filegroups within sql server.
In db2 you can specify a different page and extent size for each tablespace and then split a table over several tablespaces depending on the type of data being stored so normally in a larger database you would split a table over three tablespaces one for regular data, one for indexes and one for BLOBs with a suitable page and extent size to ensure maximum performance.
Now for why he shouldn't in SQL Server.
In SQL server the page and extent size are set you can't change them. So there is no performance benefit to be gained.
In DB2 each tablespace is associated with a specific buffer pool of the same page size (that you've created) as the tablespace and you can then also specify the number of page cleaners needed for that buffer pool this can provide performance benefits. But in SQL Server you have no control over this as SQL Server rather sensibly tunes the memory for you so it sorts out its own buffer pool and takes care of writing stuff out to disk for you hence no performance benefit.
In DB2 for each tablespace you can specify the prefetch size which controls the amount of prefetching done there is no equivalent in SQL Server it carries out read aheads when it feels there is a performance benefit as far as I'm aware you have no control over this. Again SQL Server optimises itself you don't have to do it for it.
If i remember correctly also the size limit of a table space in DB2 is not that large whereas a filegroup sql server cetainly be at least 32tb.
The basic problem is that db2 dba has not understood that filegroups in sql server are not analogous to tablespaces in db2 in terms of how they should be used.
I hope this helps. If its a bit unclear the problem is you could write a sizeable document on how to choose tablespaces etc in db2 so I've tried to pick out why I think they are doing it.
David
-
How about the pretty easy argument that SQL Server does not allow more than 256 filegroups per database? That should tell them.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply