-
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…
-
Slow FIND/FILTER on 3.60 SQL under 50SP1 client
Hi: We have a few fairly large tables - Vendor Ledger Entry (1.2 million records) and Job Ledger Entry (3.1 million records). When using a non-primary key (e.g. "Document No, Document Type, Vendor No." for Vend Ledg Entry), FINDs and FILTERs under the 5.0SP1 SQL client are very slow - e.g. 20 seconds to find one document…
-
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…
-
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…
-
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…
-
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
-
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
-
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…
-
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
-
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…
-
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…
-
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…
-
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,…
-
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…
-
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.…
-
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…
-
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"…
-
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.
-
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.…