SQL 2005 Performance Issue

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.

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