NAV 2013 Beta (Refresh) – First Impressions

Normal 0 false 21 false false false DE X-NONE X-NONE

So, finally the first public „Beta“ of “NAV 2013” is available …

https://mbs.microsoft.com/partnersource/newsevents/news/msdnav2013betadownload.htm
http://msdn.microsoft.com/en-us/library/hh173988(v=nav.70).aspx

… so I also have started to check it out! Of course, as usual my focus is on the NAV/SQL technical site

Those of you who have access to MS Connect are encouraged to provide feedback, as well.

The following will be just some kind of unsorted list of things I just came across while testing:

“Always Rowlock” gone?

In the setup of database properties (File\Database\Alter; page “Advanced”) there’s no more option to enable “Always Rowlock” …
Pity, this was a cool feature to prevent SQL Server Lock Escalation. Basically this could also be accomplished by enabling a Traceflag (1224) but I’m not fond of such global switches:
what’s good for NAV is not necessarily good for other databases/applications on the server.
Please, I’d like to have this feature back!

New locking algorithm in Sales Post (and Purchase Post) – looks good!

I just ran a few parallel sessions all creating & posting Sales Orders and Purchase Orders. In the older NAV versions blocks on “G/L Entry” were one of the most annoying things – in my test now those completely vanished! This is because “G/L Entry” is locked later than in current NAV versions. I also did not see any blocks on classic “Document Dimension” – simply because this table does not exist anymore  and the whole “Dimension” management has been complete changed and improved!
Still, some “well known old issues” remain: blocks on “No. Series Line” and “Item Ledger Entry” – some classic “single points of failure” … but those blocks just took about 700 msec at average and 2 sec at maximum!
I did not get a single deadlock! To be continued …

“Query” rocks!

The new object type “Query” promises a lot of potential for improvement as we could use it to query data with real relational statements, thus we can get rid of “loppie-loopie code” and have way less calls to the SQL Server.
I had little trouble in getting started due to NAV license issues, but finally I was able to create my own little “Query” and somewhat benchmark it; here my brief test and results:

This is a little SQL statement I created in Management Studio:

SELECT c.[Name], sl.[No_], sl.[Description], SUM(sl.[Quantity]) AS [Total Quantity]

FROM dbo.[CRONUS AG$Sales Line] sl (READUNCOMMITTED)

JOIN dbo.[CRONUS AG$Customer] c (READUNCOMMITTED)

ON sl.[Sell-to Customer No_] = c.[No_]

WHERE sl.[Type] = 2

GROUP BY c.[Name], sl.[No_], sl.[Description]

ORDER BY c.[Name] ASC, SUM(sl.[Quantity]) DESC

GO

I wanted to get the same now with NAV, so I created – my very first! – “Query” like this:

Filtering on “Type” = 2 and linking the “Sales Line”.”Sell-to Customer No.” to “Customer”.”No.”. I want to sort by “Customer”.”Name” ASCENDING and “Total_Quantity” DESCENDING.

Using SQL Profiler I could record the SQL statement fired when running the “Query”; finally it looks like this:

SELECT ISNULL("Customer"."Name",N'') AS "Name",

       ISNULL("Sales_Line"."No_",N'') AS "No",

       ISNULL("Sales_Line"."Description",N'') AS "Description",

       ISNULL(SUM("Sales_Line"."Quantity"),0.00) AS "Total_Quantity"

FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$Customer" AS "Customer" WITH(READUNCOMMITTED) 

LEFT OUTER JOIN "Demo Database NAV (7-0)"."dbo"."CRONUS AG$Sales Line" AS "Sales_Line" WITH(READUNCOMMITTED) 

ON ("Sales_Line"."Sell-to Customer No_"="Customer"."No_")

WHERE (ISNULL("Sales_Line"."Type",0)=2)

GROUP BY ISNULL("Customer"."Name",N''),ISNULL("Sales_Line"."No_",N''),ISNULL("Sales_Line"."Description",N'')

ORDER BY "Name" ASC,"Total_Quantity" DESC,"No" ASC,"Description" ASC

OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

(I have formatted the code and replaced the parameters with the actual values so I could execute this in SSMS, too)

Actually this looks quite OK to me; there are just two major differences:

1.       My “ad hoc” statement has got the “Sales Line” as FROM clause and JOINS to the “Customer”. The “Query” is doing it the other way around.

2.       The “Query” adds the remaining output fields to the ORDER BY clause

The result is in both cases the same (959 records); just the sorting order is slightly different. From a performance perspective they behave almost identically:
Reads = ca. 30000; CPU = ca. 150 msec

Being not happy with this I tried to optimize. After troubleshooting this by adding some indexes …

CREATE NONCLUSTERED INDEX ssi01 ON [dbo].[CRONUS AG$Sales Line]

([Sell-to Customer No_],[Type])

INCLUDE

([No_], [Description], [Quantity])

CREATE NONCLUSTERED INDEX ssi01 ON [dbo].[CRONUS AG$Customer]

([Name])

… I could cut that down to ca. 300 Reads and 50 msec CPU; which should be fair enough (for now!).

Not possible to identify the Users/Sessions from SQL Server perspective

With NAV 2013 the authentication setup on SQL Server site has been remarkably improved! No more xp_ndo_something, no more Login/User creation on SQL, no more Kerberos hassle, no more “Security Models” – just one straight Logon per NST, no more user delegation. This will make things a lot easier when setting up and administering a NAV/SQL system!
The downside is, that it is not possible to identify the User/Session from an SQL Server perspective; e.g. when doing SQL Profiling or Block Detection etc.. All queries are executed using the single Logon Account of the NST. For example, if a block occurs it shows “NST blocked by NST” …
For troubleshooting purposes it is mandatory to identify the User or Session. Well, this has been discussed with MS several times and there are already several ideas how to solve this problem.
Let’s see what the final “NAV 2013” will provide …

