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 …

Parents
No Data
Comment
  • 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.  

Children
No Data