NAV/SQL Performance - My Two Cents

Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server

Hi all,

in previous BLOGS I was talking about general things like "blocking mechanisms", "block/deadlock detection" or principal solutions.
In this article I'd like to present some practical examples to really solve/prevent/reduce blocking and deadlocking issues!

Having in mind that "blocking problems" is often a very individual thing, depend on code and business processes or user interaction …

Thus, this should be some kind of starting point, hopefully helping or at least inspiring you to fix some problems. I also would like to encourage YOU to share your experiences and solutions here!

Disclaimer: ALL EXAMPLES ARE PROVIDED AS IS. NO WARRANTY, NO GUARANTEE, NO SUPPORT. USE AT OWN RISK.

Here we go …

 

EXAMPLE #1 – T357 "Document Dimension"

Problem:
With the standard "Clustered Index" the records are structured in way so that certain queries – when posting documents – might "interlock"; e.g. posting Sales Order No. 1000 could block posting Sales Order No. 2000.
Actually this problem depends on the amount of documents and dimensions used.

Solution:
Change the "Clustered Index" of Table 357
Key (Primary Key):    Table ID, Document Type, Document No., Line No., Dimension Code
SQL Index:        Document No., Document Type, Table ID, Line No., Dimension Code
Clustered:        TRUE

Comment:
Changing a CI on a large table could cause heavy load on the system, taking a long time! You should implement such changes off business hours.

 

EXAMPLE #2 – C80/C90 & "G/L Entry"

Problem:
In Codeunit 80 "Sales Post" (and 90 "Purchase Post") the end of Table 17 "G/L Entry" is locked (LOCKTABLE, FINDLAST). The intention here is to avoid deadlocking issues on T17 during postings; this is actually some kind of semaphore to serialize postings, causing blocks.
But: several postings in NAV do not even create a "G/L Entry"! E.g. posting shipments without invoicing and not using "Expected Cost Postings". Still, T17 is locked, causing unnecessary blocks in such cases.

Solution:
Determine if T17 needs to be locked:

Standard Code (C80):

...
IF RECORDLEVELLOCKING THEN BEGIN
  IF WhseReceive THEN
    WhseRcptLine.LOCKTABLE;
  IF WhseShip THEN
    WhseShptLine.LOCKTABLE;
   DocDim.LOCKTABLE;
  IF WhseReference <> 0 THEN
    WhseRqst.LOCKTABLE;
  SalesLine.LOCKTABLE;
  ItemChargeAssgntSales.LOCKTABLE;
  PurchOrderLine.LOCKTABLE;
  PurchOrderHeader.LOCKTABLE;
  GLEntry.LOCKTABLE;
  IF GLEntry.FINDLAST THEN;

END;
...

Improved Code (C80):

...
IF RECORDLEVELLOCKING THEN BEGIN
  IF WhseReceive THEN
    WhseRcptLine.LOCKTABLE;
  IF WhseShip THEN
    WhseShptLine.LOCKTABLE;
  DocDim.LOCKTABLE;
  IF WhseReference <> 0 THEN
    WhseRqst.LOCKTABLE;
  SalesLine.LOCKTABLE;
  ItemChargeAssgntSales.LOCKTABLE;
  PurchOrderLine.LOCKTABLE;
  PurchOrderHeader.LOCKTABLE;


  InvtSetup.GET();
 
IF ( (InvtSetup."Expected Cost Posting to G/L" = FALSE) OR (InvtSetup." Automatic Cost Posting" = FALSE) )
    AND ("Document Type" = "Document Type"::Order)
    AND (Ship = TRUE)
    AND (Invoice = FALSE)
  THEN
    PostToGL := FALSE // PostToGL = Global; Boolean
  ELSE
    PostToGL := TRUE;
  IF PostToGL THEN BEGIN
    GLEntry.LOCKTABLE;
    IF GLEntry.FINDLAST THEN;
  END;

END;
...

Comment:
Proceed similar with C90. This example just deals with orders, ignoring returns.

 

EXAMPLE #3 – T309 "No. Series Line"

Problem:
Technically the Table 309 "No. Series Line" is pretty small and contains a fixed (more or less) number of records. Whenever a new number is drawn from No. Series, the corresponding record is locked, the last number is read and increased, then the record is updated. When updating the record the lines is locked (ROW X). In some cases this could also mean that the whole data page is also locked (PAG IX), which could lead to blocking issues. Means, for example, if a "No. Series" is used (e.g. "S-INV+") the line is locked (ROW X), also the page (PAG IX) blocking another process even when using a different "No. Series" (e.g. "P-ORD").

