-
changing the NDF files size
Hi i have big DB with 7 NDF Files. as below i want to have all NDF file to be same size. how can i do it?????? ](*,) File Name Size (MB) File Growth Data.mdf 267 10% 1_Data.ndf 221803 8% 2_Data.ndf 100000 20% 3_Data.ndf 100000 20% 4_Data.ndf 123346 15% 5_Data.ndf 117468 15% 6_Data.ndf 11066 40% 7_Data.ndf 20000 40%
-
Poor Performance when reading a SQL View via a NAV report
Hi, I'm trying to optimize a physical inventory generation in NAV2009. Client has 3,000,000+ Item Ledger entries, 65,000+ unique items and 50+ Inventory locations. I have built a version of the "Calculate Inventory" process that uses a view that I created. The view summarizes the quantities (basically provides a Qty on…
-
Maximizing throughput while minimizing blocking
If you have a long-running process in NAV that is causing excessive blocking of users, you can use a stored procedure to detect blocking, and prevent/minimize it: http://www.dynamics-nav.org/?p=100
-
GET vs FINDFIRST
Hi, It looks like there is big difference when you use FindFirst as compare to GET FINDFIRST is much better than GET I have tried on Setup tables like General Ledger Setup SQL statements are generated like this. FINDFIRST - Select TOP 1 ...... ORDER BY Primary_Key GET - Select * ..... WHERE Primary_Key=@P1 Also please…
-
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?
-
Primary Key
Hi, Just a quick question. I need to create a table which will be heavily used every day by more than 100 users. Table will have Document No. and Task No. as major fields Should I create a table with 2 fields as primary key or 1 field Table 1 (primary key on 2 columns) Document No. Task No.(must be code data type) DOC001…
-
does not generate hint..
IndexHint=Yes;Company=;Table="Sales Shipment Line";Key="Bill-to Customer No.";Search Method=;Index=5 SalesShptLine.SETCURRENTKEY("Bill-to Customer No.");SalesShptLine.SETRANGE("Sell-to Customer No.",'XXX'); SalesShptLine.SETFILTER("Qty. Shipped Not Invoiced",'<>0');FORM.RUN(0,SalesShptLine); results in SELECT * FROM…
-
Filter on FlowField
Hi I'm working with a quite big solution, the overall performance is very good, everyone is happy. However, I've been asked a few times, whether and how it would be possible to speed up filtering on flow fields, for example on "Balance"-Field on Table customers. Thanks in advance Thomas
-
Setup SQL 2008 Disks HELP!!!!
Not sure if anyone has an opinion of the best performance practice when setting up SQL 2008 servers to host Nav 2009r2 Setup Navision to write several independent files within the Navision \ alter \ database files d:\data1 d:\data2 d:\data3 d:\data4 – D: Drive (Navision SQL Data) • xxx GB RAID 10 (4 x xxxGB, U320 SCSI, 15K…
-
Navision 4.0 SP3 Build 30698 - SQL Indexhint
Hi All Would like to request your help on the below. Recently we converted Navision 4.0 SP1 (SQL 2000) database to Nav 4.0 SP3. (Executable upgrade). Now facing performance issue, system very slow. Does this performance issue anything to do with SQL Indexhint? In the build 30698, Is the SQL indexhint ON by default? Please…
-
Slow - Server or NAV
Hi, This is quite common Navision gets blame if it is slow. I have identified many times the whole system is slow, even if you do something outside NAV. Is there any tool which communicate with server and check if server reply is slow like Ping also how can i check what is running on server in the background other than…
-
Hardware Requirement for NAV 2013
Can you please advise, I don't know much about server. we are 300 users. HP ProLiant DL360p G8 1U Rack Server - 2 x Intel Xeon E5-2603 1.8GHz - 2 Processor Support - 8 GB Standard - Serial Attached SCSI (SAS) RAID Supported, Serial ATA/600 Controller - Gigabit Ethernet HP RAM Module - 8 * 8 GB - DDR3 SDRAM - 1333 MHz…
-
Table Locking
I know there are lots of threads for performance & locking objects I am looking for your views on this In few threads it is said that NAV/SQL lock the record not the whole table. but I believe it locks the whole table rather than just 1 record if 100 users are working on the same table but with 100 different records they…
-
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…
-
tempdb - Optimizing tempdb Performance
Hi guys, i want to know how can i optimize tempdb. I know from styrk that we must have "N" Files of tempdb database for "N" CPU´s that we have. Questions: 1. How can i acomplish that? 2. Did you see great improvements by doing that? Thanks for your answers. :D
-
How to force a user to always be non-locking?
The automated user that extracts our Dynamics NAV database tables into the data warehouse locks the tables with a locking type of LCK_M_S. This isn't good, because the data warehouse extract takes a very long time, and is blocking our users from doing their jobs. The guy in charge of our data warehouse said that there…
-
Posting speedup when using Analysis Views
Hi all, when using Analysis views and posting GL entries, which don't match dimension criteria, NAV 2009 in Codeunit 410 generate very huge SQL query which take tooooo long. When you want speedup, update code in UpdateEntries procedure fromIF LedgEntryDimEntryNo < MaxNumber THEN GLEntry.SETRANGE("Entry…
-
SQL NAV 2009 SP1 Issue
Hi, I would appreciate some insight to the scenario below. A client is experiencing performance issues in NAV 2009 SP1 Build 32029 for the RTC Calc and Post Vat Settlement. The environment is as follows: 1. O.S. Windows server 2008 R2 Standard Server 64 Bit 2. SQL 2008 R2 Standard 3. RAM 32gb (SQL limited to use 27gb) 4.…
-
Filegroups in SQL Server 2005 on an iSCSI SAN
We have a 1TB (approx.) database of Microsoft Business Solutions Navsion v4.0SP2 with LS Retail. I have found through google search the document "Tuning Navision for better performance" by Patrice Dupont-Roc. One of the optimisation he proposes is to split the database in 6 filegroups (allocating the tables to each…
-
Poor performance when renaming record on sql database
Hi, I have done an SQL migration for a customer from the native database NAV 3.70 to NAV 2009 R2 SQL. After the migration we have a sorting problem in the cost center table (table 11). Some cost centers have 5 digits while the rest has 7 digits. So according to our customer I could solve this problem by just adding "00" as…
-
LOCK on concurrent MODIFY, but not the same record
Hello, I am trying to avoid Lock between users when they are modifying data. I have some locks even if modifications are not on the same set of records. I made a test with 2 users: one is modifying customers whose name is beginning with "a" (filter = 'a*'), the other is modifying customers whose name is beginning with "b"…
-
Performance Analyzer for Microsoft Dynamics
Hi All "Performance Analyzer 1.15 for Microsoft Dynamics" I found this tool sometime ago but wondered if anyone here is or has used it. If you have any experience could you share this with me. It seems to be used for AX analysis but as NAV is included does anyone use this for Performance monitoring with NAV? Thanks!
-
Sql 2005 Profiler showing "Update ENVSET_1 Set CTLIDX"
Dear All, I am using Sql Profiler 2005 to find slow running queries but under SQL : BatchCompleted EventClass it is showing following statement and it is showing duration 771101 in miliseconds. I am not understanding this statement please help me & tell what it means so that i can perform some task to fast my SQL server.…
-
How To find slow running queries on SQL 2005
Dear All, I am running following query on SQL2005 Server to find slow running queries :- SELECT creation_time ,last_execution_time,total_physical_reads,total_logical_reads,total_logical_writes, execution_count, total_worker_time ,total_elapsed_time, total_elapsed_time / execution_count avg_elapsed_time,SUBSTRING(st.text,…
-
SQL Server 2008 Standard vs. SQL Server 2008 Enterprise
A customer is working with SQL Server 2008 Standard Edition with a database of approx. 30GB (size in SQL), 1 disk for the OS (64-bit) and SQL, 2 disks for the database files and 1 disk for the log-file. (All mirrored) To improve performance they added memory, from 32GB to 64GB (due to the fact that almost all of the memory…
-
View SQL code by navision table name
hi folks, i have no sql experience thus sorry if it sounds stupid to you.. when i run report that will export to excel.. i got an error message saying: The Excel Buffer table cannot be changed because it is locked by another user. Wait until the user is finished and then try again.
-
Create new index in a table
Hey, I analyzed a job which has a very long runtime. In the client monitor I saw that one SQL-Statement has a runtime around 900ms. This statement uses no index key, so in order to improve it I want to set an index. The sql-statement looks like this: SELECT SUM("Amount") FROM "XXXX" WITH (UPDLOCK, ROWLOCK) WHERE (("Ledger…
-
SQL Diagnostics Queries
I did a search and nothing came up. Glenn Berry is a SQL MVP and I have been using his "SQL Diagnostics Queries" consistently when troubleshooting NAV/SQL performance issues. Here is a link to his SQL 2008(R2) Diagnostics Queries, but he also maintains versions for SQL 2005 and 2012:…
-
unexpected result after index maintenance
Dear Experts On of my client is using NAV 5.0 and they are having Database on sql2008 of around 530 GB so there main pain area is both Disk Space & Performance.As suggested I executed index maintenance on value Entry Table but the result was shocking for me as it increased my Database size.I know that log File are…
-
Key Information Tool for the Classic NAV 6.0 - SQL Server
Hello, I'm having SQL Performance problems with NAV 6.0 and after reading a lot (including Jörg Stryk blog) I've decided to check mi VSIFT Buckets in order to reduce their maintenance as suggested (or al least I understood). The article link is attached here The White Paper named "Microsoft Business Solutions - Navision…