Disabling SIFTLevelsToMaintain

suvidhasuvidha Member Posts: 117
edited 2009-07-03 in SQL Performance
Hi,
Our Client is running on 3.7.
We face performance issues quite often now.
I went tru most of the posts here and learnt that
If I have keys defined as below:

1.Key: G/L Account No.,Posting Date.
SIF : Amount,Debit Amount,Credit Amount,Additional-Currency Amount,Add.-Currency Debit Amount,Add.-Currency Credit Amount.
SIFTLevelsToMaintain : {G/L Account No.},
{G/L Account No.,Posting Date:Year}…………..

2.Key: G/L Account No.,Business Unit Code,Global Dimension 1 Code,Global Dimension 2 Code,Close Income Statement Dim. ID,Posting Date
SIF: Amount,Debit Amount,Credit Amount,Additional-Currency Amount,Add.-Currency Debit Amount,Add.-Currency Credit Amount.
SIFTLevelsToMaintain : {G/L Account No.},
{G/L Account No.,Business Unit Code},
{G/L Account No.,Business Unit Code,Global Dimension 1 Code}……

In this case I can disable {G/L Account No.} in SIFTlevelsToMaintain of Key2.
If this is right…. Y is this not taken care in base versions???? I see many buckets being repeated like this in base 3.7.????
Is it taken care in higher versions???
First of all is my understanding regarding this rite???? :!:

