Tag Archives: SQL Server

NAV2015 | Performance Tuning

This post also applies to NAV2013, any version since we moved to the new SQL client and dropped classic client.

I am currently in progress of upgrading two of my oldest customers. One with 10 and one with 15 years of data. Large databases, highly customised and tuned for performance to perfection what can be done in those versions.

One of the things you promise these customers is better performance, since NAV2013 and beyond are the fastest versions of Navision ever.

However, after the initial data upgrade and making all the pages more or less usable this is not just the case. Actually the performance is completely terrible. Rubbish.

Ai ai.

The reasoning behind this is relatively simple. Microsoft has made a couple of changes to the way it talks and works with SQL Server that make old performance tuning tips work less and new tips to be required.

iu

First of all we have the “OUTER APPLY”. This is an actual JOIN between flowfields and tables that happens when running pages. Instead of fetching flowfields row by row Navision now creates one query that joins all the flowfields. This can cause the system to slow down if SQL is not optimised for joins.

Learning to optmise SQL for joins is basic knowlegde for DBA’s. Something I learned from sessions organised by SQL Skills. Its all about creating indexes that have the fields from the where clause. Covering indexes also make huge differences.

Second we have the statistics. It used to be best practice to turn off auto create statistics beacuse Navision used cursors. Well, it does no longer do that. We can now safely turn on the default values and not worry about updating them asynchronously.

Lastly there is paralelism on the SQL Box. This used to be set to 1 for NAV databases. Don’t do that anymore. Because of the “OUTER APPLY” we actually bennefit from multiple CPU treads.

fast

Lessons learned? Well this is not my first NAV2013 implementation/upgrade. I learned all this the hard way while emailing back and forth with my former SQL Perform friends. I actually think I had one of the first 100GB+ NAV2013 databases after an early upgrade.

NAV2013 and newer are the fastest versions ever, but performance tuning is an art. It always was and always will be.

iu7GAQ1O6O

Can you screw it up? Yes off course. As a user could Navigate to an Item Ledger Entry in classic client, remove the filter and place a new one and blow up the system, in NAV2013 they can accidentaly sort on an unindexed column. In the last scenario SQL Server will generate and use statistics, but statistics cannot replace indexes.

Enjoy this summer read.

NAV 2013 R2 | Keys, Keygroups and SetCurrentKey

With NAV 2013, the Classic Client and the Native Navision database were discontinued.

One of the possibilities that arised was improving the SQL story in NAV.

This was done first by implementing SETAUTOCALCFIELDS and the possibility to do CALCSUMS and FlowFields without specifying a SumIndexField. This is released in NAV2013.

In NAV2013 R2 the story continues

Keys

In the Native database the structure how Navision maintained Keys was perceived as revolutionairy and a main part in the success of the Application and its architecture.

Within a Key one could specify SumIndexFields. These are decimals that then would be totalled in the background without writing code.

Everything in NAV like G/L, Inventory and Jobs is based on that principle. You will not find any Application code in NAV that totals entries.

To be able to sort something a Key was required in the Native database. A maximum of 40 keys were allowed. This is different in SQL Server.

SQL Server can sort without having an Index (the name for Key in SQL Server). Therefore with the Native database being discontinued the possibility opened to change the Key story in NAV.

Therefore with the introduction of NAVA 2013 R2 it is no longer required to define a Key on a table to sort data.

It is now possible to sort on any column in the UI.

And it is also possible to sort on any field from AL code.

SetCurrentKey no longer requires a Key to be created in the table.

KeyGroups

Another change that has been imlemented is the discontunation of KeyGroups.

After internal debate and investigation Microsoft decided that the active use of the feature was not enough to justify the effort of moving the feature from Classic Client to PowerShell.

Do you agree with this?

NAV 2013 | No more Indexhinting

By Marije Brummel, founder of NAV-Skills

UPDATE: I’ve heard this was fixed in 2016. Dit not test this myself.

Well, that sounds cool right? Like, never index hinting in NAV anymore after all the discussions in the past.

http://mibuso.com/forum/viewtopic.php?f=34&t=13154&hilit=hinting

http://www.mibuso.com/dlinfo.asp?FileID=896

With NAV 2013 it is no longer possible to setup index hints using the $ndo$dbconfig table.

Does it mean it’s no longer nescesairy.

Unfortunately not.

I found out about this during an upgrade of one of my customers from NAV2009 RTC to NAV2013.

We experienced extremely slow performance in an area where NAV2009 was much faster than NAV2013. And here I promised better performance… DAMN. Stick out tongue

Off course with our perfect documentation (not) it took some time to figure out it might be that index hinting was used in NAV2009 to force SQL using an index it would not use normally.

The process is selecting shipments to be planned in a trip for a taxicompany. We use a common method to select a shipment using the user id, just like you would when applying customer and vendor ledger entries. When selected it’s an extremely selective field (only one or two record of a million have this value) but SQL does not pick it up because… because… shoot me, I don’t know why not. DAMN 2. Stick out tongue

So with index hinting no longer possible (confirmed by Microsoft, done on purpose, by redesign) I needed to be creative.

The solution is to save a pointer to a shipment in a second table called “Selected Shipment”. All shipment that need to be planned in a trip are temporarily stored there and picked up and deleted afterwards. Depending on the architecture of your transaction this could even be done in a temporary table and/or a single instance codeunit.

So recap: no more indexhinting, be carefull with upgrades and creative with solutions.

And PS: The general performance of NAV 2013 is AWESOME!!!

NAV 2013 | SETAUTOCALCFIELDS

NAV 2013 | SETAUTOCALCFIELDS

In NAV 2013 we have a new command SETAUTOCALCFIELDS.

What does this command do.

It is used in C/AL like this:

Cust.SETAUTOCALCFIELDS(Balance)

IF Cust.FINDSET THEN REPEAT

Some Code

UNTIL Cust.NEXT = 0;

This leads to this query in SQL Server:

SELECT

ISNULL(“Customer”.”timestamp”,@0) AS ISNULL(“Customer”.”No_”,@1) AS “No_”,…,

ISNULL(“SUB$Balance”.”Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount”,@77) AS “Balance”

FROM “CRONUS Nederland BV$Customer” AS “Customer” WITH(READUNCOMMITTED)

OUTER APPLY (SELECT TOP (1) ISNULL(SUM(“Balance$Detailed Cust_ Ledg_ Entry”.”Amount”),@76) AS “Balance$Detailed Cust_ Ledg_ Entry$SUM$Amount”

FROM “CRONUS Nederland BV$Detailed Cust_ Ledg_ Entry” AS “Balance$Detailed Cust_ Ledg_ Entry” WITH(READUNCOMMITTED)

WHERE (“Balance$Detailed Cust_ Ledg_ Entry”.”Customer No_”=”Customer”.”No_”)) AS “SUB$Balance”

Hence, a single SQL Statement that joins two tables.

This dramatically changes the teaching that has been done about T-SQL generation in NAV from the last few years.

Also, notice that the SIFT view is not used.

BE AWARE

Like a filter,  SETAUTOCALCFIELDS keeps being active on a variable until it is reset.

To reset this command Cust.SETAUTOCALCFIELDS should be used. So without any fields, just like resetting a filter with SETRANGE.

Thanks to Luc van Vugt to bring this to my attention.