Microsoft Dynamics NAV Sustained Engineering Team Blog

Session Monitor for SQL Server 2005

Session Monitor is not a new tool, but it did stop working with SQL Server 2005. So I think that a lot of people stopped using it, which is a shame. The things that were broken now work again, and I would recommend anyone with any kind of blocking problems to implement this tool. With this post I hope to get some people to use it again, and to provide a new tool to anyone who did not know it exists.

Purpose of the tool

The purpose of the Session Monitor tool is to show a live picture of any current blocks in the Microsoft Dynamics NAV database. And to show it in a familiar environment, i.e. a NAV form. It is an extension of the information you get when going to File -> Database -> Information, and drill down on Sessions.

What the tool does:

• Shows live information about who is blocked by whom
• Shows which user is at the top of a blocking chain
• Shows if a user is involved in blocking, i.e. either being blocked or is blocking someone. So you can filter out all sessions that are not involved in blocking
• Shows the wait resource, i.e. on which table the block is

This is how the Session Monitor shows a blocking situation:

As default it refreshes every second.

Some of the useful features of Session Monitor are:

• Filter on "Involved in Blocking". Then, as soon as there is a block it will show up. This is especially useful when you have more sessions that can fit on the screen, which means that there might otherwise be a block outside of the form.
• Wait Resource tells you on which table the block is, which helps you get some idea about in which area of the application it is.
• Originating blocker ID shows the head-blocker. This is useful when a user is blocked by someone who is blocked by someone else, etc.
• Wait Time shows how long the block has been there.

How to get it to work

As mentioned, the tool stopped working in SQL Server 2005. But there are corrections for this now. First, download the tool, which is part of the "Performance Troubleshooting Guide", available for download here (PartnerSource login required):

Microsoft Navision SQL Resource Kit

The original tool contains a SQL query (Session Monitor (SQL Server).sql) which is the part that doesn't work for SQL Server 2005. So don't run this query.  Run the query from this KB article instead (PartnerSource login required here too):

KB 933042 - Error message when you use the Session Monitor feature in Microsoft Dynamics NAV: "Invalid length parameter passed to the substring function"

Note!: Run the query on the NAV database - not on Master, or any other database you have on SQL Server.

In NAV, import the object "Session Monitor (SQL Server).fob" from the Performance Troubleshooting guide.

Make one change - otherwise in some cases it will fail with an INSERT error:

Design codeunit 150011 "Session Monitor Mgt. (SQL Srv)", and replace this line:

RefreshUserActivity()
SELECTLATESTVERSION;
IF Session.FIND('-') THEN
  REPEAT
    SessionTmp := Session;
    // SessionTmp.INSERT; Remove this line and replace with:

    IF SessionTmp.INSERT THEN ; // New line  
  UNTIL Session.NEXT = 0;

Those are the changes needed to get it to run on SQL Server 2005,

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.

How to deploy multiple NAS’es in ADCS

Although this is not related to the ‘core C/Side’, I would discuss about configuring ADCS with multiple NASes.

The first question would by why somebody would like to do this? The answer is very simple. On a single VT100 NAS installation, whenever a handheld user is performing a time-consuming task, any other handheld user will have to wait for the task to complete, before getting a response in the screen. The reason is because the NAS can perform one task at a time, and while it is busy, it will queue any other incoming requests. Another good reason is because the VT100 will round-robin the available NASes making the up-time more reliable.

The installation is simple, as long as you configure the system like this (TCP ports are examples):

For security reasons, it is recommended that the Access Point uses encryption (to the handhelds), and that this sub-network is private between the handhelds and the VT100.

The NASes can be on the same machine or on different boxes, as long as you remember to set up the registry correctly where the VT100 service is installed. If the NASes are on different boxes, remember to generate an encryption key per box, and to copy the public key to the corresponding entry in the registry of the VT100 box. Of course the installation gets simplified if the NASes run on the same box, and even more if the VT100 and the NASes share the same hardware.

Let’s make a step by step installation (assuming a NAV 5.00 or 5.00 SP1).

1.       Install the Software (Database + NAS + VT100 + C/Side) as described in the manual.

2.       Run C/Side using Windows Authentication. Remember to use the same credentials that the NAS will get (else the encryption key cannot be read by the NAS).

3.       Generate the encryption key (one per machine – not per NAS).

4.       Set each NAS to connect to the corresponding (same for all NASes) database, company, etc, using the same Windows login credentials as you logged in to generate the encryption keys.

5.       Because of a VT100 limitation, each NAS will have to get a different TCP port number, even if they are located on different machines, so, the startupparameter should be “ADCS ADCSID=#”, where # refers to a different TCP port.

6.       Now, the really tricky part comes from here. For each NAS that the VT100 will connect to (that is, on the machine where the VT100 service will run) you will need an entry in the registry, in order to connect the VT100 with the corresponding NAS. Each entry should be under “HKEY_LOCAL_MACHINE\SOFTWARE\Navision\ADCS\Application Servers”. The ADCS installation will create “ADCSNAS1”, and you can continue creating an entry per NAS on the same level (let’s say “ADCSNAS2, ADCSNAS3”, etc).

7.       If the NAS is on a different machine than the VT100, you also need to set the ‘SocketServerName’ and portnumber, where the first one refers to the machine name (or IP address) of the host of the corresponding NAS, and the second one, the portnumber (i.e. 11322). Both of these keys are ‘String Value’.

8.       For each NAS, you will need the ‘CryptBlockSize’, ‘Key Size’ and ‘Public Key’ (the ‘Private Key’ should not be copied), and this ‘Public Key’ should be copied from the machine where the NAS is hosted.

a.       NOTE- The machine that hosts the VT100 will need one ‘ADCSNAS’ entry per NAS that it connects to, but the machine(s) that hosts several NASes will only need one ‘ADCSNAS’ that will share the same keys (generated by the C/Side client), but different ADCSID startupparameter.

9.       Now you should start the NASes and the VT100 services on the corresponding machines. To test that everything works as it should, you should be able to stop any of the NASes on your system, and the screens should not halt (however, the response time might be slower). Also, when not all NASes are available, new sessions might not be successful, but any existing ones should be fine.

With this configuration you benefit on the simultaneous response time for the handhelds, as different NASes will be able to process in parallel even when the NASes are hosted on the same machine. Also, as discussed before, this helps minimize down times, as even when a NAS stops or gets restarted, the VT100 will retry from the ‘pool of NASes’ when a TCP timeout occurs. Because of this, don’t set NASes on the registry that will not be deployed, as this will in fact slow down the system.

---

 

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.

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.