Solution:
If two processes try to lock exactly the same "No. Series Line" (e.g. "S-INV+") there's nothing we could do. In other cases it could be feasible to insert some "spacer records" / "dummies" between the real "No. Series Line" records. If the spacers are big enough, this will force every "Real" record on its own data page, hence, the PAG IX lock is not a problem anymore.

New Field in T309:

Field No.

Field Name

Data Type

Length

50000

Dummy

Boolean

 

50001

Dummy 1

Text

250

50002

Dummy 2

Text

250

50003

Dummy 3

Text

250

50004

Dummy 4

Text

250

50005

Dummy 5

Text

250

50006

Dummy 6

Text

250

50007

Dummy 7

Text

250

50008

Dummy 8

Text

250


Creating the dummy records / space (using some codeunit):

NoSeriesLine.SETRANGE(Dummy, FALSE);
IF NoSeriesLine.FINDSET THEN BEGIN
  REPEAT
    FOR m := 1 TO 5 DO BEGIN
      NoSeriesDummy.INIT;
      NoSeriesDummy := NoSeriesLine;
      NoSeriesDummy."Series Code" := NoSeriesLine."Series Code";
      NoSeriesDummy."Line No." := NoSeriesLine."Line No." + m;
      NoSeriesDummy."Starting No." := '';
      NoSeriesDummy."Ending No." := '';
      NoSeriesDummy."Warning No." := '';
      NoSeriesDummy."Increment-by No." := 0;
      NoSeriesDummy."Last No. Used" := '';
      NoSeriesDummy.Open := FALSE;
      NoSeriesDummy."Last Date Used" := 0D;
      NoSeriesDummy.Dummy := TRUE;
      IF NoSeriesDummy.INSERT THEN BEGIN
        FOR n := 1 TO 25 DO BEGIN
          IF STRLEN(NoSeriesDummy."Dummy 1") <= 240 THEN BEGIN
            NoSeriesDummy."Dummy 1" := NoSeriesDummy."Dummy 1" + '1234567890';
            NoSeriesDummy."Dummy 2" := NoSeriesDummy."Dummy 2" + '1234567890';
            NoSeriesDummy."Dummy 3" := NoSeriesDummy."Dummy 3" + '1234567890';
            NoSeriesDummy."Dummy 4" := NoSeriesDummy."Dummy 4" + '1234567890';
            NoSeriesDummy."Dummy 5" := NoSeriesDummy."Dummy 5" + '1234567890';
            NoSeriesDummy."Dummy 6" := NoSeriesDummy."Dummy 6" + '1234567890';
            NoSeriesDummy."Dummy 7" := NoSeriesDummy."Dummy 7" + '1234567890';
            NoSeriesDummy."Dummy 8" := NoSeriesDummy."Dummy 8" + '1234567890';
          END;
        END;
        NoSeriesDummy.MODIFY;
      END;
    END;
  UNTIL NoSeriesLine.NEXT = 0;
END;

Comment:
Don't forget to filter Form 457 "No. Series Lines" on "Dummy" = FALSE.

 

Example #4 – DELETEALL / MODIFYALL

Problem:
If a DELETALL (or MODIFYALL) statement is fired in NAV, the resulting DELETE (or UPDATE) command (TSQL) will cause the SQL Server to instantly "mark" same meta-data/pages with X/IX. These locks are engaged even before the SQL Server actually starts the transaction, even if in fact there is nothing to DELETE or UPDATE!
Hence, there could be blocks on tables which are empty.

Solution:
Make sure there is something to DELETEALL or MODIFYALL! E.g. T36, OnDelete Trigger (excerpt):

Standard Code:

WhseRequest.SETRANGE("Source Type",DATABASE::"Sales Line");
WhseRequest.SETRANGE("Source Subtype","Document Type");
WhseRequest.SETRANGE("Source No.","No.");
WhseRequest.DELETEALL(TRUE);

Improved Code:

WhseRequest.SETRANGE("Source Type",DATABASE::"Sales Line");
WhseRequest.SETRANGE("Source Subtype","Document Type");
WhseRequest.SETRANGE("Source No.","No.");
IF NOT WhseRequest.ISEMPTY THEN
  WhseRequest.DELETEALL(TRUE);


Comment:
Search the code (SDK) for DELETEALL or MODIFYALL. Whenever you find such an algorithm, and if there is a chance that the result might be empty, then it is feasible to only execute the transaction if there is actually something to delete or modify.

 

Example #...

Problem:

Solution:

Comment:

