[ QUOTE ]
Jeff,
I have heard some good things about data compression, we will have to look into that when we upgrade to 2008.
As for what we do to add 400 MB a day, we are a manufacturing company that uses almost all the modules, including MRP and forecasting. We just have a lot of transctions happening for a company our size. We have 90 users and most of them are adding transactions to the system.
We average 8,000 SO lines added daily which becomes 16,000 when they get copied to the custom reporting table we use for performance reasons. If you figure 4 lines in G/L per SO line we are adding 32,000 daily just for SO which does not include the couple hundred work orders processed daily.
The Sales Ledger also grows quickly. We purge Sales Ledger monthly, but 3 months is still over 6 million records.
Another factor is the growth of indexes from all the insertions. We get 3+ GB back (out of 251 GB currently) in space each month when we optimize the database.
The crazy part is the business is growing so the database is going to grow more quickly. For example, we are just launching an entirely new division which has huge potential.
At some point we are truly going to have to do something and I am doing what I can to stay ahead of it. That is why I appreciate you guys for giving me things to think about on options.
Jer
[/ QUOTE ]
Colin is correct below when he states that compression is just delaying the inevitable. Use the compression if you need a short-term fix until you figure out a long term solution that includes more storage and data-related fixes.
I would have the business take a look at business processes and perhaps find ways to eliminate or consolidate orders.
As for indexes and optimizations I have a couple of thoughts:
- I would be doing index rebuilds as often as possible given that your massive record adds are fragmenting the indexes pretty badly. Take a look at this to determine fragmentation, I'm guessing it's pretty bad -
http://jeffstevenson.karamazovgroup.com/2008/09/determine-index-fragmentation-in-all.html
- Page Splits must be through the roof if you are still using the default fill factor of 0. The bad thing about changing fill factors to something more in line with your needs is that it will increase the amount of storage space needed.
- Consider an reporting/offload server that is used for inquiries. RDBMS's do well as a read or write system and can function well if the split between read and write is not large. Yours is and merits a look at splitting the system somehow.
- Storage is pretty cheap. I think your organization is going to have to come to terms with the fact that their current business model and practices add a lot of data. Either change the practices or add storage. Cost of doing business with current practices.