-
NAV 2016 - Index Hinting?
Is Index Hinting available in NAV 2016? I don't find anything in the MSDN documentation on it. If not, is there a recommended alternate solution to controlling index selection in queries? I have a situation where certain filter values are resulting in non-optimal query plans. The particular table (Item) has about 35,000…
-
Good book on SQL performance
Hi everyone, Can you recommend me a good book on NAV-SQL performance? Thanks in advance :smiley:
-
Change MaintainSIFTIndex property for a key
Hi everyone, I have a key in a table that is not used anymore.Both properties MaintainSIFTIndex and MaintainSQLIndex are set to Yes. The SIFT index associated to this key is causing a performance problem on a customer database. What shall I do about it? I do not want to delete or deactivate the key. If I set the property…
-
Weird behavior in Sales Orders
Hi experts, First of all I hope I created this thread in the correct session. If not feel free to move it to wherever it belongs. One of our clients started to have performance issues when selecting a particular Item in a Sales Line ... Let's name this item XYZ. As soon as they validate the Quantity the whole Sales Order…
-
SQL Query Performance
Good day all I am joining Navision tables to get a certain result firstly I am analyzing the values in the value entry table for a specific customer in NAV vs my OLAP Cube report and the values do match , my analysis will be done in Tableau later. So in order for me to get the Sales Invoice Line and Sales Cr_Memo data…
-
NAV 2009/2013/2015/2016 on SQL2014 - Use Delayed Durability = FORCE or ALLOWED during data upgrade?
Someone in another thread mentioned using Delayed Durability = FORCE or ALLOWED during data upgrades if the SQL Server is SQL2014. The article he referenced is: http://sqlperformance.com/2014/04/io-subsystem/delayed-durability-in-sql-server-2014 If you do a demo install of NAV2016, Delayed Durability is set to "Disabled".…
-
Will SQL Diff backup lock table?
Recently, we faced a problem with sql diff backup. Our client is using NAV 2013 R2, we take sql diff backup everynight, sometimes some users still use NAV at that timing, so there will be table lock happens and the NAV Instance just restart automatically. We asked Microsoft, they said this is normal because sql backup will…
-
Using plan guides
Hi all! Could I have a problem if I use sp_create_plan_guide to create a fixed plan guide for a specific query in NAV2013+? I have a situation where there is a table with Entry No. as CI and Table No., Entry No. as NCI. I need to filter that table by Table No. and find first Entry No. occurring after specific entry no. so…
-
sp_updatestats and indexed views
Ok, I know there is several more threads with similar topic but something bothers me regarding advice to disable autocreate and autoupdate of stats while maintaining them by scheduled jobs (at least for pre-NAV2013 clients). Advice was to use sp_createstats indexonly and sp_updatestats procedures at some schedule (daily).…
-
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…
-
Rebuild index and fill factor
Does T/SQL rebuild index command with no fillfactor set use fillfactor = 0 or fillfactor set on index or fillfactor set on server?
-
Performance - Coding best practises
Hi everyone, I have been writing NAV code for a while now but I am aware that in many cases, my code isn't really good when it comes to performance. I am wondering if there are any articles or books or special hints you can recommend me for coding better performance? Thanks in advance :)
-
Change of SQLIndex property
Hi, I've been doing some reading on changing the SQLIndex property to achieve better index selectivity in SQL. My particular interest is in T355 - Ledger Entry Dimension. I do wonder what would happen if the PK would start with "Entry No." instead of "Table ID" (in the SQLIndex). Unfortunatelly, I do not have a test…
-
Debugging table locks in NAV 2013 and later
What is the best way to find what process is locking a table in versions of NAV where the SQL access happens as the user running the SQL service? I guess you may be able to manage it with the SQL profiler with full SQL tracing turned on. However we have a customer who is having a lock happening once a week or so. We cannot…
-
Possible memory leak on sql 2k5 SP2
Hi All... We have a customer running Navision on sql 2k5 SP2, win2k3 (x86). Latelly they reported slow Navision so I started digging and found some strange counter values that looks like memory leak... Server has ~10GB RAM, and is set with PAE switch. SQL Server is not with AWE at this moment only for a reason of stable…
-
SQL Isolation Level
Hi, My client is using NAV 2013 and the current Isolation Level is Read Committed. They are experiencing the Table locking and dead locks. I have come across various blog with the suggestion that the Isolation Level should be changed to "Repeatable Read" from default("Serializable"). Can somebody suggest if "Read…
-
Deadlock resolution on NAV 2013
Hi All, Our client is using NAV 2013 and experiencing deadlocks. We have been able to reproduce the issue with 7 users doing the same activity. The issue was resolved by putting an explicit lock on one table. However when the changes were deployed on Prod, it backfired as the other activities were not having the same…
-
NAV 2013 R2 - Sales Order & WMS Posting
We've been looking into squeezing some more posting speed on a site that is getting close to go-live. Posting speed is important to them, as often a customer will be standing there waiting. With the usual index, SIFT, and minor code tweaks we've gained about a 20% reduction in posting time. Using a 100 line example sales…
-
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…
-
No Of Locks, No_Of_Blocks - Understand the values
Hi guys Hi gys I run the SQL script from Styrk and I got this values regarding locks and blocks: Objs JobQueueEntry (No Of Locks:653485 No Of Blocks:544 and Block Wait Time:20715) Sales Line$VSFIT$23 (No Of Locks:36564No Of Blocks:29 and Block Wait Time:82898) Sales Line$VSFIT$38 (No Of Locks:71592 No Of Blocks:17 and…
-
Extent Fragmentation on indexes
Hi all, I have a question on the extent fragmentation. How can I get the percentage of extent fragmentation down? For example I have an Index on the G/L Entry table ($4) , logical fragmentation 0%, Extent fragmentation 27.27%. When I do a rebuild on the index the number remains the same. So I tried to drop the index and…
-
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…
-
This hotfix solved our NAV 2009sp1 SQL performance problems
We've been struggling with performance and I think we finally found a fix worth sharing. Environment: - NAV 2009 SP1, classic only (Build 31793) - 150 GB database on SQL 2008 r2 - 6 companies - Licensed for 116 users, average about 90 concurrent (we love ExpandIT) - Lots of add-ons and customizations Until now, we've…
-
Nav-sql slow perfomance + WM + Citrix
Hi We have WM(win 2003 sp2 4Gb). User connects to nav(3.7) client(on this wm machine) in citrix. Nav Client connects to SQL DB(2008 R2) on another server. Everyting is good. But in hour of intensive work we have a picture: nav starts "exec dbo.Proc....." and it hanging... no sql activity... it may hangs 20-40 min and then…
-
SQL2012 and iSCSI or Fusion IO
Hello i do not know if this is the correct forum but i hope. We are a company with about 400 employees and want to upgrade from NAV2009 (technical - only Classic Client ) to NAV 2013. We have a database about 350 GB. Now we are testing new funktions and have two systems for this. A VM-Ware system with iSCSI Storage that is…
-
Index Rebuild
Any idea on how long (estimate) an index rebuild would take on a 80gig NAV2009 SP1 DB. On a SQL2008 server with 16gig mem. The DB has never had this done to it before and we are experiencing performance issues. I'm looking to set up a maintenance plan to do this. I know this might be hard to answer, just looking for a…
-
bak file back up
Hi expert, is there any way to automatically back up the bak file by schedule from the schduled task of windows? Thank you!
-
Planning/Requisition Worksheet order tracking policy is slow
After enabling order tracking policy it takes like four times as long to run the planning worksheet (from one hour to four). I have looked through the inventory profile offsetting and can find a lot of find('-') and was thinking of replacing them with findset but then I read that I should only do this if the records…
-
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…
-
Use filtered index
Hi, I have a problem and hope that you can help me. The following statements make some trouble: SELECT * FROM Customer Ledger Entry WITH(READUNCOMMITTED) WHERE "Posting Date" >= @P1 AND "Customer No" = @P2 ORDER BY "No" OPTION (OPTIMIZE FOR UNKNOWN)SELECT * FROM Customer Ledger Entry WITH(READUNCOMMITTED) WHERE "Posting…