Compatibility Level 120 -> Lock and Speed issues

kball

Hello all,

we have recently upgraded from NAV 2009 R2 to NAV 2016. We are using SQL 2014 as database.
On NAV 2009 the compatibility level of the database was 100 (SQL 2008 R2). When we upgraded to 2016 we used compatibility level 120 (SQL 2014) for the new database.

We have experienced many issues with performance and locks on the database.
This not only happens when we are using certain reports in NAV, it also happens when we connect to the database via ODBC.

Let me give you two scenarios we are using:
1.) Connecting via ODBC:
We are connecting via ODBC to the database to make queries (sometimes via MS Access, other times via BI-Tool). Of course we are using read only to make to queries. There are certain queries which run for some minutes. We can now see on the SQL server that this causes different types of locks. Most times we see LCK_M_X and LCK_M_U locks. This causes the NAV client to freeze for all users until the query is finished.

2.) Reports in NAV
We have a report in NAV which deletes contacts. In NAV 2016 we have experienced big issues in the runtime and many locks as well. In NAV 2016 it deletes a staggering amount of one contact per minute and the runtime causes the database to be locked big time. In 2009 we were able to run this report during the working day and we did not have any locks, because the time to delete a contact was short enough.

Together with my NAV partner we have searched for an answer for many hours and by accident we have found a solution.
We have changed the compatibility level back to 100 (SQL 2008 R2). We could see immediately that we have no issue anymore.
Both of the scenarios i have described run fast and without any locks now.

This helps us and our users, but we do not understand this at all.
Did anyone of you experience anything like this or does anybody have an explaination on the reason of this strange behaviour?

yuran

Hi Kball, have you done the re-building all indexes after you complete the database updgrade? We've also upgraded the database from NAV 2009 R2 with SQL 2008 R2 to NAV 2016 with SQL 2014 but the client has not got issues regarding the performance. They do 1000s of inventory transactions daily.

kennie

Regarding reading data using ODBC: do you use a NOLOCK hint in your select statements? If not, then you might unintentionally inflict locking

https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

kennie

I will also add a task on more testing on compatibility levels to our backlog