SQL Performance

This topic has been created for all SQL-related topics about performance & tuning.

Performance in SQL
Hello. I have a customer working with Navision 4.0 SQL version for one year and a half. When database has become larger some tasks are getting too slow (such as displaying inventory in item's card). Is Optimize the only way to improve performance in SQL?. Is there any other options or whitepapers to read about?. Thanks so…
min memory per query on SQL
Has anybody have any experience with Navision and "min memory per query" on SQL server? Could you write your experience? Thanks.
Autoincrement in ledger tables
I have reviewed some of the previous posts about autoincrement. Also asked a Microsoft employee about it. His comment is the current locktable method will stay as long as there is a native database. Jörg A. Stryk wrote that he had implemented autoincrement, but added GUID as the clustered index to avoid locking problems.…
System Performance - SCSI vs SAS
Has anyone done any comparisons in the performance difference between SCSI and SAS drive systems? We are running two upgrades (of the same 160 GB SQL database) on two different servers. One server has SCSI drives the other has SAS drives. The SCSI machine has a lot more drives. The SAS machine is running at close to 3 time…
NAV 4.0 SP3 hotfix 6 (build 25143)causes slow SELECT
NAV 4.0 SP3 hotfix 6 (build 25143) causes slow SELECT cursor on big table We have an issue with a simple cursor on a select query of a big table (700.000 rows) that has a very bad performance on NAV 4.0 SP 3 hotfix 6 (build 25143). It performs well with NAV 4.0 SP3 without hotfix. A simple NAV form with two columns of this…
Effect of Dropping SIFT Buckets within Navision
Hello, Considering the following Scenario. I have Navision 3.6 running on SQL 2000. Looking at The Item ledger Entry Table (32) I find a Key with the following fields: Item No.,Variant Code,Drop Shipment,Location Code,Bin Code,Posting Date The Resulting SIFT Buckets are as follows: 1. Item No. 2. Item No.,Variant Code 3.…
SQL Profiler hi scores
Let's post the hi scores we find when profiling sql server. This is what i'm looking at right now type==duration====read====writes====CPU----------------------------------------------------------SELECT 3188 470896 16 3172SELECT 3137 470908 0 3078SELECT 3145 468914 1987 3094SELECT 3150 468133 2011 3125SELECT…
SQL Performance
We have 90GB database running in Native Database, we want to convert it into SQL server 2005 and we have also 41 user. Can any one sugest us what is the minimum hardware configuration for running and convert the database in SQl 2005 smoothly.
20,000 salers orders per day
I have submitted some questions to forum members who manage large databases already. What I am trying to find out is, does anyone have or know of sites that are running at least 20,000 sales orders per day along with shipping and the associated posting. We have a prospect who wants to run this volume and does not want to…
I've realized something important about performance
Say you are using 4 dimensions which can roughly be considered the average, common case... and you post a shipment & invoice. It inserts 10 Item Ledger Entries and then 40 Ledger Entry Dimension. Then 10 Value Entries and another 80 Ledger Entry Dimension. A couple of G/L Entries and, guess what, 4 times a couple of Ledger…
Perfmon
In BOL, they refer you to Windows for explanation of the PERFMON counters. In Windows, they tell you they are SQL Server counters. Using google and technet, I can come up with some explanations of the various counters, but nothing that addresses everything. Has anyone seen an exhaustive explanation with examples that…
SQL Locking
Navision Version 4.0 SP3 I've a question about the locking in NAV 403 on a SQL2005 database Always rowlock = Yes If we look at the following code, we see that we want to change the record recWhseJnlLine. That's why we've used the findset(true,false) The result is a locking of 9 records. There are 9 records which comply to…
Nav Sp1 5.0 Bulk Insert
Hello Navision has introduced with 5.0 SP1 Bulk insert. By my definition Bulk insert is actualling using BULK INSERT statement. What I see in query profiler is insert record one insert record two. etc etc. Is this actually bulk insert? or just that the records not insert until the commit and then inserted? Also if they are…
Strange behavior of CURRENTTRANSACTIONTYPE operator
Hello! I faced with a strange behavior of CURRENTTRANSACTIONTYPE operator. I have two sessions of NAV - the first is writing to the table from time to time and the second is reading. For the second session it is needed to read only commited data. I have the "lock timeout" option on and timeout duration of 300 seconds. When…
Inventory Valuation Performance on SQL
Does anyone out there have a good way to speed up the Inventory Valuation report using SQL? The problem with the report is that it goes through every item ledger entry in the system and finds the appropriate applied entries. The current process takes wwwaaaayyy too long! ](*,)
Navision read performance on a big table is very slow
We have a big table with 2M records. 15 fields. We have keys for 4 searchable fields. When users try to search on this table, it is very slow, and many times they get locked up. these are the configuration: 1) sql 2005, 12 drives RAID 10, log file is on seprate drive. 4G RAM. 2) 150 users. half of them Citrix users. 3) NAV…
SQL Pre-processing in Microsoft Dynamics NAV 5.0 SP1
I would just like you to take a look at this new behavior of NAV5SP1: http://blogs.msdn.com/microsoft_dynamic ... n-sp1.aspx It seems to be a good solution, isn't it?
Sql Performance
Hi We are using SQL Database....but every time we conncet to the SQL SERVER...the performance is very slow...why is that happen..do I need to Adjust/increase the OBJECT CACHE in NAV or It is the SQL server. Hoping for your reply :D
Deleting Zero SIFT Records
Hello, I have spent some time optimizing my Navision Database (NAV 5.0 on SQL2000) and have had quite a bit of success doing so. However, I am a little confused about what should be done with respect to SIFT maintenance. I have looked at the SQL Server Stored Procedure "ssi_delzerosift" provided by STRYK System Improvement…
Stopped Query
Hi If we lookup on article for inventory we had sometimes a very long response time. Activity Monitor: 1 open Transaction, State: stopped, worktype: PAGEIOCATCH_SH, no blocking process, Profiler: duration 58s, reads 103115, query: select * from [company$Item Ledger Entry] where [Item No_] = 'xy' At the blocking time we can…
can't understand what CLUSTERED property means
hi what means exactly the CLUSTERED property in a nav key? regards
Using Autoincrement in Warehouse Entry
Navision 3.60 code running on Navision 5 clients and SQL 2005 We have had performance issues and deadlocks in one company on our system. This company posts only Orders with warehousing. We found deadlocks happening in a few scenarios. We have changed, in our test system, the warehouse entry table to autoincrement. We have…
Same function running at different speeds
Truely baffled here. Nav5execs on 3.60 code base. SQL 2005. I have one function in a codeunit 5000 thats run in two companies. In it is the following code: PurchaseLine.SETCURRENTKEY("Document Type",Type,"No.","Variant Code","Drop Shipment","Location Code","Bin Code","Expected Receipt Date");PurchaseLine.SETRANGE("Document…
NAV4SP3 vs ”NAV4SP3 Hotfix 6+6.1+6.2+6.3 and IndexHint=No”
(Using MS-SQL 2005 – no servicepacks, yet) I’m currently in the process of migrating a Native NAV4SP3 to SQL, but encountered a strange behavior. First step was to create the DB and import a backup. This was done without problems and the performance was acceptable. This was done using a plain NAV4SP3 without any hotfixes.…
SQL Tuning for NAV
Hello, We have a customer that migrated to a new SQL server (2005) from a SQL 2000 server in NAV 4.0 SP3, and now process are taking longer than they used to on the SQL 2000 server. I am doing my own analysis and research, but if anyone has any checklists or suggestions to check, it would be much appreciated. Thanks!
Activity Log Entry
I'm using Testing duration using activity log entry. for sample, I try to use Sales Header. Total records = 52418 rows. my codes :ActivityLog.Description := 'Loop 5';ActivityLog.Start;SH.SETFILTER(SH."No.",'<%1',xxxx);IF SH.FINDSET THEN REPEAT ActivityLog."No. of Operations" := ActivityLog."No. of Operations" + 1; UNTIL…
SQL2005 -LIKE and comparison (<,>) Operator
Hi, after testing and checking our source, i woundered why the SQLServer 2000 and 2005 do not the same. The source was fine and i found the problem on SQL Server side. The Problem is, that the 2005 use bad exec. plans because he use the comparison operater (>,<) instead of the Like operator (SQL 2000 behavior) So the…
Database Performance Statistics
Hello All, If one is asked to provide the Performance statistics of a Navision Database (Either SQL or Native Database). What is Expected that one do?
keys in Nav
Hi master, When I compare nav4.0 SP1 and Nav5.0 there is change in key. for example in Sales line. the primary key is "Document Type,Document No.,Line No." (nonclustered) and secondary key is "Document No.,Line No.,Document Type" (clustered) If I want to change my key in nav4.0 SP1 same as Nav 5.0, is it true if I just add…
Severe SQL Server 2005 database performance issues
We are having severe performance issues on Nav 4.0 running against SQL Server 2005. The disk performance is clearly a problem and is being addressed with a hardware upgrade. After that is anyone having major problems with concurrent user activity when running against SQL Server?

SQL Performance

Discussion List

Categories