SQL 2005 Performance Issue

Mauddib

KB943858 I beleive.

Martin_Brülisauer

bbrown wrote:

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.

Any thoughts? :-k

The cause of the problem is easy to explain. The solution is difficult
in NAV.

Explanation:
NAV does SELECTS in both orders, ascending and descending.
Form 21, Customer Card -> Ctrl-F5 -> Form 25, Customer Ledger Entry

Example if you scroll down:

---

SELECT * FROM "CRONUS (Schweiz) AG$Cust_ Ledger Entry" WHERE
(("Customer No_"='10000')) AND "Customer No_"='10000' AND
"Posting Date"={ts '2007-12-31 00:00:00.000'} AND
"Currency Code"='' AND "Entry No_">=2480
ORDER BY "Customer No_","Posting Date"

Example if you scroll up in the same form:

---

SELECT * FROM "CRONUS (Schweiz) AG$Cust_ Ledger Entry" WHERE
(("Customer No_"='10000')) AND "Customer No_"='10000' AND
"Posting Date"={ts '2008-01-21 00:00:00.000'} AND
"Currency Code"='' AND "Entry No_"<2700
ORDER BY "Customer No_" DESC,"Posting Date" DESC

If your Customer Ledger Entry table contains some millions of records
your sqlserver will be overloaded with disk IO because NAV only creates
ascending indices and you do not have any possibility to create a
descending index.

Solution:
Find you key in management studio and create the corresponding
descending key for each key the user wants to use and each company
in your database. The above example would be solved creating the
sql index

CREATE UNIQUE NONCLUSTERED INDEX [Reverse$1] 
ON [dbo].[CRONUS (Schweiz) AG$Cust_ Ledger Entry] 
(
	[Customer No_] DESC,
	[Posting Date] DESC,
	[Entry No_] DESC
)
WITH (
  PAD_INDEX  = OFF, 
  STATISTICS_NORECOMPUTE  = OFF, 
  SORT_IN_TEMPDB = ON, 
  IGNORE_DUP_KEY = OFF, 
  DROP_EXISTING = OFF, 
  ONLINE = OFF, 
  ALLOW_ROW_LOCKS  = ON, 
  ALLOW_PAGE_LOCKS  = ON)

To generate DESC queries without creating the corresponding sql index
is a bad kernel design within NAV. The design team would find these
errors if they tested using real load databases and no only fancy
CRONUS stuff.

bbrown

:shock:

Sorcerer

for anyone who wants to update to sql sp2 cumulative update 4 and uses a (x64) cluster:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;943525

otherwise the sql agent doesn't work any more...

Martin_Brülisauer

This issue has been solved by the Microsoft Dynamics NAV engineering.
Get the "Platform Roll-up Update for Microsoft Dynamics NAV 5.0
(Build Number 25359) (KB943858).

After the update you won't see any DESC sql queries. The usual
application issues still exist (e.g. FIND(+/-) without SETCURRENTKEY()).

Marije_Brummel

Build 25359 contains a blocking bug.

Use 25653 instead.

DeputyDogh

bbrown wrote:

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 sets.

Any thoughts? :-k

Are you experiencing the same with the "Item Card" and "Item List" ?
I've experienced the same problem there and have fixed this issue by simply deactivating the BLOB field. (I didn't need it, and if i did, i would place it in another table)