Following up on our previous posts, this is the latest edition of the NAVDiag tool, aimed to inspire and simplify your customer service. The tool is for on-premise installations only, but we hope this can help increase the efficiency of your customer service and free more time and resources for product tasks, like migration to Cloud.

New in this edition is the code coverage added to supplement the steps recording function added in last version. The Record Steps action will now start both step recorder and code coverage, and export the output when completed. The idea is to collect all details you might need for troubleshooting a single task, in one click of the button. This is done invoking the built-in code coverage, available as of Dynamics NAV 2016, but you can modify the tool to use the old CodeCoverage free fobs available for 2015 version.

We’ve also added a simple config analysis function that will point out the changes in your service configuration, in relation to default configuration values. User can now also download and mail the log.

You have given us some great ideas and suggestions, and these are not forgotten. Please allow us some time to add these, and keep your ideas coming!

navdiag1-4

The post Coffee Break: Simplify Your Customer Service appeared first on Microsoft Dynamics 365 Blog.

In the previous two posts, Automation Part 1 and Part 2, we talked about how to easily bring PowerShell into Dynamics NAV.

Based on the applicability and interest shown so far we wanted to continue on the previous thread and address some of the suggestions we received, as well as add some functionality. We called the result NAVDiag. You can use this as a tool that helps you simplify and speed up the troubleshooting. But above all: We hope this will create engagement around tooling possibilities and inspire for more suggestions.

In this edition, we have added examples of how to read the license file and read the config file. Exporting license file is done using a NAV cmdlet, and because running NAV cmdlets requires elevated permissions,  in order to run NAV cmdlets from this tool your NAV Server account must be an admin account. In general, we are trying to avoid functionalities that require elevated permissions in these examples, as the point is simplicity and the ability to use this on a default configuration without any further installation or modifications needed. But after your feedback we thought this could be useful to have under a caution that running actions that invoke NAV cmdlets will fail unless the NAV Server user account has elevated permissions.

Another functionality we added is capturing repro steps when troubleshooting using Windows Steps Recorder. This is initiated using client side .NET interoperability, so this functionality will not run (nor be present) on a Web client (see limitations). The same would apply to collecting diagnostic details client side (can be used on Windows client only).  Collecting client-side data was one of the questions/suggestions we got and is something that we will come back to soon. For now, we would like to wrap up the functionalities that do not require any changes to default configuration.

So here it is:

The tool now allows you to collect diagnostics, the same diagnostics we presented in the last post, with added examples of reading the config file, the license file and Steps Recorder.

Record Steps action will allow user to record problem steps and save the output. This is basically just a call to the built-in Windows Steps Recorder function, but still useful to have as a part of the tool. Finally, the License File action will read the license file, the specified NAV Server account has elevated permissions.

There will be more coming up but if you find this useful and have further ideas about how this can be improved and used in your daily tasks, please let us know.

NAVDiag Log object

The post Coffee Break: NAVDiag appeared first on Microsoft Dynamics 365 Blog.

This is a continuation of our previous post, with further examples of how to utilize Windows PowerShell from Dynamics NAV. The samples provided here just demonstrate how easy it can be to integrate the two, so you can build on this using any of the great (Dynamics NAV) PowerShell tools out there from a familiar development environment. For example, to collect system locale (local codepage) from your server, you only need to call on the following two routines:

PSSession.AddCommand('get-WinSystemLocale');
 RunPS('Get System Locale','DisplayName');

Where the DisplayName is the return value you are looking for.

The idea here is to help finding ways to simplify and automate supporting customers and users. This time we also provided an option of collecting the details you may need using a default installation configuration, with Network Service as the Dynamics NAV Server account. The examples provided are for illustration and we look forward to hearing from you about useful scenarios that could be sampled here.

The sample objects are included in this attachment: navdiag1-1

The Coffee Break Team

The post Coffee Break: Automate it, part 2 appeared first on Microsoft Dynamics 365 Blog.

The Coffee Break Team is back, and this time we will unite the best of the two worlds and show how we can utilize the power of Windows PowerShell, using your favorite application: Dynamics NAV. While we do recognize that scripting complex PowerShell tools and modules is not to everyone’s taste, C/AL is something we all do in Dynamics NAV world. So let’s put them together and see how you can extend your application with PowerShell.

