Base on David's "hint, hint ) I made my promise come true and copied this from a current thread:
SIFT tables include aggregated information of e.g. "Ledger Entry" tables to display these values in FlowFields. The aggregation is done via SQL Server site triggers which are programmed really poor and performing bad (I skip some details here).
Hence, it is necessary to minimize the number of SIFT indexes and bucktes to get the optimal balance between reading and writing performance. As a rule of thumb one could say, that just the one-before-the-last bucket should remain, all previous could be disabled (means we get one level of aggregation); this is usually sufficient, increasing read/write performance remarkable. Of course, the more precise one could tune the SIFT buckets - which requires deep knowledge about how data is queries - the better.
To further optimize the large SIFT tables it is feasible to create a covering index on them (= an Index including all table fields (bucket, f?, s?)). This indexes are relatively big, but really boost performance. And is "cheaper" to maintain this index instead of another SIFT bucket.
If all SIFT Indexes are disabled on the Ledger Entry table, write performance is maximal (no additional costs) but read-performance is worst as the full qualified info has to be queried from the table/records. A covering index on normal NAV tables is not possible, as NAV always queries SELECT * (all) and an index could only include 16 fields.
Thus, an index could be created which includes all fields from the SELECT SUM query - value fields and data fields. But indexing decimal fields could enlarge the Index. Hence it could be an option to create an index on the data fields and INCLUDE the value fields for summing up.
But one have to have in mind that any kinds of indexes on Ledger Entry tables (e.g. 10 Mio records) will always be larger than indexes on SIFT tabels (e.g. aggregation 1:1000 = 10k records).
Finally, it remains tricky business. It depends on the size of the source table and the SIFT tables, thus on the index size etc.. The problem is to find out "when to prescribe which medicine", it's not possible to state a definite "Yes" or "No". It's not about replacing SIFT by INCLUDES, it actually mixing various options.
Thus, using INCLUDED columns could be a very good option to improve the system!
P.S.: In NAV 5.0 SP1 (1Q 2008) SIFT will be replaced by "Indexed Views", then things will be somewhat different!
HOW TO: Example T32 "Item Ledger Entry"
Key: Item No. (f2),Entry Type (f4),Variant Code (f5402),Drop Shipment (f47),Location Code (f8),Posting Date (f3)
Sum Index Fields: Quantity (s12),Invoiced Quantity(s14)
This results in 10 SIFT buckets (0 to 9) to maintain; stored in SIFT table ...$32$0 (bucket, f2, f4, f5402, f47, f8, f3, s12, s14)
The first step of optimization should be to disable all SIFT buckets from 0 to 7 and 9; only leaving bucket 8 as first level of aggregation.
Then a "Covering Index" on the SIFT table could be created (SQL 2000 and 2005):
CREATE INDEX ssi_CovIdx ON [...$32$0] (bucket, f2, f4, f5402, f47, f8, f3, s12, s14)
Here the s-fields are indexed, thus the index could be pretty large. But anyway, Covering Indexes are "paradise on earth" for SQL Server and will be used as much as possible. Especially when summing up the s-fields (e.g. for higher aggregation) this index will speed up things remarkably!
On smaller tables it could be feasible to disable SIFT at all (MaintainSIFTIndex = No) and - maybe - create an "Include" index as well:
CREATE INDEX ssi_IncIdx ON [...$Item Ledger Entry] ("Item No_", "Entry Type", "Variant Code", "Drop Shipment", "Location Code", "Posting Date") INCLUDE ("Quantity", "Invoiced Quantity")
Note: this should ony be an example. In reality it may not be feasible to have just this index on T32, here some SIFT aggregaton may be required. But this could be usefull on "Sales Line" or "Purchase Line" etc..
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics