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
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:
Split the database into more data files and locate them on different disks.
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.
Rebuild the indexes and recalculate the statistics (this is automatically done when you rebuild an index).
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.
Here is a summary of the most important things to check.
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.
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.
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.
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 |
|
|
512 MB |
|
|
1 GB |
|
|
2 GB |
|
|
4 GB |
|
|
8 GB |
|
|
16 GB |
|
|
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.
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.
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.
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.
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('-') orFIND('+') but useFINDFIRSTorFINDLASTinstead (these are optimized for finding the single first or last record in the specified filter and range).Use
GETwhen 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 theFETCHcommands 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 usingFINDSET: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 toFIND('-')).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
ISEMPTYfunction.Avoid too many FlowFields on tables and pages.
Use
SETAUTOCALCFIELDSwhen 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.