-
NVMe Drives vs SAS SSD
Hello Has anybody looked at the server hardware recently for NAV db on sql server? NVMe drives don't support RAID so I'm trying to figure out if it worth getting them. And if we do, what should be put on it? Just the Tempdb? The current server db size is 800 gigs with 200 concurrent users. Any experience is appreciated.…
-
Slow SQL Update from ADO .NET
This is NAV 2016 I am using ADO .NET to interact with an external SQL database on a different server. The connection is done thru a Linked Server. Everything is working fine so far except for 1 update statement. The statement is updating a small set of records (~4) in a fairly large table (~1 million). The table contains a…
-
copy all views
Hi experts!! How can I copy all views from one sql 2008 r2 database to another in sql 2012? Thanks!!
-
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…
-
SmartSQL
What are your experiences with smartSQL? I have seen major improvements in general performance when disabling SmartSQL. NAV is much more responsive and the number of locks is reduced. So far I have not had time to investigate Query plans for when smartSQL is on and off. But it seems like smartSQL is not so smart?
-
Good book on SQL performance
Hi everyone, Can you recommend me a good book on NAV-SQL performance? Thanks in advance :smiley:
-
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…
-
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).…
-
Adding Sum Index Field when does it populate?
I have added a sum field to a few keys on a table. There were exisiting fields there my question is when does SQL go in and calculate the SIFT for the newly added field? 2013R2
-
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…
-
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…
-
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…
-
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…
-
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…
-
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…
-
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…
-
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…
-
sql execution plan 2k5
Hello I ran the following code in query analysizer on sql to study it. dbcc show_statistics ([Jason Pharm Test$Sales Shipment Header],[$1]) The table is Sales SHipment Header. The key in navision is Order No., No. The density is .9997 There are 42696 records in the table. The following sql statement took 3130 ms. SELECT *…
-
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…
-
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…
-
Recovery Model affect Performance?
Hello, has the Recovery Model an impact on the performance of the SQL Server? We are using SQL-Server 2008 R2 Entpr Edtn. And we have a heavy performance issue. And it seems to be that this problem occur after we switched from the simple to the full recovery model. We use LogShipping for Backup / Failover. So the full…
-
OPTIMIZE FOR UNKNOWN
Hi, on 2008+ newer builds of NAV support OPTIMIZE FOR UKNOWN, but I've noticed that only the client sets this, NAS still does the regular prepare/execute. Is this by design?
-
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…