Jörg Stryk (MVP - Dynamics NAV) about Performance Optimization & Troubleshooting with MS Dynamics NAV and MS SQL Server
the first public „Beta“ of “NAV 2013”
is available …
… so I also
have started to check it out! Of course, as usual my focus is on the NAV/SQL
you who have access to MS Connect are encouraged to provide feedback,
following will be just some kind of unsorted list of things I just came across
“Always Rowlock” gone?
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
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 …
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]
JOIN dbo.[CRONUS AG$Customer]
ON sl.[Sell-to Customer No_] =
WHERE sl.[Type] = 2
GROUP BY c.[Name], sl.[No_], sl.[Description]
ORDER BY c.[Name] ASC, SUM(sl.[Quantity]) DESC
I wanted to
get the same now with NAV, so I created – my very first! – “Query” like this:
on “Type” = 2 and linking the “Sales Line”.”Sell-to Customer No.” to
“Customer”.”No.”. I want to sort by “Customer”.”Name” ASCENDING and
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
AG$Customer" AS "Customer" WITH(READUNCOMMITTED)
LEFT OUTER JOIN "Demo Database NAV
Line" AS "Sales_Line"
GROUP BY ISNULL("Customer"."Name",N''),ISNULL("Sales_Line"."No_",N''),ISNULL("Sales_Line"."Description",N'')
ORDER BY "Name" ASC,"Total_Quantity"
OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)
formatted the code and replaced the parameters with the actual values so I
could execute this in SSMS, too)
this looks quite OK to me; there are just two major differences:
“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.
“Query” adds the remaining output fields to the ORDER BY clause
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
happy with this I tried to optimize. After troubleshooting this by adding some
CREATE NONCLUSTERED INDEX ssi01 ON [dbo].[CRONUS AG$Sales Line]
([Sell-to Customer No_],[Type])
([No_], [Description], [Quantity])
CREATE NONCLUSTERED INDEX ssi01 ON [dbo].[CRONUS AG$Customer]
… I could
cut that down to ca. 300 Reads and 50 msec CPU; which should be fair enough
Not possible to identify the Users/Sessions
from SQL Server perspective
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 ).
FIND(-) and FINDSET are supposed to behave pretty much alike. Here some test
queries I have recorded with SQL Profiler:
-- FIND('-'); No
SELECT TOP (50) "timestamp","Entry No_","G_L Account No_",...
FROM "Demo Database NAV
(7-0)"."dbo"."CRONUS AG$G_L Entry
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
-- FIND('-'); With
FROM "Demo Database NAV
(7-0)"."dbo"."CRONUS AG$G_L Entry
ORDER BY "G_L Account No_" ASC,"Posting Date"
ASC,"Entry No_" ASC
-- FINDSET; No
SELECT "timestamp","Entry No_","G_L Account No_",...
-- FINDSET; With
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.
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
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
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.
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”.
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.
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.
Any news on the inability to identify the NAV username from within SQL Server? An existing workaround - filtering on hostname - is not particularly helpful in a terminal server environment...
Thanks for your SQL related overview - very useful.