4.0 SP2 SQL and clustered index

ara3n

Hello client recently upgraded to 4.0 SP2 on SQL with the HOTFIX. I thought SP2 supposed to fix the clustered index issue that was in 4.0. I looked item ledger and GL entry, and the PK does not have the clustered field checked. TIA

ara3n

Never mind Looked from Enterprise manager. The PK is clustered, it's the objects that are not check marked. One other question discussion, which keys would you change in following tables to Clustered?


Item Ledger
Value Entry
GL Entry
Cust ledger
Cust Detail
Vendor Ledger
Vend Detail

kriki

In general the key that is used the most and that can depend per customer and setup MaintainSIFTIndex and SIFTLevels.
An example of the last one:"GL Entry":If you don't maintain the SIFT on "G/L Account No.,Posting Date", this is definitely the key to be clustered because the fastest access should be when users open the "G/L Account List"-form. With the SIFTLevels on both "G/L Account No." "Posting Date" (I think only on day is usefull!) this is not so needed anymore.

-Item Ledger:"G/L Account No.,Posting Date"
-Value Entry:"Item Ledger Entry No.,Entry Type" (PS:MaintainSIFTIndex=No)
-GL Entry:see above
-Cust ledger:"Customer No.,Open,Positive,Due Date,Currency Code"
-Cust Detail:"Cust. Ledger Entry No.,Posting Date"
-Vendor Ledger:"Vendor No.,Open,Positive,Due Date,Currency Code"
-Vend Detail:"Vendor Ledger Entry No.,Posting Date" (PS:MaintainSIFTIndex=No)

These are changes I have done for a customer on SQL. And for the moment they are working since some time like that and all is performant.
But it is necessary to:
-calculate SQL-statistics every night (for the moment I take 30% sample, later I can decrease this)
-every week:reorganize indexes
-every day:File=>Database=>Information=>Tables=Optimize for some tables (all tables is too much for 1 day)

DenSter

The hotfix only sets the clustered property on the SQL Server indexes, but does not update NAV. Even MSFT can't get to NAV with a SQL script

johanna

kriki wrote:

In general the key that is used the most and that can depend per customer and setup MaintainSIFTIndex and SIFTLevels.
An example of the last one:"GL Entry":If you don't maintain the SIFT on "G/L Account No.,Posting Date", this is definitely the key to be clustered because the fastest access should be when users open the "G/L Account List"-form. With the SIFTLevels on both "G/L Account No." "Posting Date" (I think only on day is usefull!) this is not so needed anymore.

-Item Ledger:"G/L Account No.,Posting Date"
-Value Entry:"Item Ledger Entry No.,Entry Type" (PS:MaintainSIFTIndex=No)
-GL Entry:see above
-Cust ledger:"Customer No.,Open,Positive,Due Date,Currency Code"
-Cust Detail:"Cust. Ledger Entry No.,Posting Date"
-Vendor Ledger:"Vendor No.,Open,Positive,Due Date,Currency Code"
-Vend Detail:"Vendor Ledger Entry No.,Posting Date" (PS:MaintainSIFTIndex=No)

These are changes I have done for a customer on SQL. And for the moment they are working since some time like that and all is performant.
But it is necessary to:
-calculate SQL-statistics every night (for the moment I take 30% sample, later I can decrease this)
-every week:reorganize indexes
-every day:File=>Database=>Information=>Tables=Optimize for some tables (all tables is too much for 1 day)

Hi, Kriki

I have read your post in http://www.mibuso.com/forum/viewtopic.php?t=7235. There, you suggest not to maintain "Customer No.,Open,Positive,Due Date,Currency Code" in Cust Ledger Entry table. And in this post that I have quoted, you suggest "Customer No.,Open,Positive,Due Date,Currency Code" to be clustered. Is it does not matter if this key is clustered while it is not maintained?

Thank you

kriki

Those were posts where I was still testing a lot of possibilities and found that in some points one thing helped and in others helped other things and I (and also others) was struggling with the different behaviour between SQL2000 and SQL2005.

First and for all: you need to maintain an index if you want to make it the clustered index.

At the moment I am more 'experienced' with SQL and my (current) first idea is to keep the original clustered index in the entry-tables. This because this key small and increasing, so it creates few pagesplits that are bad for performance. Lately I haven't encountered any more the problems we had in the beginning with SQL2005.
If I would have the same problem, I will consider testing another clustered index on customer ledger entry to see if it helps.

johanna

Thank Kriki for your explanation..

Yeah, this matter requires testing and testing again..