To be continued …

  • Edit 11.02.2013: "G/L Entry -  Lock - Bypass" (Example #2) enhanced/corrected ("Automatic Cost Posting" must be regarded)...

  • One general remark according to Example #2:

    Have in mind that when checking in CU80 the flag "Ship", the corresponding flag in CU90 is "Receive" (NOT "Ship"):

    InvtSetup.GET();

     IF (InvtSetup. "Expected Cost Posting to G/L" = FALSE)

       AND ("Document Type" = "Document Type"::Order)

       AND (Receive = TRUE)

       AND (Invoice = FALSE)

     THEN

       PostToGL := FALSE // PostToGL = Global; Boolean

     ELSE

       PostToGL := TRUE;

     IF PostToGL THEN BEGIN

       GLEntry.LOCKTABLE;

       IF GLEntry.FINDLAST THEN;

     END;

  • Sure, please change the code as it suits your needs! My example actually was taken from a project, thus it's based on their setup.

  • Thanks Jorg.. :)

    Now, I want to implement your second example. If I am using Expected Cost to G/L, but I am not using Automatic Cost To G/L, could I change your code like below?

     InvtSetup.GET();

     IF (InvtSetup. "Expected Cost Posting to G/L" = FALSE) OR ((InvtSetup. "Expected Cost Posting to G/L" = TRUE) AND (InvtSetup. "Automatic Cost Posting" = FALSE))

       AND ("Document Type" = "Document Type"::Order)

       AND (Ship = TRUE)

       AND (Invoice = FALSE)

     THEN

       PostToGL := FALSE // PostToGL = Global; Boolean

     ELSE

       PostToGL := TRUE;

     IF PostToGL THEN BEGIN

       GLEntry.LOCKTABLE;

       IF GLEntry.FINDLAST THEN;

     END;

    Thanks Jorg.. :)

    Best regard,

    Johanna

  • >>Should I add Document No., Document Type, Table ID, Line No., Dimension Code as secondary key then set its Clustered property to YES?

    No, in this case it is not necessary to create an additional Key; it should be sufficient enough to set the "SQLIndex" property for the first Key (PK/CI) to:

    Document No., Document Type, Table ID, Line No., Dimension Code

  • Hi Jorg,

    Thanks so much for your sharing..

    I have many problems in blocking and deadlock. I want to try your example 1:  

    Key (Primary Key):    Table ID, Document Type, Document No., Line No., Dimension Code

    SQL Index:        Document No., Document Type, Table ID, Line No., Dimension Code

    CLUSTERED : YES

    I have looked in Table Document Dimensions of my NAV 4 SP3. There are Table ID, Document Type, Document No., Line No., Dimension Code

    as first key. What is SQL Index in your example? Should I add Document No., Document Type, Table ID, Line No., Dimension Code as secondary key then set its Clustered property to YES?

    Thanks..

    Best Regards,

    Johanna

  • All these improvements have been implemented in real life scenarios several times! When example #2 was created for the fisrt time we could cut down the blocks on G/L from about 100 per day to zero!

    But of course, it depends on how the application has to post those documents ...

  • I am curious about example #2. Do you have real world examples of improvement? We average 17000 lines a day in the sales line table and blocking has been detrimental. Any thing at this point would be helpful.  

  • Indeed, changing the "SQL Index" to a different layout than the "Key" could cause trouble; but in this special case I have never seen this problem.

    But changing the "Primary Key" in NAV should be an absolute NO GO! If you change the PK you have to massively change the (standard) C/AL code; actually you could screw up the whole application.

    Regarding this example #1: if you don't want to change the "SQL Index" of the PK, then simply add a new "Key" with the optimized layout and mark that one as "Clustered".

  • Hi Jorg,

    Thanks for sharing your solutions.  They are very good except example #1.  I do dis agree using a different SQL Index.  The reason is the order by clause is still dictated by the navision key not by the SQL Index and due to this it will cause massive deadlocks in busy tables such as document dimension.  This is perticularly true in modern NAV version (V4 Sp3 and above).  I have seen this in many places.  Perhaps the solution should be to change the Navision primary key instad.

  • @Peter: sure, nothing is "100% bulletproof". As so often, it always depends on individual circumstances if a "solution" is indeed a solution ... or not ...

    As mentioned in the beginning:

    This should just list examples, something to help and inspire people to solve (?) their issues.

    But then again: the examples I have put were implemented several times with obvious success!

  • Nice examples, but I doubt #4 is always 100% bulletproff.

    Say you have two users: One is trying to delete a document, which does a DELETEALL on the document lines. Then the second user does something which should modify all the document lines, but the ISEMPTY say it is already empty, so it skips the MODIFYALL statement. However; the first user might encounter an error before committing and thereby rollback his statements.

    Now the lines are recreated, but user 2 didn't get any error and but the data is now inconsitant. (same situation if he was trying to delete the lines)

    It might sound theoretical, but I think that is why NAV locks a table even when it is empty.

  • Thanks Jörg, these practical examples are really helpful.