25. Oktober 2016

Tooling for database lock and performance monitoring or how NAV 2017 on-prem benefits from Dynamics 365

While Microsoft can’t state more explicitly and loudly that they have an and strategy (meaning Dynamics 365 and Dynamics NAV on-prem) there are still rumors and concerns about the future of NAV on-prem. From my personal point of view I don’t see at all why Microsoft would risk their huge customer base of approx. 100.000 in the on-prem market especially as even in 2019 according to market analysis the on-prem ERP market will be almost double the size of the Cloud ERP market. It doesn’t make any sense at all to not continue to support this long-term especially with the way NAV has performed in the last few years.

However one can’t deny that for the first time since a lot of years vNext of NAV on-prem was not a topic at all at Directions EMEA and it also obviously is true that the main investment is going into Dynamics 365, “SaaS-ifying” the solution and building a true cloud ecosystem around it because that is where fast growth can happen. But whatever is built for Dynamics 365 that also makes sense on-prem should be in NAV on-prem as well and you can already see it: This is true for application enhancements in core financials, for technical additions like Extensions and also for general improvements like SmartNAV or notifications. Getting new partners to join in the Extensions and AppSource fun is difficult with C/SIDE and the whole database-bound development and delivery model. The new VS Code based dev environment promises to solve that problem but it also solves a lot of problems for traditional on-prem deployments (if you are able to use Extensions). While all this could also have happened if Dynamics 365 never came into existence there are also some benefits that I think never would have come or at least a lot later if not for Dynamics 365. And that also makes absolute sense: If you don’t have to run or host NAV yourself you probably consider administration or hosting tooling not your first priority. But if you are all of a sudden a hoster yourself those aspects probably will get more attention. An example of that is the new tooling for database locks as presented by Kennie Pontoppidan as part of his very interesting session about new SQL Server features at Directions EMEA. We’ve been asking for this since we started looking into 3-tier and now we get it, I guess not coincidentally at the same time as Dynamics 365 is going into production and Microsoft becomes a large scale NAV hoster.

For those not familiar with the problem: On the old 2-tier architecture whenever one user got blocked by another user because they were both trying to write something into the same database table, the second user got a warning stating the table causing the problem but also naming the first user. With that information, you could find out what the first user was doing and improve performance, reduce locking or whatever was needed. At the very least for a lot of customers those two colleagues could just talk to each other and figure out how to time their actions so that they didn’t interfere. With the 3-tier architecture all the user gets is a message stating that the table is locked by someone else. Because of connection pooling between NAV Server and SQL Server even in SQL Server you can no longer identify the user causing that lock as all connections are opened by the user account running the NAV Server instance. There are ways to sort of get to the needed information but only if you really know what you are doing and sift through a lot of SQL Profiler logs. Not really an option for end users or even administrators but only for a skilled SQL administrator together with a NAV developer. In a SaaS world this is just technically impossible as neither partners nor customers have the necessary access to SQL and NAV Server.

But fortunately with NAV 2017 we now have a solution for this with the new database locks page and virtual table. It shows all the information you need: User locking the table (NAV user, not SQL user), locked table, C/AL object and function causing the lock. With that information it is even easier than it was with 2-tier to get to the root of the lock.

There is only one caveat: The NAV and C/AL information will only be available if you open the page in a session running on the same NAV Server instance as the user causing the lock. Makes sense and even in larger environments it should be possible to get to that situation. But sometimes the user only tells you after the fact or can’t reach an administrator for whatever reason while the lock is still there. That means that when you open the page, the lock might be long gone. NAV 2017 has a solution for that as well: Deadlocks are logged in the Windows Event Log including the Deadlock graph so that you can analyze later what caused the issue. Pretty neat if you ask me. The event message looks like this (I am blocking myself in two different session):

Processes involved in the deadlock:
Process id: processb67506188
Is victim: True
User: FUM-GLOBAL\TFENSTER
SessionId: 93
SQL TransactionId: 68686434
AL ObjectType: Page
AL ObjectNumber: 50005
AL ScopeName: test2 - OnAction
 
Process id: processc7f02dc38
Is victim: False
User: FUM-GLOBAL\TFENSTER
SessionId: 89
SQL TransactionId: 68686391
AL ObjectType: Page
AL ObjectNumber: 50005
AL ScopeName: test1 - OnAction

You can find the official documentation here. One could rightfully argue that this feature is about five years late but better late than never and I think this shows the increased interest in administrative tooling by Microsoft. It would also make a lot of sense to have some kind of resource government if you are hosting thousands of tenants in Dynamics 365 which in turn could also help on-prem or private cloud installations. The first glimpse in that area is another new feature which logs every SQL statement that takes more than one second to the Windows Event Log. This should be improved with a configurable threshold as there will be a lot of log entries on a multi-customer environment but again it is a good start and shows what can happen if the company creating a piece of software also hosts it.

I think in the long term only partners with a good cloud business strategy will be able to keep in the NAV business. And that isn’t a bad thing at all as obviously Dynamics 365 opens up a lot of new opportunities, helping both Microsoft and NAV partners to grow. But even in the short term the improvements made for Dynamics 365 also improve NAV 2017 on-prem and will help to drive our traditional business in on-prem or private cloud installations. While currently the amount of Dynamics 365, Extensions and Events information is a bit much, the direction and strategy of the product seems very sound and built with both short and long term success in mind. If you think back only a couple of years ago and compare the state of NAV then and now it is an almost unbelievable progress. The move to 3-tier (and RDLC) was very painful and costly and the move to Extensions will also take some time although probably not nearly as much. But you already get improvements right now with NAV 2017 and the opportunities with Dynamics 365 are even bigger, not to mention that finally the development environment will be something that at least resembles something closer to what you expect in the year 2016. Also five or maybe ten years late but again, better late than never. And who knows, maybe concepts like session failover allowing seamless load balancing or easier access to custom SQL statements for improved performance will also find their way into the product if Dynamics 365 really takes off and Microsoft decides to invest even more.

Der Autor

Tobias Fenster

Kategorien

Dynamics 365, NAV, on-prem, SQL

Schlagwörter

Blocking, Cloud, Locking, Performance, SaaS