SIFT Maintenance for MS Dynamics NAV & SQL Server
15. März 2007 12:51
When performing transactions in NAV so called SIFT (Sum Index Flowfield Technology ™) tables (SQL Server option only) are used to save aggregated values about Sum Index Fields to be used in FlowFields which are providing these values.
Usually many of these SIFT records could contain only “sums” of value zero. These records are not required to provide the FlowField feature in NAV and should be erased periodically to keep the number of records within the SIFT tables as small as possible, to increase the NAV system’s performance.
With NAV this kind of maintenance could only be done by using the “Table Optimizer”. Unfortunately, this feature could not be scheduled to run automatically, and as it additionally re-creates all indexes, the processing could be rather time consuming – and while running, the performance is remarkably decreased; the system is virtually offline, users will be blocked.
It also has to be mentioned that the “Table Optimizer” also may reset all kind of index-optimizations which were implemented on SQL Server site!
The stored procedure usp_delzerosift deletes those SIFT records, where all sum values are equal to zero.
As this procedure could be used within a SQL Server Agent job, it could be used for periodic and automatic SIFT maintenance.
usp_delzerosift determines the current structure of SIFT tables dynamically “on the fly” – no programming or scripting required.
Hence, usp_delzerosift is a simple but convenient utility to keep SIFT tables small and performing better!
Download
Usually many of these SIFT records could contain only “sums” of value zero. These records are not required to provide the FlowField feature in NAV and should be erased periodically to keep the number of records within the SIFT tables as small as possible, to increase the NAV system’s performance.
With NAV this kind of maintenance could only be done by using the “Table Optimizer”. Unfortunately, this feature could not be scheduled to run automatically, and as it additionally re-creates all indexes, the processing could be rather time consuming – and while running, the performance is remarkably decreased; the system is virtually offline, users will be blocked.
It also has to be mentioned that the “Table Optimizer” also may reset all kind of index-optimizations which were implemented on SQL Server site!
The stored procedure usp_delzerosift deletes those SIFT records, where all sum values are equal to zero.
As this procedure could be used within a SQL Server Agent job, it could be used for periodic and automatic SIFT maintenance.
usp_delzerosift determines the current structure of SIFT tables dynamically “on the fly” – no programming or scripting required.
Hence, usp_delzerosift is a simple but convenient utility to keep SIFT tables small and performing better!
Download
Zuletzt geändert von Timo Lässer am 14. Juli 2009 14:32, insgesamt 2-mal geändert.
Grund: Defekten Link korrigiert
Grund: Defekten Link korrigiert