Speed up data upgrade for NAV2013

Question

I'm doing a NAV2013 upgrade on a clients database which involves a lot of heavy SQL queries

Microsoft have "compressed" dimensions by normalising them in NAV2013 saving a lot of DB space, but the operation requires that a lot of data is checked and moved around in the database in order to build the new dimension sets

Running the upgrade process on a 50gig database for the client in question takes forever. To check timings I have done a mock upgrade on a backup of their current data. The server doing the upgrade is a new SQL server, pretty decent spec with 32gig of ram.

They want the upgrade done over a weekend but the upgrade dimensions query absolutely maxes out the processor for 2/3 days straight. I cannot alter the queries involved in the upgrade so the only alternative I can come up with is to somehow maximse the SQL performance externally

Is there anything I can do to the database in order to maximise the performance or efficiency of the work it is doing?

Since noone needs to use the SQL server yet, I can do whatever I want to it without it impacting anything but the upgrade, so I am completely open to turning off features that may tie up the processor or slow down query processing (I was thinking about minimising the amount of transaction logging SQL does but I've not looked into this yet)

When I'm in the office on Monday I'm going to speak to our performance guru, but I was wondering if there is something that I could try in the meantime.

Most performance optimising articles I have found talk about optimising the performance of queries using keys etc, in the context that the database is in use - this is different since it's a one time upgrade process and it only needs to happen once

Thanks in advance!

Answer 1

Upgrades classically take a long time. Particular from NAV 2009 to NAV 2013 as there were significant changes to the way Dimensions work.

The update routine has to generate new Dimension Set ID's to represent every combination of dimensions, and then tag the associated records accordingly. It's a lot of processing, but makes Dimensions faster and better suited for longevity.

Database

Assuming you can take a full backup before running the transfer, you could disable the FULL logging to say, SIMPLE. Though this will technically still log, it will manage space itself.

Making sure your database files are split out correctly is important for best performance (including master > tempdb).

Indexes & Keys

I'd guess that Microsoft would have significantly optimised the dimension tables (key wise). It was a big change to dimensions in NAV 2013. That being said, it might be worth disabling the indexes on the table (where possible) as that can impact write performance.

Upgrade Toolkit

The upgrade toolkit (from memory) actually executes raw SQL to transfer large data. If you're able to, it might be worth looking through the code of the upgrade toolkit to see whether you can run it directly in SQL yourself (though you mentioned you can't change the query?).

Jump into PartnerSource to see if there are any application hotfixes by Microsoft that might optimise the Upgrade Toolkit, though it's been out for several years so it may be quite settled.

Alternate Queries

Check on Mibuso (for example) to see if there are some user-written queries that might optimize the migration to NAV 2013, obviously take caution with these alternate scripts and test them thoroughly before running in LIVE.

Deleting Data

The core reason that the upgrade takes so long is that there is a heap of data to migrate, especially systems that are several years old. You could potentially delete the dimension data for entries older than a set period (e.g. 4 years), though this will mean the data can no longer be analysed with existing dimension-based reports, etc.