As a result of using indexed views, SIFT keys are exposed to SQL Server tracing and tuning tools. For example, the SQL Server profiler can display information about which indexed views are maintained for a specific table. This makes it easy for you to assess the cost of maintaining SIFT keys and allows you to make informed decisions about any adjustments that might be required.
SIFT Keys
When data is inserted, updated, or deleted in a table, the SIFT keys that have been defined and enabled for that table are maintained. Maintaining these SIFT indexes has performance overhead. The size of the performance overhead depends on the number of keys and SumIndexFields that have been defined for each table. You should therefore give careful consideration to the number of SIFT keys that you define and only maintain the SIFT keys that are important for your application.
There is no need to maintain a SIFT key for a total that is only used periodically and can be easily generated by a report. You can still program Microsoft Dynamics NAV 2013 R2 to calculate the SIFT based totals even if the SIFT key is disabled. The calculation is performed directly on the base table.
You should consider combining indexes wherever possible.
Example
Maintaining two SIFT keys:
-
Key: "WareHouseId, ItemId, Color" SumField: "OnStock"
-
Key: "WareHouseId, ItemId, Size" SumField: "OnStock"
If there are only a few combinations of Size and Color (for example, less than 200), then one combined index/SIFT key should be sufficient.
-
The Combined Key:
-
"WareHouseId, ItemId, Color, Size" SumFIeld: "OnStock"
When you set the MaintainSIFTIndex property of a key to Yes, this will be the SIFT key and create the indexed view to support it. However, disabling the SIFT key by setting MaintainSIFTIndex to No can improve performance in certain circumstances. Setting this property to No means that the SIFT functionality must be implemented by calculating the totals online instead of using the precalculated sums that are maintained by SIFT.