This time, the user scenario can be to automate diagnostics of your Dynamics NAV system and shorten the time and effort it takes to collect all needed details. In this first post we will look at how to use PowerShell to collect some basic diagnostic details. The attached objects consist of a PowerShell runner codeunit, to invoke and manage PowerShell sessions, another codeunit to specify the diagnostics you chose to run, a log table you can export, and two pages to bind them all.

The essence of invoking and running a PowerShell session is described in this blog post, and involves the PowerShell Runner Add-In (deployed with the product). This time we will go a step further and show how to collect the resulting output. Once you run the cmdlet, the result, in fact a collection of them, is read using a PS object ‘wrapper’ (also part of the PowerShell Runner Add-In). In its most basic form, this would read in this result:

ResultsEnumerator := PSRunner. Results.GetEnumerator;

ReturnObject := ReturnObject.PSObjectAdapter;

ReturnObject.PSObject := ResultsEnumerator.Current;

ReturnObject.GetProperty enables you to then query for specific properties.

The example we’re showing here (objects attached here: NAVDiag Log objects) does require a server user account to have administrative rights on the machine (to read service details), but it can be replaced by other queries/cmdlets that would not require administrative permissions. Reading event logs, for example, would not require elevated permissions.

The screenshot of the tool (below) needs little explanation. The Run Diagnostic button will collect the following details: services on the machine, their versions and running state. Then it will proceed to collect last 10 event logs generated by Dynamics NAV. The output log can be exported.

There is much more you can collect of course, and we intend to show some of it in these posts, but this will hopefully provide a good illustration of how to use PowerShell, in a relatively simple way, even if you are not much experienced in it.

The Coffee Break Team

The post Coffee Break: Automate it! appeared first on Microsoft Dynamics 365 Blog.

First, let me start by saying that based on the evidence so far: It is. Significantly.

A number of the changes to the Dynamics NAV 2013 architecture contribute to the performance boosts that many of the test have shown. To outline some of the changes:

• Middle tier is 64 bit
• We are no longer using cursors but MARS
• Ability to auto-update sift fields when looping, rather than repeating the process for each call. SETAUTOCALCFIELDS reduces the amount of statements issued
• Pages containing flowfields now issue ‘SmartSQL’ queries, basically one source table query with outer joins for each flowfield query, again reducing the ‘chattiness’
  and calculating sift values in one server roundtrip
• Global cache
• REPEATABLEREAD is default isolation level
• Locking changes in posting routines, locking at later point and reducing overall blocking time

… and more.

However(!), a few things have surfaced in the course of time that are not as explicitely documented as the changes above, nor as apparent, and might have unexpected side effects. I have collected some of the side effects that you might or not be aware of and that might leave you panicking if not certain what you’re facing.

• Pages (with FlowFields) will in general run faster on Dynamics NAV 2013 than on NAV 2009 due to SmartSQL queries and reduced chattiness. Flow Fields on a page (all of them) are calculated in one go, which greatly reduces number of statements issued. But SmartSQL queries are not cached. Also, since we changed to MARS, SQL seems to be more sensitive to an index’s column cardinality then before. You might experience that (depending on various factors) some queries that have run fine in the past now take much longer, this is due to a poor execution plan on SQL. With Dynamic cursors, query plan optimizer tended to optimize for the ORDER BY, while with MARS, SQL is free to choose.  It does a good job at it too, but in rare occasions might chose poorly. These cases are exceptions and contrary to the popular belief, this is not caused by the SmartSQL queries. These queries will have same poor execution plan and perform poorly even when isolated from the SmartsSQL query.

Consider the following example. This is just an illustration of the problem, constructed on CRONUS extended database:

A lot of Inventory transactions are posted through the Item Journal, generating a lot of Post cost to the G/L Entry. After this, when browsing an item list and opening an Item card, the page opens very slowly.

After locating the offending query in the SQL profiler and running it isolated in Microsoft SQL Server Management Studio with ‘Include Actual Execution Plan’ option enabled, the plan looks similar to the one shown below:

Each sub-query shows reasonably (small) percentage of cost and no apparent reason for bad execution plan. There are no obvious extreme costs, however there is a Clustered Index Scan here:

Looking at the filter that SQL Server applies, namely it filters on “Post Value Entry to G_L”. “Item_No_” = “Item”.”No_”:

Although SQL Server reports Operator Cost as small, it shows CPU Cost in excess of 2.3 in approx. 1.94 executions. So, it is likely scanning the table twice and unfortunately the table has 4,7 million rows.

