Microsoft Dynamics introduced a feature inclined towards redesigning data access layers between Dynamics NAV server Tier and SQL Server RDBMS. The reason for doing so, it seems, is to adjust to ADO.NET interface & support enhanced features within SQL Server. Let’s see some of the features introduced with changes:

  • Reduced Resource Consumption: Earlier versions of Microsoft Dynamics NAV were the one, where systems would create a connection on their own with the SQL Server for every individual end-user in the NAV system. Approximately 40MB of memory was consumed for every such connection in the SQL server. However; the new version of NAV now utilizes connection pooling, a concept developed to limit the count of connections to the SQL server. To do so it reuses the same connection by user.
  • Caching: Existing CATCHING system has been replaced by two new type of cache’s a Global Cache and a Private cache. The settings and behavior of caching can be altered by updating “Data Cache Size” in the service tier configuration file. Synergy between multiple services tiers also. The setting and behavior of the caching can be modified by updating the Data Cache Size setting in the service tier configuration file. Synchronization between multiple service tiers can be made and changed by defining the desired interval in the “Cache Synchronization Period value” in service tier configuration file.
  • Multiple Active Results Sets (MARS): this was one of the prime features with SQL Server 2005 to ease some of the challenges faced by developers with connection pooling. It also happens to be a default processing mode for SQL Server data access API’s. Evolved technology also helps you with one plus pending requests under a SQL connection. It is also faster as compared to server-side cursors for a lot of operations that take place all within the SQL Server environment.
  • Performance: – Apart from other improvements, performance around SIFT indexes (Sum Index Flow Technology) is also the one that is been paid attention to:

    • COUNT and AVERAGE formulas use SIFT indexes
    • MIN and MAX formulas are directly mapped to SQL Server MIN/MAX functions
    • RECORDID and VARIANT data types on the table no longer prohibits BULK Insert functionality
    • FlowFields are improved to perform single statement and not a statement for every individually filtered FlowField & every record in the table.

These were the benefits that are been catered to new customers, however; not to forget that some of the drawbacks that are faced by existing users. Some of the features that formed the integral part of previous NAV versions but no longer exist.

  • Login Stored Procedure: A login store procedure (sp_$ndo$loginproc] available only to developers to perform customized functions when a user log’s into the Dynamics NAV environment. This particular function works fine in the development environment, but unfortunately done not support NAV 2013R2+ service tier where users in the RoleTailored Client are not allowed to utilize this feature.
  • Index Hinting: This capability available in all previous versions of NAV has been disabled – one can no longer specify a precise index to use within the application.
  • Performance Analysis: Use of Connection Pooling does not allow Performance analysis of connections in the environment and does not map one-to-one to users. This has invited a situation where, in order to comprehend a transaction in the system; the user compulsorily needs to either be isolated into another environment for the test to be run, allowing the SQL Profiler to catch the transaction information. There is also an option available in the new debugger called “Start Full SQL Tracing” that gives additional information to each query that SQL Profiler can then catch – this information can then be saved into a table for further analysis.

These changes of Dynamics NAV system has witnessed considerably enhanced performance as compared to NAV 2009. This technology has evolved in a refined manner in its newer avatar of Microsoft Dynamics NAV 2015. If you are one of those businesses or entrepreneur using a version prior to NAV 2013 R2, I would strongly recommend you to speak to your software development partner and find out more about these changes and make them work in your favor.