I am working with a customer that has a database around 6 gig in size. It is 10.1C Work Group running on a Windows 2003 server. The existing ST file has about a dozen storage areas. The areas are broken down by module (AR, AR, GL)
Indexes and Data for a module reside in the same storage area. I need to maintain this arrangement for now.
I have dumped the data and re-loaded it into an structure where each storage area is just a variable extent. This gives me a good idea of the current size of things. Right now nothing is over 2 gig. I suspect that at their current run rate that 2 of the areas will get there in a number of months. I expect to to set all areas up as fixed with 30% anticipated growth. A bit more for these 2 larger areas.
The database is 4K blocksize. On average, there are one or two larger sized files per area that would make me think that 32 records per block is appropriate. Mathematically, it might be 64 for a few of them.
Does this make any difference ? Also I am inclined to set the Blocks per Cluster to 8 for all but the 2 larger areas. For those I plan on using 64 as they tend to grow in spurts more than at a constant rate. Thoughts?
I have seen a fair amount written on both topics but usually it is with the Enterprise version in mind. Is it different for WG?
These guys are not there yet. Just looking to validate my thoughts and design given what we have. I expect this will need to last for less than a year before we upgrade and get to Enterprise.
Wow, where to begin? This is a pretty hefty collection of bad practices in one database, together with a 12-year-old unsupported OS and a 7-year-old unsupported OE version. And the DB is on the large size for Workgroup.
"The areas are broken down by module (AR, AR, GL)." In a database this small I'd be inclined to put all tables in one area and all indexes in another (and an area for LOBs if any), unless you expect one or two tables to suddenly start growing a lot. That gives you fewer HWMs to worry about monitoring. Separating by application module doesn't help you.
"Indexes and Data for a module reside in the same storage area. I need to maintain this arrangement for now." Why? The application doesn't know or care where objects reside physically (or it shouldn't...).
"On average, there are one or two larger sized files per area that would make me think that 32 records per block is appropriate. Mathematically, it might be 64 for a few of them. Does this make any difference ?"
Yes, if you set RPB too low for some of your data then blocks will be only partially filled and your caching efficiency will be reduced. In other words, more physical I/O than is ideally necessary and possible application performance impact. Is it enough for users to notice? Maybe not. But D&L is the time to make these changes, and there's no downside to making reasonable structure configuration decisions.
8 BPC is probably fine for this DB, but that depends on growth rate. A 6 GB DB that was 500 MB a year ago is not the same as a 6 GB DB that was 5.9 GB a year ago. And future business plans could alter the future rate from what it is today.
"I have seen a fair amount written on both topics but usually it is with the Enterprise version in mind. Is it different for WG? These guys are not there yet."
Does "not there yet" mean not ready to use Enterprise? If there's some reason other than money to pay for the more expensive licenses then it's probably not a valid reason. And really, they're not a lot more expensive.
With Workgroup, RPB and BPC recommendations would be the same, but extent choices are not. I'd make a single fixed extent and a variable for each area, large enough to hold the current data. Then you can track growth by watching the variable extent sizes. You have to monitor the growth as you don't have large file support.
Another difference is BI cluster size. Unlike Enterprise you want a value smaller than default, like 128 KB.
Try to get them to 64-bit Enterprise sooner rather than later, preferably on a modern OS and a supported OE release. Then you can enable large files and not have to watch extent sizes so closely.
You didn't mention AI. Hopefully because it's already enabled and being monitored. ;)
Thanks for your feedback.
The database structure and version of OE is dictated by the vendor. I am not the vendor in this particular instance. When invited to someone else's house, you play by their rules.
A server OS upgrade along with a move to Enterprise are on the table and being addressed. For all of our customers money/cost actually does factor in even if it's "not a lot more expensive". :-)
I appreciate your comment on the RPB impact on the physical IO. I agree it probably is not material in this specific case but something to consider in general.