Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server
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 357Key (Primary Key): Table ID, Document Type, Document No., Line No., Dimension CodeSQL Index: Document No., Document Type, Table ID, Line No., Dimension CodeClustered: 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:
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.
To be continued …
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".