Comments

  • strykstryk Member Posts: 645
    Hi!

    First of all: No, the SIFT Buckets are not optimized in any version of NAV; but they are replaced by VSIFT since version 5.0 SP1.

    There's a lot one could say about SIFT tuning etc., but I recommend this proceeding:

    Referring to your example in T17 "G/L Entry":
    Key: G/L Account No., Posting Date
    SIFT Buckets available: 0, 1, 2, 3, 4, 5
    SIFT Buckets enabled (default): 1, 2, 3, 4

    Bucket 0 (GRANT TOTAL) is disabled and should NEVER be enabled.
    Bucket 5 is also disabled. 5 "aggregates" on Primary Key level (the "Entry No." is added!) hence there is NO aggregation. Enabling 5 would mean to create one SIFT record for each record in T17. Thus, also Bucket 5 must NEVER be enabled.

    The first level of aggregation which is really needed is Bucket 4. All higher levels (1, 2, 3) can be calculated from Bucket 4.
    Means you could disable Buckets 1, 2, 3 only leaving Bucket 4 enabled.

    As a general "rule of thumb" one could say:

    "Disable all SIFT Buckets, except the one before the last one (the PK Bucket)"

    Only if you encounter problems with this single Bucket - if you got proof with SQL Profiler - then you should enable the next higher Bucket (but have in mind that there are other tricks to fix this).

    The consequenses of this are:
    - Shorter "SIFT Trigger" code, faster SIFT processing
    - Reduction of number of records in SIFT tables = smaller indexes
    - Faster Read and Write performance
    - Reduced blocking conflicts

    This optimization should be implemented in all large or heavily used tables. For tables which just contain few records; e.g. less than 10000 you may consider to disable SIFT at all (MaintainSIFTIndex = FALSE).

    P.S.: If you have a "Date" field in the key you should only enable the "Day"-aggregation. If there is a "DateTime" field you should also start aggregatin on "Day"-level, NOT "Milliseconds" etc.!

    P.S.P.S.: The remaining aggregation level - after optimization - is actually the same level of aggregation used in VSIFT.
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • suvidhasuvidha Member Posts: 117
    That was very clear....
    Thanks a lottt..... :D
  • suvidhasuvidha Member Posts: 117
    Hi stryk,
    i have observed in the client Db, In Item ledger entry table

    Key: Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date
    SumIndexFields: Quantity,Invoiced Quantity

    The maintained Bucket No. and SIFT Levels are:

    4. Entry Type,Item No.,Variant Code,Drop Shipment
    5. Entry Type,Item No.,Variant Code,Drop Shipment,Location Code
    9. Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date:Day,Entry No


    Bucket no. 9 is "aggregates" on Primary Key level

    but as u said, this last SIFT level should never be enabled...
    Now how can i find y this was enabled during some customization
    How to identify if this is useful or not... :?:
  • strykstryk Member Posts: 645
    suvidha wrote:
    Hi stryk,
    i have observed in the client Db, In Item ledger entry table

    Key: Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date
    SumIndexFields: Quantity,Invoiced Quantity

    The maintained Bucket No. and SIFT Levels are:

    4. Entry Type,Item No.,Variant Code,Drop Shipment
    5. Entry Type,Item No.,Variant Code,Drop Shipment,Location Code
    9. Entry Type,Item No.,Variant Code,Drop Shipment,Location Code,Posting Date:Day,Entry No


    Bucket no. 9 is "aggregates" on Primary Key level

    but as u said, this last SIFT level should never be enabled...
    Now how can i find y this was enabled during some customization
    How to identify if this is useful or not... :?:
    Well, the only chance to find out who has changed this and when is to compare the object wit a previous version - so probably you'll never find out whos has done this and why (anything in the "Documentation-Trigger"?) ... :-k

    But this is definitely not useful:

    If you have this Bucket enabled, a SIFT-record will be created per "Item Ledger Entry" record.
    You could check with this TSQL:
    select count(*) from dbo."CRONUS 403$Item Ledger Entry"
    select count(*) from dbo."CRONUS 403$32$0" where "bucket" = 9
    
    Both figures should be the same.
    So what would happen is this:
    If anyone calculates the "Quantity" or "Invoiced Quantity", having set a filter on the "Entry No." then NAV would recognize the existing SIFT Bucket (9) and probably fire this query (or similar):
    select sum(s12), sum(s14) from dbo."CRONUS 403$32$0"
    where "bucket" = 9 and "f2" = ... and "f1" = 12345
    
    Where "f1" is the "Entry No.". If the Bucket (9) would not exist, then NAV would directly query the "Item Ledger Entry" table:
    select sum("Quantity"), sum("Invoiced Quantity") from dbo."CRONUS 403$Item Ledger Entry"
    where "Item No_" = ... and "Entry No_" = 12345
    
    The results should be identical.
    Hence, the "PK Bucket" is just creating a hell of records, expanding the SIFT-table tremendously, causing slow performance.
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • suvidhasuvidha Member Posts: 117
    I found 2 keys with Pk bucket in Item Ledger entry!!!!
    Thanks a lot... i am going to disable these hoping the performance will be better:)
  • DenSterDenSter Member Posts: 8,304
    Don't expect miracles. Your system is not going to all of a sudden be lightning fast just for disabling that SIFT level.
  • garakgarak Member Posts: 3,263
    SIFTS are one point to start.
    Others are the indizes, table structure, the C/AL source self, the Hardware (RAM, HDD, RAID, SAN,CPU), the SQL Server config self, the design of forms and Reports (for example many FLOW fields on forms, filters which are not good for the "order by" (setcurrentkey) statement, Blocks or "stupid" users or or or ...
    So, fist check why the system is slow (you can check this, f.e. with Perfmon, SQL Profiler, or the extended Client Monitor and Pivot Tables in Excel).

    Here in the forum are many posts about this. One is, i hope it was this post, this one viewtopic.php?f=34&t=13154
    But there are also many others. read this posts before (i know could be a lot of work)...

    viewtopic.php?f=34&t=24392
    viewtopic.php?f=34&t=25676

    Regards
    Do you make it right, it works too!
  • bored04bored04 Member Posts: 1
    this is an interesting discussion.. thanks for sharing

    simulation rachat de credit
  • garakgarak Member Posts: 3,263
    please and welcome.
    Also read the other post in the "SQL Perfomance" forum
    Do you make it right, it works too!
Sign In or Register to comment.