-
NAV C/AL code vs NAV Query Object
Hi, Version affected in NAV2017 cu 14. Recently we hit a performance related issue. When I investigated further, I found out that this was caused by Query Objects in the Role Center. And when I tested, I even found out that it was better to use NAV C/AL to get the calculated results vs NAV Query object. This is because,…
-
Business Central Benchmark
Hello, I test the validation of accounting ledger under Business Central. I have an accounting sheet with 200,000 lines. I would like to optimize my installation and validate as quickly as possible the volume of information With a standard nav installation the system validates the sheet in 20 minutes The processors do not…
-
SQL Performance Query Object
Hello everyone! We have a performance issue with a query object in NAV BC14. We isolated the code snippet with the Performance Profiler that caused this issue and we stumbled upon a SQL statement (caused by an Query.READ) which needs 7-10 seconds to run through. The SQL statement looks like this: The code that calls the…
-
Heavy query with 2-Abort in profiler
Hi, During a (re)search for a performance issue using the SQL Server Profiler we found (large) query's with an Error '2-Abort'. Sometimes the reads are enormous (>200.000) and a duration of +73000 We see this query every time we open a sale/purchase order (or create a new) but not when we open a sale invoice (same table)…
-
Controlling VSIFT selection in BC
I'm working on tweaking a process in BC18 (On premise). Part of the process uses a Query object that is summing "Warehouse Entry" records. I've isolated the issue to the VSIFT index it uses to calc that sum. The 2 indexes of concern are Key5 and Key8. Both shown below. The process is summing "Qty. (Base)". The query is…
-
What is Smart SQL and does anyone have any documentation?
I see that there's an option to disable it, but I wanted more info on what it was and whether there was any documentation that I could review with my team as to whether we should enable it for our clients?
-
Lock request timeout period exceedd Error 1222
Hell everyone, My Sql is getting this error everyday, I'm using Navision BC , anyone can help with this? what could be the cause?
-
1 complex insert or multiple updates
Hi, I need to consolidate G/L entry table for 16 companies records are in millions I have written Insert into Select query with lots of Join statement where I am calling G/L account name, etc from other tables into 1 new consolidated table It works fine but to speed up should I simply insert only g/l entry fields first and…
-
How can we measure the time consumption of the Code in SQL?
According to the NAV TechDays 2018 - Performance: Business Central reloaded for the Cloud https://youtube.com/watch?v=jGdkpTGmCgs I saw that the guys were able to measure the time consumption of some pieces of code. the simple question would be: How can I accomplish that? NAV, SQL, 3rd party software?
-
ISEMPTY create SQL query run over 10 sec, and keep happening. How to tuning up
Recently our customer keep get table locking issue, and from NST event viewer, we found following information: Message <ii>Threshold Trace Tag. Execution Time = 10010 milliseconds Long Running SQL statement Task ID:3 SELECT TOP 1 NULL FROM "xxxxx_$Job" WITH(XLOCK) WHERE ("No_"=@0 AND "timestamp"=@1) OPTION(OPTIMIZE FOR…
-
Query Store and NAV Performance
Hi everybody, Does someone know if the activity of the Query store can cause performance problems in NAV 2017 ? Our customer is suffering from performance problems for a few weeks and we suspect that the query store is the origin of the problem... Thanks in advance for your reply. M.
-
RDP vs Client Performance NAV2009R2 with SQL
Hi All, I am by no means an expert in performance on NAV and SQL but I have been working with NAV long enough to have a “feel” for how it should perform. We are encountering a very strange situation and I was wondering if someone can shed some light on this or have some experience in this. When using Remote Desktop to the…
-
In one of our client is facing NAV Connectivity issues.
For our Client they are facing NAV Connectivity issues - > Time Out or connecting error message will come, in Same NAV DB they have written SSRS Reports for internal day to day activity purpose. Client DBA is saying from SQL Log they are getting more thread for Single SPID. Please suggest any tool is there to drill down…
-
Query 1501 "Workflow Instance" Performance
Has anyone had any experience (luck) improving the performance of Query object 1501 "Workflow Instance"? I'm working here with NAV 2016. As I investigate the performance of various processes I often find calls to this object account for a sizable portion of the processing time. This seems to be checking if a Workflow needs…
-
SQL Server consumed very huge memory (90% plus)
Hi All, one of my customer has issue about SQL Server. the server is only be used for NAV 2017, Jetreport and SQL Server 2014 they only use for 1 database with 20 users the moment we restart SQL, memory consumed will lower down until 25% once user login and used... it will raise significantly become 93-96% by the way, the…
-
Index and statistic maintenance
We have been reviewing the default parameters of the scripts we use in SQL to rebuild and maintain indexes and statistics on the SQL side. A bit of a newbie to this I have a few small questions about the default values which are actually recommended by Microsoft for SQL server in general, but might be better modified for…
-
SQL Server 2012 EE - using NAV VSIFT Indexed View when that is slower than the Primary Key
Dear Experts I have a very simple Query that selects records from a table where the timestamp (cast as a BigInt) is greater than a previously stored BigInt. It runs on SQL 2012 EE used to be quite fast (~100ms) and overnight slowed down (~5 Seconds). I looked at the execution plan and noticed it is using an Indexed View.…
-
SQL Query on Enterprise Edition slowed down using the VSIFT indexed view.
Dear Experts I have a very simple Query that selects records from a table where the timestamp (cast as a BigInt) is greater than a previously stored BigInt. It runs on SQL 2012 EE used to be quite fast (~100ms) and overnight slowed down (~5 Seconds). I looked at the execution plan and noticed it is using an Indexed View.…
-
New documentation on Monitoring Long Running SQL Queries using the Event Log
If you need to troubleshoot performance on Dynamics NAV 2017 or later: https://docs.microsoft.com/en-us/dynamics-nav/monitoring-long-running-sql-queries-event-log
-
SQL Wait ASYNC_NETWORK_IO on NAV Production
Hi All, While we are looking into NAV SQL Performance issue, we found that one of the top wait is "ASYNC_NETWORK_IO".As per my understading the NAV App server is not consuming the data generated by DB quickly. Our Prod Setup is: -2 Terminal services for NAV client, and they are load balanced. -2 App Servers are load…
-
SQL Profiler with NAV 2013 R2
I find myself using SQL Profiler more often these days for a few reasons. Mainly looking into performance related issues. With prior versions, it was easy to separate the activity of different users as they each showed up as their own login. Being able to know who a SQL statement came from, let me hopefully sort of what…
-
Performance Issue when Running Report in Dynamics NAV 2013
We have an issue when Printing reports in RTC time to time. Reports get delayed in rendering and previewing output for some users. There are two different set of users connect NST using terminal server and direct RTC connections. Terminal Server users are not getting any issues while others are experiencing preview issues…
-
How to Join [Active Session] tables with MSSQL SQL DB session
Hi All, Can some one help me to Join Nav's [Active Session] table info with MSSQL DB level session. Question:- how can i join below 2 queries Example:- Nav session:- SELECT [timestamp] ,[Server Instance ID] ,[Session ID] ,[User SID] ,[Server Instance Name] ,[Server Computer Name] ,[User ID] ,[Client Type] ,[Client Computer…
-
New documentation on "Troubleshooting SQL Queries Generated by Dynamics NAV"
Hi, We added new documentation on Troubleshooting SQL Queries Generated by Dynamics NAV: https://blogs.msdn.microsoft.com/nav/2017/05/22/troubleshooting-sql-queries-generated-by-dynamics-nav/ Hope you can use it Best wishes, Kennie
-
NAV - SQL slow performance
The users are using a classic client customized form and perform queries on it. This form is basically looking at two ledger entries tables. So when the users enter a value then it queries customer ledger table and other ledger table same time. This is slow. There always been users performing queries on Customer ledger…
-
NAV 2013 R2 Performance on SQL Server 2012
Dear Sir, The SQL Server is running on a well o:) configured Server. The Server Configuration is given below. 1. OS - Windows Server 2012 R2 2. RAM - DDR3 24 GB ECC 3. RAID 10 The NAV SERVER is also installed in the same server. Almost 112 concurrent End-users are accessing the NAVISION Database thru different clients…
-
Performance problem after hardware upgrade
Hi all I have performance problem with classic client NAV 5.01 My new server use high performance storage, 5 time faster from old raid SATA drive. When i operate with journals, reports , posting , general working with client, performance is the same as the old server. Where to look for the problems? SQL server or database…
-
Question about PAGEIOLATCH wait type
Calling all SQL Performance Experts! If you want a challenge, this is it! This is a LONG read, but I believe you should have everything you need to provide recommendations. The TL;DR version is at the bottom. I have a 24/7 manufacturing customer that has recently started to experience massive/crippling locking timeouts +…
-
Compatibility Level 120 -> Lock and Speed issues
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…
-
Upgrading from 2009R2 to 2013 taking a LONG time
I've already posted on the Three Tier forum and someone suggest I repost here, so apologies for any duplication: viewtopic.php?f=32&t=59121&start=0 To summarise the issue I'm having, I'm trying to upgrade a NAV2009R2 database to 2013, but at the point of doing the Ledger Entry Dimension upgrade, the UI freezes up…