Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Compatibility Level 120 -> Lock and Speed issues
kball
Member Posts: 3
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?
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?
0
Comments
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table