FIND and FINDSET – no difference (?)

Today it is most important - regarding performance – to use the correct FIND command according to the filter and key specified. A FIND(-) will create a “Dynamic Cursor”, and this one will go berserk if the filter does not properly match to the sorting order and no appropriate index is available (see my other articles about details). FINDSET won’t create a DC, but call the first result with a TOP clause, then – if the actual result exceeds that TOP clause – the remaining data will be fetched with a DC, same as with FIND(-).
NAV 2013 does not use “Cursors” anymore – at least I haven’t seen any so far -; now “Multiple Active Result Sets” (MARS) are used (see: http://www.sqlteam.com/article/multiple-active-result-sets-mars ).

Hence, FIND(-) and FINDSET are supposed to behave pretty much alike. Here some test queries I have recorded with SQL Profiler:

-- FIND('-'); No SETCURRENTKEY

SELECT  TOP (50) "timestamp","Entry No_","G_L Account No_",...

FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$G_L Entry 2" WITH(READUNCOMMITTED) 

WHERE ("G_L Account No_"=N'2310' AND "Posting Date">='01.01.1999 00:00:00' AND "Posting Date"<='31.12.2000 00:00:00')

ORDER BY "Entry No_" ASC

OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

-- FIND('-'); With SETCURRENTKEY

SELECT  TOP (50) "timestamp","Entry No_","G_L Account No_",...

FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$G_L Entry 2" WITH(READUNCOMMITTED)

WHERE ("G_L Account No_"=N'2310' AND "Posting Date">='01.01.1999 00:00:00' AND "Posting Date"<='31.12.2000 00:00:00')

ORDER BY "G_L Account No_" ASC,"Posting Date" ASC,"Entry No_" ASC

OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

-- FINDSET; No SETCURRENTKEY

SELECT  "timestamp","Entry No_","G_L Account No_",...

FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$G_L Entry 2" WITH(READUNCOMMITTED) 

WHERE ("G_L Account No_"=N'2310' AND "Posting Date">='01.01.1999 00:00:00' AND "Posting Date"<='31.12.2000 00:00:00')

ORDER BY "Entry No_" ASC

OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

-- FINDSET; With SETCURRENTKEY

SELECT  "timestamp","Entry No_","G_L Account No_",...

FROM "Demo Database NAV (7-0)"."dbo"."CRONUS AG$G_L Entry 2" WITH(READUNCOMMITTED) 

WHERE ("G_L Account No_"=N'2310' AND "Posting Date">='01.01.1999 00:00:00' AND "Posting Date"<='31.12.2000 00:00:00')

ORDER BY "G_L Account No_" ASC,"Posting Date" ASC,"Entry No_" ASC

OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)

Remark: with NAV 2013 there is no more SELECT * as all fields are explicitly listed now (same as with today’s NAV versions on BLOB affected tables). I have shortened that in my examples.

So the major difference is the ORDER BY clause, as expected. But: now the FIND(-) queries (instead of FINDSET!) contain a TOP (50) clause! If the actual result exceeds this TOP value, then another query is fired to fetch the remaining data … Why that difference? Bug or Feature?

Good news: from a performance perspective all queries perform pretty similar, using the right index, causing very few Reads and CPU load!

Max. Record Size 8000 and UNICODE

Finally! As current NAV versions limit the record-definition length to 4000 bytes it is often necessary to “link” separate tables to virtually expand the record. Such workarounds should be history now, as the new limit is 8000 bytes.

But have in mind: NAV 2013 supports UNICODE - Hurray! But this also means, that “Text” and “Code” fields use more space: as in current NAV the datatype “Text” and “Code” is “varchar” on SQL Server, thus using 1 byte (8 bit) per character, with NAV 2013 it will be “nvarchar”, thus taking 2 bytes (16 bit) per character!
So when migrating data from existing NAV to NAV 2013 we have to expect an increase of the database size, just due to the conversion from “varchar” to “nvarchar”.

Intelligent MODIFY?!

With current NAV, whenever you do a MODIFY on a record - even though you have not changed any field-value! - an UPDATE statement is fired to the SQL Server anyway (always "modifying" the last field of a table).
NAV 2013 seems to realize if nothing was changed and just sends a SELECT TOP 1 NULL ... WITH (XLOCK) to the server.
Thus, still an exclusive lock is engaged, but at least no write transaction was started.

NAV 2013 Beta Refresh Build 33595

Since a couple of weeks/months the "refreshed" Beta is available. Well, I dare say the most important technical improvements it the ...

Full SQL Trace

With the new Debugger (now a Page) we could enable a so called "Full SQL Trace" for an existing session or any new ones. If enabled, then the NST sends an additional statement to the SQL Server, containing valuable information of NAV's C/AL stack: the user name, the object context and the related AL! Isn't that cool!?!
Well, at least this is a start. Since this info is sent as a separate statement it is not possible to filter SQL Profiler Traces, e.g. on "Reads" or "Duration" etc. as this will supress the "AL Statement" ... Also, this info could not be recorded when detecting blocks or deadlocks ...

Anyway, this is what we've got now and it is indeed useful. Here an article describing how to use this new feature: http://blogs.msdn.com/b/nav/archive/2012/09/18/example-of-how-to-use-sql-tracing-feature-to-profile-al-code.aspx (please also follow the comments to this BLOG article)

I have attached a SQL Profiler Trace Template (SQL 2008 R2) and an improved modified version of the TSQL code shown in the MS article.

To be continued …