Performance and installation tips

For a successful NAV implementation, performance is an important aspect to consider and to carefully monitor.

The following are the main NAV points to check in order to have better performance:

• Data tier (SQL Server Database)
• Service tier
• C/AL code

Data tier (SQL Server Database)

Microsoft Dynamics NAV relies on Microsoft SQL Server as its database, so recommendations on optimizing SQL Server performance for NAV are much the same as other types of data-intensive applications:

1. Split the database into more data files and locate them on different disks.
2. Every time a record is added, modified, or deleted from any table in the database, SQL Server updates all the indexes that are related to those tables. Check the indexes and re-build them periodically by using SQL Server Maintenance Plans. By rebuilding the indexes, you avoid fragmentation.
3. Rebuild the indexes and recalculate the statistics (this is automatically done when you rebuild an index).
4. Check your database Recovery Model and periodically shrink your transaction log.

SQL Server indexes are created on a column level in tables and views and they provide a quick way to retrieve data based on the values within the indexed columns. After frequent database operations (insert, update, delete) indexes can have pages where logical ordering (based on the key value) differs from the physical ordering inside the data file. This means that there is a high percentage of free space on index pages, and that SQL Server has to read higher number of pages when scanning each index. As a result, performances will suffer.

You can use this script to manually rebuild indexes on your NAV database:

    DECLARE @TableName varchar(255)  
 
    DECLARE TableCursor CURSOR FOR  
    SELECT table_name FROM information_schema.tables  
    WHERE table_type = 'base table'  
 
    OPEN TableCursor  
 
    FETCH NEXT FROM TableCursor INTO @TableName  
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
    DBCC DBREINDEX(@TableName,' ',90)  
    FETCH NEXT FROM TableCursor INTO @TableName  
    END  
 
    CLOSE TableCursor  
 
    DEALLOCATE TableCursor 

Note

For more information, check the MSDN site at https://msdn.microsoft.com/en-us/library/hh169233(v=nav.90).aspx.

Service tier 

Here is a summary of the most important things to check.

Number of Microsoft Dynamics NAV service tiers

If you have many users, you have to consider creating more than one NAV service tier and balance the user's connection between the different services. In my experience, the number of users for the service tier without impacting on performance is about 40. If the number of your users is more than 40, you should start thinking about using more than one service tier.

Server memory

Each Microsoft Dynamics NAV service tier needs about 500 MB of memory to run and a certain amount of memory for every active session (even if idle). The standard amount of memory to count is about 10 MB per session, but obviously the more pages a user opens, the more memory usage increases.

Server CPU - cores and speed

This could be obvious, but if you have a fast CPU you will have better performance. The more cores you have, the more things in parallel you can do (the NAV service tier is 64-bit and multicore enabled). Idle sessions on the Microsoft Dynamics NAV service tier don't use any CPU power.

Data cache size

This is a Microsoft Dynamics NAV server setting, which is located in the CustomSettings.config file:

    <add key="DataCacheSize" value="9" /> 

The number specified in the DataCacheSize setting determines how much memory is used for caching the data:

Value	Memory
9 (default)	512 MB
10	1 GB
11	2 GB
12	4 GB
13	8 GB
14	16 GB
15	32 GB

When running a single tenant system (classic installation) the default value of 9 is probably good but on a multitenant installation (where the cache is shared between tenants) this value could be increased.

Metadata provider cache size

This is a Microsoft Dynamics NAV server setting, which is located in the CustomSettings.config file. It sets the metadata provider cache size (the number of objects cached). A value of 0 means the cache is disabled:

    <add key="MetadataProviderCacheSize" value="150" /> 

In the three-tier environment, objects are cached in the service tier. The value of 150 is default one but in my experience this value is too low. You can try to up this parameter and monitor memory load on the server.

Maximum concurrent calls

This is a Microsoft Dynamics NAV server setting, located in the CustomSettings.config file, and it's the maximum number of concurrent client calls that can be active on the Microsoft Dynamics NAV server.

To disable this setting, set the value to MaxValue:

    <add key="MaxConcurrentCalls" value="40" /> 

The more cores in your server, the higher this value can be.

Maximum concurrent connections

This is a Microsoft Dynamics NAV server setting, which is located in the CustomSettings.config file, and it's the maximum number of concurrent client connections that the service tier will accept. To disable this setting, set the value to MaxValue:

    <add key="ClientServicesMaxConcurrentConnections" value="150" /> 

Note

For more information about monitoring the Microsoft Dynamics NAV Server using performance counters, check the MSDN site at https://msdn.microsoft.com/en-us/library/dn414713(v=nav.90).aspx.

C/AL performance

Obviously, the way you write code on NAV can seriously affect performance.

C/AL (the NAV native language) has a set of commands optimized for SQL Server data access and your code should consider these new instructions. Here are a few basic things to remember:

• Never use FIND('-') or FIND('+') but use FINDFIRST or FINDLAST instead (these are optimized for finding the single first or last record in the specified filter and range).
• Use GET when you have to retrieve a record via a primary key.
• When you want to retrieve a set of data or loop through it, use FINDSET. This function is optimized for finding and modifying sets of data, without creating cursors (and without using the FETCH commands called on SQL Server). However, this is only valid for the first 500 records (this number can be changed in the NAV database properties and can be increased if needed). After the default 500 records, the loop will still create a cursor, like in the old NAV versions. These are the general rules when using FINDSET:
  • FINDSET(FALSE,FALSE): This is a read-only command and uses no server cursors, and the record set is read with a single server call.
  •  FINDSET(TRUE,FALSE): This is used to update non-key fields. This uses a cursor with a fetch buffer (similar to FIND('-')).
  •  FINDSET(TRUE,TRUE): This is used to update key fields.

• To check if a set of records contains data after filters have been applied, use the ISEMPTY function.
• Avoid too many FlowFields on tables and pages.
• Use SETAUTOCALCFIELDS when you have to retrieve data and request a calculation of associated FlowFields.
• When you have to apply filters to a set of records, use the right index by using SETCURRENTKEY.

  Note

  For more information, check the MSDN site at https://msdn.microsoft.com/en-us/library/dd355237(v=nav.90).aspx.

• It is recommended to check also the C/AL Coding Guidelines published by the NAV Design Pattern Team at https://community.dynamics.com/nav/w/designpatterns/156.cal-coding-guidelines.
• You can perform C/AL performance testing by using tools such as the Microsoft Dynamics NAV performance testing repository in GitHub at https://github.com/NAVPERF. This is out of the scope of this book but it could be useful to bookmark the link.