Microsoft Dynamics NAV Sustained Engineering Team Blog

The random events

 Some years ago I was dealing with an issue that was driving me crazy and it took many hours to figure out what was wrong with the code. It was until I was explaining my findings to a colleague (don't you solve lots of things this way?) that it hit me, and when looking at the original (NAS) code I confirmed what was wrong.

What was more irritating was that the system was working most of the time, and, of course, there was no way to consistently reproduce the issue.

I will explain the issue here, hoping that people don't make the same mistake again.

The problem

Think about a single instance code unit which is receiving an event in order to process the incoming data (this was using the ComCom objects), and, in order to catch possible errors in the AL execution, a NAV Timer (Navision Timer) is used to reply successfully or with an error to the receiving event initiator (the COM component that started the conversation).

Normal flow (from the NAS perspective) was:

• ComCom2::MessageReceived would be invoked.
• GlobalVal, a global variable in the single instance Codeunit would be filled with the incoming data to be processed.
• NTimer would be enabled, in order to fire relatively soon (I think it was something like 20 milliseconds).
• NTimer::Timer would fire and would get the value from GlobalVal to process the data and reply to the connection (by NAV architecture, this connection had to be a new one, as, when the MessageReceived event finishes, the ComCom connection drops).
• If any AL error would arise within the NTimer event, the NTimer::TimerError would be triggered, which would allow us to reply an error to the calling component (using the GlobalVal to identify which connection would have to be informed about the problem).

Sounds reasonable, and in most cases this would work great. However, people were experiencing mixed up answers especially when errors were to be reported.

Of course, my first thought was that the component connecting to the NAS was the guilty one, as, after all, it is a multi-thread, multi-connection component (it runs as a Windows Service), and getting all this threads straight is always a tricky business. However, after inspecting the components code, and correcting some issues, the problem was still there.

I decided it was time to get the hands really dirty and started making my own component which would simulate the system and start hammering the NAS with requests using multiple threads and inspecting the replies, and sure enough, especially when the NAS would error, the replies would contain a different ID than the original sending ID on the same virtual connection.

So, there must be something in the NAS that does not work correctly. More code inspection, but nothing showed up until.... Hey, wait a second... We are using a global variable to save data and then reusing this data later on when the NTimer event fires, and, since the NAS is single threaded, this should all work fine, right?

Well, of course the first thing that came into my mind was that there was a possibility that a new incoming event would be "queued" before the NTimer event would be scheduled, and this would mean that a new MessageReceived event would be triggered before the NTimer could even process the previous message. Bingo! That is the issue!

After further investigating the NAS code, it turns out that, although the previous scenario is possible, it actually does not queue sequentially. Incoming events are basically random when the host COM cannot handle the call. I could go very deeply here, but what happens is that, when the NAS COM event is called, the NAS actually checks to see if it is already handling another incoming event. If this is the case, it signals that it cannot process the incoming event (via a RPC_E_SERVERCALL_RETRYLATER) and COM basically waits "some time" before it retries the call. The problem is that this "some time" is not known and it changes, and that is when the trouble really starts.

Basically, what happened in our scenario was that 2 consecutive MessageReceived events will fire (before the first NTimer would) and obviously one GlobalVal would be lost and we would reply twice  to a single event (the NTimers would fire, but with the same GlobalVal).

How to solve this

The cure was simple. When the MessageReceived event is fired, we process the information and reply to the event on the same connection. After all, ComCom objects do report back AL errors in XML format, so, in principle, there is no need to complicate matters.

So, after getting rid of the NTimer, and replying to the incoming request within the MessageReceived event, everything works as expected.

---

Jorge Alberto Torres
Software Developer Engineer

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

Jobs Update for Microsoft Dynamics NAV 5.0 SP1 is now released.

I am pleased to announce the release of Jobs Update for Microsoft Dynamics NAV 5.0 SP1. This update is now available for you to download from Partner Source. Login credentials are required. https://mbs.microsoft.com/partnersource/downloads/releases/MicrosoftDynamicsNAV50SP1.htm

Jobs update rollup corrects the issues that are described in KB article number 954191.

 

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

 

Mtoo Norrild
Program Manager

Modern NAV/SQL troubleshooting II

Please refer to this post about what I mean with "modern troubleshooting".

This post describes methods that work on any version of SQL Server, including SQL2000. It describes one of the most common questions I get, which is "Where do we start"...

General performance problems - where to start:
If a system is suffering general performance problems, then it is not always easy to decide what to do first, or where to start. Then you may be tempted to collect lots of information, for example Profiler traces and databases which may or may not give any results, but which is guaranteed to require a lot of work both to collect, to send, and to analyse.

Often, over-indexing is one of the main causes of both bad performance problems and blocking. Indexes take time to maintain, and updating an index causes locks, which in turn can contribute to blocking-problems. so in a cases where the problem can't be located to a specific action, one place to start is to do some index tuning.

In a case like that, I often begin by asking for just two files:
Excel spreadsheet containing Table Information
A NAV backup or fob file which contains the customer's objects, but not their data

Each of these files don't take long to prepare, and are normally small enough to send by email.

You collect the Excel spreadsheet like this:
In NAV, go to File -> Database -> Information, and click on Tables.
Copy this into an Excel spreadhseet.

When you get the spreadsheet, sort it by "No. of Records", or by "Size (KB)". The difference is, that "Size (KB)" includes the size of the indexes on each table. So either way of sorting gives you a good idea of which tables are being updated the most, and which tables have lots of both records and indexes.

Then load the NAV objects that you also received, and for the top 5 - 10 tob tables in the spreadsheet, take a look at the tables in NAV, and check the number of keys and SIFT-indexes. Some times you will immediately see that these tables have had a lot of keys added, and you should try to see if you can reduce the number of keys, using the key properties MaintainSQLIndex, MaintainSIFTIndex and SIFTLevelsToMaintain (remember the SIFTLevelsToMaintain-property don't exist any longer in NAV 5 SP1 where it was removed as part of re-designing the SIFT system).

Which indexes to disable the SQL-maintenance of:
Only if you know how the system is being used, can you tell whether a certain index is being used or not. But, for example, a key like "IC Partner Code" in table 17 is only ever useful if the customer is using Intercompany Posting, and the "Additional-Currency Amount"-sumindex field on the same table is only needed if Additional currency is being used. So some times, a few obvious changes can be done. But to really do some index tuning, you need to know more than the objects alone can tell you.

You can also take the opposite approach, and disable maintenance of all indexes and SIFT (except for the clustered index). This will show you which ones are really needed when certain processes begin to run very slowly, and you will then have to (quickly) re-enable those. This is of course a risky approach, but it can be quicker than analyzing each individual key.

 

In deciding which indexes may be disabled, the following tools may help as well:
If running on SQL Server 2005 or later, run the query "Index Usage" (follow this link)

The Key Information tool on the SQL Server Resource kit can help you deciding the cost of indexes per table, and help deciding the usefulness of SIFT indexes.
With Navision Developers Tool (NDT), you can run "Where Used" on a key, to see in which object it is being used. If you use this method, then make sure to select everything in the "Where Used"-options. And keep in mind, that even with everyhthing selected, the NDT cannot see if users are manually specifying certain keys on the forms and reports they are running.

 

 

Lars Lohndorf-Larsen

Escalation Engineer

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

 

Microsoft Dynamics NAV 5.0 SP1 and SQL Server 2000

Microsoft Dynamics NAV 5.0 SP1 introduces a new way to handle SIFT. Instead of maintaining totals in separate tables, Dynamics NAV 5.0 SP1 uses a SQL feature called indexed views. Indexed views will automatically be maintained by the SQL Server.

 

With SQL Server 2000, updating an indexed view can be a time consuming process as the SQL Server 2000 might decide to include a clustered index scan as part of its query plan to update the view. If your Microsoft Dynamics NAV implementation includes tables with many records and is based on Microsoft SQL Server 2000 there is a potential risk for experiencing bad performance.

 

Microsoft Dynamics NAV 5.0 SP1 is optimized for Microsoft SQL Server 2005 and you should consider to upgrade to SQL Server 2005 if you are implementing Microsoft Dynamics NAV 5.0 SP1.

 

The problem described here, only applies to SQL Server 2000. SQL Server 2005 handles the updates of indexed views much more efficiently.

 

 

Lars Lohndorf-Larsen

Escalation Engineer

 

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

Release of Upgrade Toolkit for Microsoft Dynamics NAV 5.0 SP1

I am pleased to announce the release of Upgrade toolkit for Microsoft Dynamics NAV 5.0 SP1. This update is now available for the first countries and it’s ready for you to download from Partner Source. Login credentials are required. https://mbs.microsoft.com/partnersource/downloads/releases/MicrosoftDynamicsNAV50SP1.htm

The Upgrade toolkit update rollup corrects the issues that are described in KB article number 952193.

 

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

 

Mtoo Norrild
Program Manager

NAV and the .Net Garbage Collector

Many NAV solutions, including internal ones, have the necessity to use .Net as a supplement of NAV technology, in order to complete their logic; however, dealing with managed and unmanaged world takes some extra challenges, especially when the managed code uses unmanaged resources. This is further important, when the .Net component will be hosted on the NAS, as it restarts (traditionally because of a deadlock) giving a short time for the unmanaged resources to be disposed.

When a .Net component ends, it is marked as to be released by the .Net runtime garbage collector, however, the time when the component will be freed from memory is not known (this is especially true for IDisposable objects). This can be problematic when the managed component is using unmanaged resources (such as TCP ports, files and the like), as these are not available to the host computer until they are completely de-referenced.

.Net architecture is aware of this issue, and has means to deal with it, this is further explained in the IDisposable Interface (http://msdn.microsoft.com/en-us/library/system.idisposable.aspx). What this document explains, is that your .Net component has to have the IDisposable interface in order to stop the unmanaged resources within your managed code. This works well, except that it adds the extra constrain that the Dispose method should be called specifically in order to trigger the logic, this is somehow a little bit tricky in NAV, as there is no specific method that is called on a codeunit, when it is exiting (there is no “OnStop” method).

There are at least two different ways to solve this problem. One is to create a mixed mode wrapper, and the other one is to call the Dispose (or Stop, or whatever you want to call your method) when the CompanyClose trigger is executed.

As an example, let’s assume that your solution needs to query the NAS for information using a TCP Socket. For that, you create a .Net COM component that will use the System.Net.Sockets.TcpClient object to be opened once the component starts, and you add this component to codeunit 50000. You modify codeunit 1 trigger 99 to start your single instance codeunit 50000. Everything seems to be working great, until the NAS restarts and your component refuses to start again (or does not start on the first restart try). The error is something like: “The TCP port x, is already in use”.

It might take some thinking before knowing exactly what went wrong over here, but it is easy to explain. As we mention before, when a .Net component is deleted, it is marked to be discarded by the .Net runtime at a later time (and if you still have references to your object, this time might be never), while the garbage collector “kicks in”, all unmanaged resources are still owned and opened by the previous component instance, preventing new instances on reusing the same unmanaged resource (in this example a TCP port).

The solution here is simple. We should add the Dispose method to the .Net component, and somehow call it when the single instance codeunit stops (on the “OnStop” event).

Now, where should we locate such a Dispose call? Since the NAS needs a company to work, we can use the OnCompanyClose trigger to further call our Stop method, which will call the Dispose method. We cannot only code this logic on the single instance codeunit (50000), as there is no trigger that will be called when the NAS is restarting.

Our hypothetical (and simplified) codeunit could look something like:

And one way to call the OnStop trigger would be:

 There can be other ways to solve this problem (as we also pointed out, mixed mode code is another obvious option), but the key is to know when the .Net COM component should be discarded if this component is using unmanaged resources.

---

Jorge Alberto Torres
Software Developer Engineer

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

What is the cost of maintaining an indexed view?

In previous versions, Microsoft Dynamics NAV maintains SIFT totals in SIFT tables. So updating the main table is done in one query. Updating the related SIFT tables is done by seperate queries run from triggers on the SQL tables. This makes it difficult to idenitfy the real cost of updating the table with a SQL Profiler trace, since you need to take several queries into account to get the real cost.

From NAV version 5 SP1, the SIFT tables are replaced with Indexed Views which makes it simpler to trace the full cost of an update to a base table and its associated indexed views:

In a SQL Profiler Trace, enable the event Performance:Showplan XML. With this event, when you see an update to a base table (for example INSERT INTO "W1500SP1"."dbo"."CRONUS International Ltd_$G_L Entry" etc), then the "Showplan XML"-event for this query will show not just the insert into the base table, but also which indexed views were updated, and the percentage of cost that each indexed view added to the whole query. So with this event you will have the full cost of an update and associated SIFT indexes in just one place - not spread over multiple queries in the Profiler trace.

You can read more details and screenshots of this, and other changed in NAV 5 SP1 in this post:

Changes to Microsoft Dynamics NAV 5.0 SP1 with Microsoft SQL Server

 

 

Lars Lohndorf-Larsen

Escalation Engineer

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

Index View Matching & Dynamics NAV 5.0 SP1

In a recent question we were asked whether it was required to use Dynamics NAV 5.0 SP1 with Microsoft SQL Server 2005 Enterprise or Developer Edition to gain better performance, from the changes we have done in Dynamics NAV 5.0 SP1, and the answer is "NO". I will in this blog post try to explain why.

Indexed views on SQL Server 2005 includes functionality like "index view matching" and is a capability that enables SQL Server (Enterprise and Developer Edition only) to automatically get the result of any query from an indexed view if it deems that this is faster than going to the base table. In other words this really makes indexed views act like indexes (A faster way to retrieve data). This functionality (index view matching) is not used in Dynamics NAV 5.0 SP1.

The benefit of index view matching is seen when we create an indexed view like:
CREATE VIEW GLEntry$VSIFT1 WITH SCHEMABINDING AS SELECT AccountNo, COUNT_BIG(*) $Cnt, SUM(Amount) SUM$Amount FROM GLEntry GROUP BY AccountNo

And execute a query like:
SELECT AccountNo, SUM (Amount) FROM GLEntry WHERE AccountNo='1000'

Then SQL server automatically gets the result from the indexed view rather than the base table.

Note: The functionality described above only works on enterprise and developer editions of Microsoft SQL Server 2005. In Microsoft Dynamics NAV 5.0 SP1 we didn't choose to use it as we did not want to limit Dynamics NAV implementations to the Enterprise Edition of Microsoft SQL Server.

Instead Dynamics NAV 5.0 SP1 will get data from the view explicitly like this:
SELECT AccountNo, SUM$Amount FROM GLEntry$VSIFT1 WITH NOEXPAND WHERE AccountNo='1000' GROUP BY AccountNo

We split calcfields/calcsum calls per table and if no indexed view contains all fields then we will go to the base table.

We choose the indexed view based on a simple filter cost calculation and of course the SIFT index must be able to honor all the SumFields and all the filter fields:

Filter:
"A=? AND B=? AND C>=? AND C<=?"

Keys:
A,B,C,D  - cost = 0+0+0+(1<<15-3)  = 4096
A,B,D,C  - cost = 0+0+(1<<15-2)+0  = 8192
A,B,C    - cost = 0+0+0 = 0!
D,A,B,C  - cost = (1<<15-0)+0+0+0) = 32768
A,D,E,B,C- cost = 0+(1<<15-1)+(1<<15-2)+0+0 = 24576

Martin Nielander
Program Manager

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

Changes to Microsoft Dynamics NAV 5.0 SP1 with Microsoft SQL Server

I just want to point you to the whitepaper describing the changes done for the Microsoft Dynamics NAV 5.0 SP1 SQL Option:

With the release of Microsoft Dynamics NAV™ 5.0 SP1, major changes have been made to Microsoft Dynamics NAV™ with Microsoft SQL Server. This document outlines these changes and shows how these changes can help you improve the Microsoft Dynamics NAV customer experience. This document will only cover changes to Microsoft Dynamics NAV with SQL Server.

Martin Nielander
Program Manager

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

Modern NAV/SQL troubleshooting

This post is the first in a planned series to describe various "modern" methods for troubleshooting performance problems with Microsoft Dynamics NAV on SQL Server. The idea is also to make the best out of information that already exists, either in other places on this blog or anywhere else.

Modern Troubleshooting:
The idea about "Modern Troubleshooting" is:

• To take as much of the hard work out of troubleshooting as possible.
• To make the system itself (SQL Server and NAV) help you finding out what is wrong with it.
• To collect as little and as specific data as possible, to avoid having to read through for example gigabytes of trace files.
• To do the troubleshooting remotely as much as possible, rather than spending days on an individual system on site.

I want to make it clear that with "Troubleshooting", I mean "Trouble". And the methods I describe here will be quick-fixes for when something has gone wrong already. I don't mean to reduce the need for thorough performance- or scalability reviews, which can still require lots of work and understanding of the customers business. So I definitely don't want the "Modern Troubleshooting" to replace specialist consulting and ongoing (especially pro-active) performance tuning. Of course it is always better to avoid Trouble in the first place.

Identifying Trouble-queries:
One of the most useful queries I have used for performance troubleshooting recently, is the "TOP 30"- query described here:
Simple query to check the recent performance history

It returns the queries that are currently in SQL Server's plan cache, sorted with the queries causing most reads at the top. Every time SQL Server generates a query plan, then this plan is stored in this cache. And every time SQL Server (re)uses this plan it will also update some statistics about the plan, like execution_count, number of reads, etc. It is this information that the "TOP 30" query gives you.

SQL Server's plan cache is changing all the time, depending on what queries SQL Server runs, so you may get different results depending on what time of the day you run the query. The plan cache is also reset when SQL Server restarts, or you can reset the cache by running DBCC FREEPROCCACHE.

The result of the "TOP 30"-query is easy to copy into an Excel spreadsheet for further analysis, or to send to someone else. So it is simple data to collect, as long as the customer is on SQL2005 or later (Being based on Dynamic Management Views (DMW) which was introduced in SQL Server 2005, it not work for SQL Server 2000).

 

What to look at:
After receiving the result of the query - preferably in an Excel spreadsheet - these are the things I look at:
Being sorted by "max_logical_reads", you have the "worst" query at the top. But also look at execution_count. If a query ran just once, it might have been a batch job, or something else that is not really causing any problems. Of course, queries that have an execution_count in the 100s or 1.000s may be more relevant to look at. Also see if the queries (stetement_text) look similar, or if many of them look to be in the same area (same tables).

The column diff_quota shows max_logical_reads divided with min_logical_reads. If this number is high, it means that the query plan is inconsistent. This can be either because of inconsistent use of NAV (for example users applying different filters on the same table). Or, because a query plan is good for some queries but bad for others. In this case, some times you can affect the way that SQL Server creates query plans, either by adding RECOMPILE hints, plan guides or index hints. Or by upgrading to a newer version of NAV client (for example see the post "SQL Pre-processing in Microsoft Dynamics NAV 5.0 SP1" for how NAV 5 SP1 will cause different query plans).

If diff_quota is low, it means that the query plan is just consistently bad, which means that it is more likely that the query itself is bad. And, you will have to look for reasons why that query consistently causes the number of reads that it does. The "TOP 30"-query can't really help finding out why a query is causing many reads. But at least it can some times identify which queries to investigate first, which can be a very time consuming task otherwise (collecting and analysing profiler traces, etc).

You can also look at max_elapsed_time, but keep in mind that when a query takes a long time to run because it is being blocked, then the real problem is somewhere else (in the blocking query). So, if a query has a high max_elapsed_time, then see if the query contains a lock (WITH UPDLOCK). If it does, then you are most likely looking at a blocking problem which requires a wider look, and which often cannot be solved by the query you see.

So the "TOP 30"-query is always a good place to start. Some times it won't help you solving any problems, but then you will know that without spending much time. Other times, the query tells you right away which queries are causing problems, which can save you a lot of time. So I would always run this query first. And only if it doesn't help, then begin to look at collecting further information.

 

 

Lars Lohndorf-Larsen

Escalation Engineer

 

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

SQLIndex property

In some situations, using the SQLIndex property on a key in Microsoft Dynamics NAV can harm performance. In this blog I

describe what to be careful about, and why the use of this property has been removed in the NAV 5 SP1 application.

The property is still available and it is still a valuable tool in performance tuning. But from SP1, it is not used anymore

in the standard application. The document "Changes in NAV 5 0 SP1.doc" on the SP1 product CD lists the 72 tables where the

SQLIndex has been removed (Change A222).

 

Background:
The key-property SQLIndex was introduced in NAV version 4 SP1. The idea of the property is to make it possible to change

the index on the SQL Server while maintaining the same application (same sorting) in NAV.

The main use of the property is to make the SQL index more selective. In the NAV application there are many keys that begin

with an option which is not very selective, for example the primary key "Document Type","No." on the Sales Header table.

"Document Type" - having only 6 possible options - is not very selective, and SQL Server might choose not to use it. If the

index was changed to be the other way around ("No.","Document Type"), it would be much more selective and more effecient

for SQL Server to use in SELECT statements.

The other benefit of this property is to enable "covering indexes", so that you can have a few indexes to cover for most

searches, and then disable the maintenance of other indexes. For example an index on the "Sales Header" table beginning

with "No." can be used effeciently with many different filters, reducing the need to have one key for every possible exact filter.

 

Problems:
When you have a query which includes an "ORDER BY"-clause, SQL Server has to return the reords in the order specified by

that clause. If SQL Server doesn't have a matching index, it has to retrieve data using a different index and then do some

internal sorting to return data in the correct order. If there are no good indexes, then SQL Server may choose to

use the clustered index which can be bad enough. But when the query also has an index hint, then SQL Server is forced to use the

index specified by the hint, and this can lead to large amounts of reads.

 

Example:
In a recent support case, the customer had generally bad performance. In this case, the "SELECT TOP 30"-query from the post "Simple query to check the recent performance history" showed that out of the top 30 "worst" queries, 26 were similar to this one

SELECT TOP 501 * FROM "Reservation Entry" WITH (UPDLOCK, INDEX("$1"))   WHERE (("Source ID"=@P1)) AND (("Source Ref_

No_"=@P2)) AND (("Source Type"=@P3)) AND (("Source Subtype"=@P4)) AND (("Source Batch Name"=@P5)) AND (("Source Prod_ Order

Line"=@P6)) AND (("Reservation Status"=@P7)) ORDER BY "Source ID","Source Ref_ No_","Source Type","Source Subtype","Source

Batch Name","Source Prod_ Order Line","Reservation Status","Shipment Date","Expected Receipt Date","Entry No_","Positive"

 

The query itself looks good enough: WHERE-clause and "ORDER BY"-clause match each other, and there were no immediate

reasons why this query should cause more reads than the number of records in the table. But in the standard application, the SQLIndex property for this key was:

"Source ID","Entry No.",Positive

The idea with this is SQLIndex is to have an index which can cover more situations, and in that way reduce the number of indexes that

need to be maintained on SQLServer. And the SQLindex is fine for the SELECT-part of the query. The problem is, that the

index can't be used for the "ORDER BY"-part of the query.

So what happens, is:

SQL Server may have planned to use the clustered index to read all data and then do some internal sorting. But in this

case, the Index Hint forces SQL Server away from that plan. The result is that SQL Server is forced into doing a very

difficult task while being restricted by the index speicifed by the index hint.

In this case we designed the "Reservation Entry"-table and removed the SQLIndex property from the key, and performance went

up.

 

Conclusion:
When you see a query which causes many reads, even if SQL Server has a good index, then also consider if the index is

good for the "ORDER BY"-part of the query. The "ORDER BY"-part of the query depends on the key in NAV. But if the SQLIndex

property has been set for this key, then by definition, the "ORDER BY" and the SQL index will not be matching.

You should still consider the use of the SQLIndex property as part of tuning performance of a system. But just be aware that it can also cause problems as described here.

 

 

Lars Lohndorf-Larsen

Escalation Engineer

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

Bulk Inserts in Microsoft Dynamics NAV 5.0 SP1

Bulk Inserts is a new feature in NAV 5.0 SP1 which is designed to improve performance when inserting multiple records by delaying the physical inserts on the SQL Server.

Before this change, inserts happen in the order that the C/AL code is running. With Bulk Insert, the NAV-client delays the actual inserts until the last possible moment in the transaction. This means that tables get locked later, so it reduces the amount of time that a table is locked.

"The last possible moment", means that the inserts will take place just before COMMIT. Or, if you use the return value of the INSERT command (IF Rec.INSERT THEN;). NAV also has to do the inserts if you run any MODIFY, DELETE or FIND methods on the table. So to take full advantage of this feature, you have to consider this when designing a NAV process.

 

Note: This feature has nothing to do with the Transact SQL command "BULK INSERT". The Bulk Insert all happens on the client. It is not using any special SQL Server features.

The Pseudo-code below illustrates how you will see the effect of Bulk Insert in a SQL Profiler trace. Without Bulk Insert, the SQL updates may look like this:

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

 

With Bulk Insert, the same C/AL code would look lilke this:

 

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

 

So, you will see exactly the same SQL updates. But with Bulk Insert, the inserts will be accumulated and run at the end of the process.

 

 

Lars Lohndorf-Larsen,

Escalation Engineer

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

My experiences with Microsoft Dynamics NAV 5.0 SP1 …

I spent last week performing a Microsoft Dynamics NAV 5.0 Update 1 to Microsoft Dynamics NAV 5.0 SP1 database conversion for a customer.  The process went very smoothly and the customer was very excited about the increase in performance we were able to achieve. 

Just as an illustration of our success, the customer ships hundreds of packages a day.  This process has been very painful in the past.  When the conversion was completed and a little tuning was done, we were able to achieve the following improvements …

·         Create a Shipment --> Reduced by 27%

·         Create a Pick --> Reduced by 15%

·         Register a Pick --> Reduced by 54%

·         Post a Shipment --> Reduced by 53%

 

Now, I mentioned a little tuning was necessary so let me elaborate on that.  SP1 seems to highlight poor filtering used for the FlowFields.  The tuning that I had to do amounted to determining which CALCSUMS were not performing well.  I did this by using the Client Monitor in the Performance Toolkit.  I was then able to examine the filters that were applied and determine what a more appropriate key would be.  I added the additional key to the table along with the appropriate SumIndexField(s).   This made all the difference for this customer.

 

This customer was also plagued by a large number of locking/blocking issues.  After applying SP1, with the Bulk Inserts and Indexed Views, the locking/blocking issues became almost non-existent.

 

Some other pieces of information that might be helpful:

·         Log file size – during the conversion, the log file did NOT grow at all.

·         Database Size – when the conversion was done, this particular customer was able to get back approximately 10 GB of free space in the database out of a 65 GB database.

·         Conversion Time – it took between 40 -60 seconds per GB depending on what hardware was used.

·         Indexed Views – do NOT add any additional indexes to the Indexed Views.  When you design the table that the Indexed View is associated with, the view will be dropped and recreated and so will any additional indexes that you might have added.

 

Robert Miller

Escalation Engineer

 

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

SQL Pre-processing in Microsoft Dynamics NAV 5.0 SP1

One of the major changes in Microsoft Dynamics NAV version 5 SP1 (in relation to performance on SQL Server), is a new way to send queries to SQL Server. In previous versions of NAV, we some times saw SQL Server 2005 optimizing query plans for extreme parameter-values, which - when re-used from cache for queries with other parameter-values - could cause long response time. A behaviour which is described in more details in KB 935395 on PartnerSource (login required). Some of the updates for NAV version 4 introduced new features to give better control of the query plans that SQL Server makes, such as index hints and Recompile-option.

 

SP1 for NAV version 5 has restructured the way that queries are sent to SQL Server, with the aim that SQL Server will now make query plans that are optimized for average parameter-values rather than extreme parameter-values. It is also a method which lets SQL Server make the plan, without forcing it in a certain direction with index hints or recompile-option.

Before NAV SP1, a typical query could look like this:

declare @p1 int

set @p1=180150033

declare @p3 int

set @p3=2

declare @p4 int

set @p4=1

declare @p5 int

set @p5=0

exec sp_cursoropen @p1 output,N'SELECT * FROM "Demo Database NAV (5-0)"."dbo"."CRONUS International Ltd_$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND "G_L Account No_"=@P2 AND "Posting Date"=@P3 AND "Entry No_">=@P4 ORDER BY "G_L Account No_","Posting Date","Entry No_" ',@p3 output,@p4 output,@p5 output,N'@P1 varchar(20),@P2 varchar(20),@P3 datetime,@P4 int','1105','','1753-01-01 00:00:00:000',0

select @p1, @p3, @p4, @p5

Notice the parameter-values in bold ('1105','','1753-01-01 00:00:00:000'). SQL Server will make a query plan based on running the query with these parameter-values. It will then cache this plan, and use the same plan for other queries which are identical, but have different parameter-values.

 

In SP1, the query above will look like this:

 

declare @p1 int

set @p1=1073741861

declare @p5 int

set @p5=12290

declare @p6 int

set @p6=8193

exec sp_cursorprepare @p1 output,N'@P1 varchar(20),@P2 varchar(20)',N'SELECT * FROM "W1500SP1RTM"."dbo"."CRONUS International Ltd_$G_L Entry" WHERE (("G_L Account No_"=@P1)) AND "G_L Account No_">@P2 ORDER BY "G_L Account No_","Posting Date","Entry No_" ',1,@p5 output,@p6 output

select @p1, @p5, @p6

And then another query:

declare @p2 int

set @p2=180150031

declare @p3 int

set @p3=2

declare @p4 int

set @p4=1

declare @p5 int

set @p5=2

exec sp_cursorexecute 1073741861,@p2 output,@p3 output,@p4 output,@p5 output,'1110',''

select @p2, @p3, @p4, @p5

 

So before, we had one query. In SP1 we have two! So what's the benefit of that?

If you look at the first query from SP1, notice that it is a sp_cursorprepare statement, and not sp_cursoropen. So the actual query is not run at this point. More importantly, the first query does not contain the parameter-values. This is the query for which SQL Server makes the query plan. Not having the parameter-values, SQL Server makes the plan based on its statistics about the data in the table. Only after this, NAV then executes the statement in the second query (sp_cursorexecute).

This method guarantees that SQL Server's query plan will not be affected by the parameter-values. It means that some times, SQL Server is prevented from making the optimum query plan for a certain set of parameter-values. But remember that the query plan will be re-used for other parameter-values. So at the expense of having a few highly optimized queries, the method will give well optimized queries with better consistensy.

Another cost of this method, is of course that now NAV sends 2 queries instead of 1, requiring an extra roundtrip to SQL Server. But this only happens the first time the query is run. If the same query is run again, NAV will only run the second query (sp_cursorexecute).

 

One side effect of this is, that tracing a query in SQL Profiler becomes different. With SP1 you will see a lot of queries like the second one above, which does not show what NAV is actually doing. Take a look at the second query again:

exec sp_cursorexecute 1073741861,@p2 output,@p3 output,@p4 output,@p5 output,'1110',''

With SP1 you will see a lot of queries like this, and then wonder what the actual query is. To find out, you need to use the cursor ID, and then find the original sp_cursorprepare statement, which will contain this line:

set @p1=1073741861

and then the actual query.

 

In summary, this method is designed to give persistently, good overall performance, and to avoid sudden drops in performance that could be the result of cached query plans on SQL Server.

 

 

Lars Lohndorf-Larsen

Escalation Engineer

 

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

Release of Microsoft Dynamics NAV 5.0 SP1

It is with great pleasure that I can announce the release of Microsoft Dynamics NAV 5.0 SP1. During today the first countries will be available from PartnerSource and I would really like you to pay attention to the following two web sites on PartnerSource:

Microsoft Dynamics NAV 5.0 SP1 General information:
https://mbs.microsoft.com/partnersource/products/navision/newsevents/news/50sp1.htm

Microsoft Dynamics NAV 5.0 SP1 Download Information:
https://mbs.microsoft.com/partnersource/downloads/releases/MicrosoftDynamicsNAV50SP1.htm

Microsoft Dynamics NAV 5.0 SP1 Release Plan:

March 28, 2008:
Release in United States, Germany and Denmark. This is also the release date for the W1 version of Microsoft Dynamics NAV 5.0 SP1.

May 7, 2008:
Release in Australia, New Zealand, Canada (EN+FR), France, India, Indonesia, Ireland, Italy, Malaysia, Netherlands, Philippines, Singapore, Spain, Thailand and United Kingdom.

June 17, 2008:
Release in Austria, Belgium (NL+FR), Finland, Iceland, Mexico, Norway, Sweden, Russia, Czech Republic, Slovakia, Estonia, Hungary, Latvia, Lithuania, Poland, Portugal, Switzerland (FR+IT+DE), Bulgaria, Croatia, Romania and Slovenia

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

Martin Nielander
Program Manager