Although it is not obvious from the actual SQL Server execution plan that this is a problem, profiling with the SQL Server Profiler reports the query to use more than 5 seconds of CPU, while doing 131.519 reads to fetch 52 rows:

The reason the Duration is on par with CPU Seconds is that all reads are logical from SQL Server Buffers. Re-issuing the query after adding the supporting index shows this in SQL Server Profiler:

So Reads were reduced by a factor of 100 and (warm) duration was reduced by a factor of 40.

As you can see, these poor execution plans are not caused by the SmartSQL. However the fact that the SmartSQL queries don’t cache their results will only amplify the issue. To solve it, we have to tackle the performance of that one isolated query by creating a covering index to improve the execution plan.

And no, it won’t help to merely customize the page or change the visibility of the field. As long as it is contained in page metadata (so unless removed from page altogether), it will be calculated.

So in short, if you do run into an issue of rather dramatic slowness of a page containing flowfields in Dynamics NAV 2013 or higher, isolating and testing the Flow Field queries separately (focusing on ones with clustered index scan, regardless of cost) should lead you to the culprit fairly quickly.  A supporting index should resolve the problem.

• Temp tables: These are now moved to .NET. When you create a Temp record, your private C# TempTableDataProvider object is created. It will store whatever records you insert into a C# Dictionary, with the Primary Key Fields as the key for the Dictionary. The Dictionary is in memory and stores records in the order they
  are inserted.  When you add a SetCurrentKey, an AVLTree is built on the fly at first FindXX() you perform with that (current) key. In terms of performance, this is an expensive operation. This tree is in memory however, so it will be cached and reused later. If you issue a query without calling SetCurrentKey, an AVLTree for the primary key will be used.

However, maintaining all this can consume quite a lot of memory on your middle tier, so plan for ample memory when scaling. Also, as mentioned above, querying temp tables is cached but sorting them is a fairly expensive operation, just something to keep in mind.

• The next one is not strictly a performance issue, but can have a fairly drastic performance side-effect if you’re affected by it, so deserves to be mentioned:

You might (or might not) be aware that the transaction scope and behavior of a page action has changed in Microsoft Dynamics NAV 2013 and higher. This will be especially significant if you are calling functions in codeunits (or objects other than the source table), passing the REC as a parameter and intend to lock the record in that function.

Consider the following example: You have added an action that invokes a function in a codeunit, that in turn locks/ modifies the record (typically calling custom posting routine)

So OnAction trigger code is, for example, as follows:

  PostingFunction(Rec);

Where PostingFunction is a function (in any object other than the source table).

Now, the consequence of the previously mentioned transaction scope change and the fact that you’re locking the record in the function, is that the entire source table (Rec) you passed as a parameter in the example above, is locked. In other words, you’re passing the current record as a parameter, but you are locking the whole table. The behavior would cause more damage than good to change at this point due to all functionality it would affect, so it won’t be changed, but fortunately – if
you’re aware of this issue, the solution is very simple:

SETSELECTINOFILTER(Rec);  //adding this line

PostingFunction(Rec);

Adding the line above should reduce the scope of locking and leave you with locking just the one record (or selection of records if on a list). This applies to card pages as well.

• Last, but not least: you might have noticed increased Async_Network_IO waits. These cause quite a lot of concern out there. However, this is a symptom and not a problem per se.

When Dynamics NAV issues a query that returns thousands of rows, SQL Server will start a work thread to return the result. If the application does not consume the rows as fast as they are delivered from SQL Server then the SQL Server work thread will have to wait and that shows up as wait type Network_Async_IO. If the application never consumes the entire result set then the worker thread will be hanging until the transaction is ended (when we close the statement), or if it was a read transaction, for a longer period either until the connection is closed (as idle) or if the statement is overwritten by another statement (statement cache is full).

Example: when we do a FINDSET, a number of records (50, 100…) is retrieved. If it is a larger set it will run until all records are retrieved (or buffer is full), even if only first 10 are actually read by application. Eventually the session goes to sleep and after transaction ends sleeping sessions are removed. So in short, these are merely reflecting NAV data access methods, and are not a problem as such. If you want to reduce these, make sure you’re reading all the rows you’re asking for when using FINDSET, otherwise use FIND(‘-‘) or FIND(‘+’).

With thanks to Jesper Falkebo and Jens Klarskov Jensen

Jasminka Thunes

Microsoft CSS

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

The post Microsoft Dynamics NAV: Faster than ever. appeared first on Microsoft Dynamics 365 Blog.