Index Rebuild

midnight

Any idea on how long (estimate) an index rebuild would take on a 80gig NAV2009 SP1 DB. On a SQL2008 server with 16gig mem. The DB has never had this done to it before and we are experiencing performance issues. I'm looking to set up a maintenance plan to do this. I know this might be hard to answer, just looking for a ballpark figure. Does the rebuild chew up disk space, and what would be the repercussions if something were to go wrong.

Thanks

Marije_Brummel

There is not that much that can go wrong. I would guess it would take anywhere between one and two hours depending on hardware.

You can also reindex one table at a time or do the largest ones by hand first and then let the maintenance plan run the rest.

There are ton's of scripts online that are smart enough to look if the table actually needs reindexing and skip tables that are fine.

midnight

Great info Mark thanks. I'll set up an plan for tonight on our test DB, and see how that goes. Then I'll create one tomorrow for the live db. It's just taking way too long to do an item journal to adjust inventory. About 25 mins for one item.

midnight

oh, one more thing. I guess this has to be done while no one is accessing the system?

Marije_Brummel

Ah, you were the 25min guy. To hard to remember all the names.

Let us know how you go...

Marije_Brummel

midnight wrote:

oh, one more thing. I guess this has to be done while no one is accessing the system?

The table that is being indexed cannot be accessed (changed) by users.

midnight

You got it Mark, thanks again. I just put in a new barcoding system for NAV (went live today for a physical count), and the update to NAV uses item journals. Can't have this thing running for 3 days or more to upadate two thousand parts. I'll let you know how it goes.

bbrown

midnight wrote:

... Can't have this thing running for 3 days or more to upadate two thousand parts...

Is it taking 25 minutes to post 1 journal line? Or is that multiple lines?

davmac1

Do you have one item number with 2000 serial numbers?
Are you using average cost?

Large batches usually take much longer to post proportionally. Average costing on items with serial numbers adds another slowdown when there area lot of them. (Does not affect things when added at time of shipping.)

You should test times using different batch sizes and come up with a performance curve.
You may find that 2000 lines takes a lot more than 10 times longer than 200 lines.

If you get into a code that repeats calculations for each line for all lines, then the performance curve could be the square of the number of lines.

midnight

Ran an index rebuild on the test db Fri night. Checked it Sat morning, and holy crap the log file went to 55 gig on a 68 gig db. Not sure why this happened. Gonna have to truncate that sucker today. The items that were taking long to post do not have serial numbers and use std cost.

DenSter

Don't truncate, don't resize, but run a log backup, which will empty the log file. If you resize the log file, next time that you run a reindex it will grow again. Do that enough times and you will have a fragmented file. Better to keep it at the size that it needs to be, and run regular log backups.

midnight

Thanks for the info. I'm running log file backups regularly. This is on our test DB which is not really uesd that much. I don't think the log file needs to be 55 gig for a 68 gig DB. I would like to reclaim some of that disk space. It just took me by surprise that it grew to that size after an index rebuild.

DenSter

Suit yourself. Do you really need the space? Is it a big problem that the database has that size? Does it really matter all that much if the database is a bit smaller?

The more indexes, the more space they need. Your database is probably massively overindexed. I once took all the indexes off of an Item Ledger entry table just to see what would happen, and the size of the ILE table went from 75GB down to 12GB.