August 31, 2011 at 11:56 am
I've been performance tuning our Dynamics AX ERP system which is on SQL 2008. We just completed an upgrade of the server infrastructure that helped about 5 to 20% overall with a couple exceptions over 300% improvement on batch jobs. The biggest thing we got from the upgrade was enough disk space to actually do all the reindexing and updating statistics that we can afford in our current maintenance window.
We made a much bigger jump in overall performance 200-600% on user level jobs once we determined which tables were actually the bad boys even though they weren't the highest in fragmentation. Our system is kind of odd because AX has thousands of tables and indexes, but two tables (and their indexes) make up 67% of our 200 GB database. We are working on an archival strategy for these, which should have a huge impact in a few months. The reindex on these two tables take a total of 4 hours. Right now we can handle that with our production being light in the US on Saturday afternoon and in China early Sun morning. But we may lose that window soon. Our basic performance is great, but we had experienced severe blocking when big utilities ran like MRP. With the upgrade and the cleanup, the mid-week blocking seems to be gone and we have a good global system now. But I've got to get a working maintenance plan that can survive when we get back to busy weekends.
I can reindex everything except the big two tables in about 2 hours and then update all statistics on Sunday which causes no blocking. I know what mid-sized tables make a difference now, and that has much more of an impact in reindexing these than the big two. If I get squeezed really hard, I believe I can minimalize the amount of indexing weekly, do a REORG of the big two when needed and negotiate a monthly or even quarterly 6 hour window for a full reindex.
I could also upgrade to Enterprise version of SQL, but the price tag seems high when we are so close to being able to manage the process as noted above. We haven't done a lot of REORG'ing so I don't know what the long term impact will be to our performance. In some tests, it has been able to bring the frag numbers down really well even when they were well over 30% (even up to 75%).
So questions -
Does this sound like a good plan or should I be considering something else?
Are there other performance benefits with SQL Ent version? I don't think we be able to do much with partitioning until AX supports it, but I am interested in the merry-go-round scan capability and wonder if it would help us.
Are there hidden negatives we should consider even if the frag_pct number stays low, that would make a REINDEX better than REORG? We will still be able to do the update_stats with FULL SCAN because it doesn't block even though it takes several hours.
Thanks for any advice,
Paul
August 31, 2011 at 1:22 pm
I am with you on being reluctant to shell out for Ent licenses, especially as you are working on an archiving process.
Is is necessary to reindex every index on the 2 big tables every week? If not, I would break the work up into cycles and spead the indexes over more than one weekend.
Secondly, I'm not an expert on the advanced scanning that comes in Ent version, but seeing as your database is only small-ish, and getting smaller with your archiving, I would suggest that you won't see any noticable benifit. My hunch is that this will be mostly beneficial for VLDB's. Online index rebuilding aside, I don't believe that there are any other performance features that will benefit you by upgrading.
Hope that helps!
August 31, 2011 at 6:03 pm
webtekkie (8/31/2011)
I am with you on being reluctant to shell out for Ent licenses, especially as you are working on an archiving process.Is is necessary to reindex every index on the 2 big tables every week? If not, I would break the work up into cycles and spead the indexes over more than one weekend.
Secondly, I'm not an expert on the advanced scanning that comes in Ent version, but seeing as your database is only small-ish, and getting smaller with your archiving, I would suggest that you won't see any noticable benifit. My hunch is that this will be mostly beneficial for VLDB's. Online index rebuilding aside, I don't believe that there are any other performance features that will benefit you by upgrading.
Hope that helps!
There is at least one performance benefit in Enterprise that can be quite beneficial. That is, in Enterprise index rebuilds are parallel operations - in Standard they are single (MAXDOP = 1).
What I would recommend is that you consider implementing a smart-reindexing scheme. Using either Ola Hallengrens scripts or SQL Fools (my preference) - you can reduce the number of indexes that are touched and it determines whether or not the index is reorganized or rebuilt.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply