After Microsoft Dynamics NAV 2009 SP1 release more and more developers start using it and trying to adopt existing solutions for new 3tier environment. Most workload comes from trying to adopt current... (Continue reading)

The query below combines these three queries into one: Index Usage Query Recent Bocking History Table Information Query
It can be used to just see the number of records in each table. But also by... (Continue reading)

The NAV 2009 documentation walkthroughs provide step-by-step instructions for installing NAV 2009 on 2 or 3 machines. However, we have found that some of the same configuration issues come up time after... (Continue reading)

A new translator service is now available for all whose browser is set to one of the following languages:
English, German, French, Italian, Spanish, Portuguese, Netherlands Dutch, Russian, Chinese Simplified... (Continue reading)

Attached at the end of this post is a set of NAV objects that collects index usage information, and displays it in a NAV client so that you can sort tables by no. of Indexes / Index Reads / (Index) Block... (Continue reading)

Upgrading Dynamics NAV (database upgrade) is a timely task, but the time to upgrade (transfer data) can be somewhat reduced on sql option by taking few simple steps.

Due to large transaction volume and amount of indexes and sifts, transfering data (running upgrade toolkit codeunits: step 1 and 2 ) can be a long process.To reduce this time, it might be beneficial to disable maintaining sql index (and maintaining sift index) for all but primary and clustered keys, and keys used by upgrade toolkit. Following procedure might help boosting performance while transfering data.

1. As a first step, table objects should be exported so the index structure can be reimplemented when upgrade is finished. Note, in 5.0 SP1 this will cause rebuild of indexed views and might take some time on large systems. In 5.0 SP1, this should be done outside office hours.

2. Search the objects used by upgrade toolkit for all occurences of SETCURRENTKEY in code. Note down tables and keys where SETCURRENTKEY is used. Normally, there are only a few keys (other then primary keys) used in upgrade toolkit. These keys and primary keys (also keys with clustered indexes) should be left with maintainsqlindex set to yes. For all others, this property can be unchecked.

3. Then manually (or by automatizing the process) uncheck MaintainSlqINdex and Maintain SIFT Index for all but primary(clustered) keys and keys noted down in step 2. This might take considerable time on large systems as it will drop most indexes and sift tables, but it could be run as an unmonitored overnight process.

The following example illustrates how the process could be automatized. You might need a solution developer granule for this scenario.

When running form, select all indexes you want to have MaintainSQLIndex and MaintainSIFTIndex property set to false (as an example, select all and then unselect indexes used by upgrade tollkit, collected in step 2). From menu select Edit and Toggle Mark. This will mark all selected records. Click on the created button to set the properties MaintainSQLIndex and MaintainSIFTIndex of the record selection to FALSE.

4. Run the codeunits in upgrade toolkit. Remember to recompile upgrade toolkit objects before upgrading. As only primary keys and keys used by upgrade toolkit are now maintained, running the codeunits for data transfer should now run faster.

5. When data transfer is finished, reimport table objects exported in step 1 to restore original index structure . This will rebuild indexes and sift tables, and might take considerable time, but can be run as an unmonitored overnight process.

Note that the process might be further optimized by modifying some of the keys used in upgrade toolkit. Instead of using best matching keys among existing ones (as done in some triggers), one could create keys that match the filtering exactly, and remove them after the upgrade process is finished, but this might only be worth while on very large tables.

Jasminka Vukovic

Microsoft Dynamics NO

Microsoft Customer Service and Support (CSS) EMEA

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.