-
Convergence: SQL Server: Understanding locking
Luc and kriki asked me to post the convergence sessions about sql performance in this forum... :) During this session, the speaker used a lot of diagrams and graphs, which is quite difficult to put into words ... . There are numerous lock types: X: exclusive S: shared IS: intended shared ... Some locks are compatible with…
-
Convergence: SQL Server and Win. Perf. Counters That Matters
Luc and kriki asked me to post the convergence sessions about sql performance in this forum... :) What to start looking at ... . Why should you tune your database? Loss of user productivity Cust of new hardware Harware may only temporarily resolve Understanding load being placed on your server ... Seems logical, doesn't…
-
Deleting Key vs. Disable Key in SQL
Performance wise, does it matter?
-
New keys
Hi, I would want your opinion. I have created two extra indexes? Why? Because my customer is using NAV and Filters in the most exotic way... The question is in which order you would place the fields... . I know, the most selective, but it's hard to say which are most selective... #-o Index No. 1 on Sales Line table: - No.…
-
'NEXT' in 5.0 version
Hi, In version 5.0 with SqlServer2005, has "NEXT" any improvement? Thanks Asier
-
Never use WHILE FIND('Which')
I 've read this in a document writted by SqlPerform. Why? What's the problem? Thanks Asier
-
SQL2005 Hangups...
Hi all... I'm experiencing a strange hangups on client site: SQL 2005 NAV 4.0 SP3 one navision is acting normal (and that one is on the same machine as sql server) others are experiencing extreme hangs, kind of => whatever you click in NAV, you wait for a minut or a while for response. Turned-off firewalls and any…
-
Analysis View: s-l-o-w update on SQL Server 2000
One of my clients (running Navision Attain 3.60 on SQL Server 2000) reported extremely slow update of their analysis view after adding a new dimension. Their database contains only some 90,000 G/L Entries but after adding the new dimension code and confirmed deletion of existing analysis views, a renewed update took…
-
Performance Issue on posintg sales and purchase invoice
The problem we face is posting performance issue, once the problem occur all posting will very slow, one invoice with 7 lines might take more than 3 mins to post. We are now resolve the problem by restart DB server then everything back to OK but we face this situation nearly once a week. I would like to know what root…
-
Need Tips On SQL DB performance issues
Hi Can anybody give the list of points to increase the performance with Navision SQL DB. 1) Do I need to click the optimize button for each table OR is there nay facility to Optimize all the tables. 2)I am already using the SQL commands like FINDSET,FINDFIRST etc., is there anything i need to optimize my code 3)I got one…
-
Huge Cust. Ledger Entry table on SQL
Hi there I'm running af 4.02 SQL runtime (3.60 base), where I'm experiencing a rather big delay when posting payments in a standard journal. The problem is table 21 Cust. Ledger Entry which consists of more than 9 million records. The posting routine is complete standard using the standard defined keys, but it's extremely…
-
Sift Tables
Hello I was reading this column on persisted columns on sql 2k5. This looks like a solution for sift tables. You could instead of sift table use persisted fields. Anybody ever thought about this? Here is an example CREATE TABLE [dbo].[Invoice_Table]( [Invoice#] [int] IDENTITY(1,1) NOT NULL, [Invoice_Amount] [money] NOT…
-
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 *…
-
sql optimization webcast second part
Ok saw the part. Most of the topics have been discussed in here. So it was a good refersher. One thing that was mentioned in there was the tempbd purpose and that there isn't realy a good reason to put it on separate disk of raid1. That's because navision isn't creating any complex queries such as inner outer joines. So my…
-
SQL index changes in 5.0
Hello I was looking at the beta 5.0 and Navision has changed the sql keys. For example in Item Ledger. They have changed the Navision key Source Type,Source No.,Item No.,Variant Code,Posting Date To sql index Source No.,Item No.,Variant Code,Posting Date,Entry No. Since Navision calls sort by Source Type,Source No.,Item…
-
Performance Question
Hi, we work with navision 40SP1 Since we updated to 40SP1 we have some performanceissues. The serverhardware is really fine and no leaks there. First i will do is to set the indexes to clustered, i hope it will boost the performance a bit. I reorganize data and index pages with a maintance plan daily. (fillfactor 30% )…
-
sql optimization
Hello I've been looking at how navision does findasyoutype on sql. And this is very costly. I was simply searching on name On SQL Navision runs the following statement. SELECT * ,DATALENGTH("Picture") FROM "Test"."dbo"."Pharm$Customer" WHERE (("Name" LIKE '%p%')) ORDER BY "No_" OPTION (FAST 10) This takes 1700 ms and the…
-
SQL tuning
Hi ... I want to start experimenting with using the SQLIndex property. But first I would like to ask if anyone knew what the results would be if for a key in Navision MaintainSQLIndex would be False but MaintainSIFTIndex would be true?
-
Webcast on SQL Optimization for Ms Dynamics NAV
Hello anybody attended the webcast? There was one question I had asked but couldn't understand what he said. The question was this. Navision has standard keys on tables. There certain fields that are not used for a given company. For example Variant Code. There are keys with this field in Item ledger. SQL has to maintain…
-
SQL 2005 Performance Issue
Experiencing random system hangs when scrolling thru list forms. Example: 1. Open Customer Card and press F5 to open Customer List. Scroll to buttom of list then start to scroll up. Sometimes it works fine, others it will take 30 seconds+ to move up 1 customer. Also similar behavior in other list forms with larger record…
-
qty on hand using sift tables from sql
Hello. A lot companies use outside systems to grab qty on hand for items. Most of the use item ledger. This store procedure uses the sift tables just like navision Just change Company_Name to your company name and run the store procedure. CREATE PROC GetQuantity @ItemNo varchar(20)='%',@Variant varchar(10)='%', @Location…
-
SAN or NAS (Network Attached Storage) for SQL
We are going to implement SQL on out HO database and we are looking to see what platform is better for Navision, SAN or NAS (of course to add to confusion this is not the NAS we all know and love but rather Network Attached Storage :D ) Anyway it appears that SAN and NAS are very similar - has anybody out there got SQL…
-
Improving Navision SQL performance in terms of disk I/O
Hi there everyone, After upgrading our client from 260 SQL to 370 SQL, and changing the server to RAID 5 the performance dropped dramatically, especially in terms of disk reads. We weren't sure what the problem was and tried in vain to fix it. After a bit of screwing around we discovered that going to File, Database,…