SIFT Maintenance for NAV and SQL Server v1.06

Administrator

SIFT Maintenance for NAV and SQL Server
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 t o 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!

http://www.mibuso.com/dlinfo.asp?FileID=812

Discuss this download here.

stryk

Hello BlackTiger!

Thanks for your notice. No, I don't have precise statistics regarding Reads/Writes, just some examples about the number of records which could be deleted: e.g. a database of 300GB and 70 Users is procuding about 15.000 "Zero SIFT" records; that's something.
Depending on the number of transactions you run, you could get rid of millions of records!

And I can assure you: There is NO reason for keeping these "Zero SIFT" records: The standard "Table Optimizer" is deleting them, too - but in an inconvenient way - and this measure is also recommended by Microsoft in the "SQL Server Option Resource Kit" documentation.

If you compare the statements executed by usp_delzerosift with the statements of the "Optimizer" (using SQL Profiler) you will see that they are more or less the same.

You also could compare for example the "Chart of Accounts" before and after SIFT Maintenance, the figures are not changed - of course!

kriki

In some cases it can degrade performance, but in most cases a lot of SIFT-records are created and then put to 0 and they remain there.
E.g. Some SIFT on T37 with Document no. in it. After some time that document no does not exist anymore in T37, but the SIFT-record remains there!

I am waiting for a tool like this for a lot of time! I thought of writing it myself, but I miss some programming experience in SQL.

Marije_Brummel

The tool already existed in the SQL Perform tools But Jorg made it free. Mixed feelings.

Deleting empty sifts is pefect for thinks like G/L entries and Value and Detail entries.

The tables where a lot of updates occur you might want to consider to drop as many sift levels anyway.

Anyways. Good that it is there. Keep up the good work, and our Perform Tools do a lot more to make worth the money. 8)

SQLGuru

Well, I have never experienced any permance degradation due to deleting empty SIFT records.

As far as the update/insert issue is concerned, there's only a little difference in performance: Adding a row in SQL Server is just as quick as updating it. A slight additional overhead may occur due to the serverside update-roundtrip in vain... well, i am sure that this impact is negligible.

My tool SQLSunrise NavTune has this integrated as a standard feature, and not only that queries against SIFT become faster: The database size decreases which speeds up backups and restores as well... and backups use less space.

stryk

Dear all!

Thank you very much for your replies. I received some very kind e-mails getting some further inspiration. Whenever this would come to "live", I'll publish it here, too - promised.

Nonetheless, nobody is perfect: I recently run the procedure on a pretty large db (450 GB) and encountered some errors regarding data-type overflow. I fixed this instantly, of course, and I hope Luc will publish the updated version soon - hang on!

I apologize for the inconvenience.

P.S.: Meanwhile you could download the recent version directly from my website: http://www.stryk.info

Kind regards,
Jörg

Administrator

SIFT Maintenance for NAV and SQL Server v1.03
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 t o 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!

--- Changes in 1.03 ---
Fixed some bugs regarding data-type overflow errors

http://www.mibuso.com/dlinfo.asp?FileID=812

Discuss this download here.

Administrator

SIFT Maintenance for NAV and SQL Server v1.04
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 t o 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!

--- Changes in 1.03 ---
Fixed some bugs regarding data-type overflow errors

--- Changes in 1.04 ---
An error could occur when tables were created which "look like" SIFT tables, but aren't. Fix: Enhanced validation of SIFT tables. (thank you Stephen!)

http://www.mibuso.com/dlinfo.asp?FileID=812

Discuss this download here.

stryk

Hi all!

Just something to ad(d) ...

As others also "mentioned" their utilities here, I would like to point out, that this procedure is part of my "NAV/SQL Performance Toolbox" which is also doing a lot more than just SIFT clean-up!

So please, compare the features and the price
http://www.stryk.info/english/toolbox.html

Best regards,
Jörg

PaddyMullaney

I have been running the following SP on production for a couple of months, it deletes the unrequirez sift values.


GO
/****** Object: StoredProcedure [dbo].[pDeleteZeroSiftValues] Script Date: 06/04/2007 16:57:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[pDeleteZeroSiftValues] (@DbName varchar(100),@CoName varchar (100)) as
Declare
@TableName varchar(100),
@TableID int,
@TableNamec varchar(100),
@TableNamec2 varchar(100),
@TableIDc int,
@ColumnNamec varchar(100),
@sqlDel varchar (500),
@SqlField varchar(50),
@sqlWhere varchar (500),
@sql varchar (1050),
@q varchar(1),
@nsql nvarchar(1050)

Set @q = char(39)
--Set @DbName = 'EddyTraining'
--Set @CoName = 'AccentUK'

Create table #SiftTableList (TableName varchar (100), TableID int)
Create table #SiftTableAndColumnList (TableName varchar (100), TableID int, [ColumnName] varchar(100))

set @sql =
'insert into #SiftTableList Select top 100 percent [name], [id] From '
+ '['
+ @DbName
+ '].[dbo].[sysobjects] Where xtype = '
+ @q
+ 'U'
+ @q
+ 'and [name] like '
+ @q
+ '%$%$%'
+ @q
+ 'and [name] like '
+ @q
+ @CoName
+ '%'
+ @q
+ 'and Right([name], 1) in ('
+ @q
+ '0'
+ @q
+ ','
+ @q
+ '1'
+ @q
+', '
+ @q
+'2'
+ @q
+', '
+ @q
+ '3'
+ @q
+', '
+ @q
+ '4'
+ @q
+', '
+ @q
+ '5'
+ @q
+', '
+ @q
+ '6'
+ @q
+ ', '
+ @q
+ '7'
+ @q
+ ', '
+ @q
+ '8'
+ @q
+ ', '
+ @q
+ '9'
+ @q
+ ') order by [name]'
--print @sql
set @nsql = convert(nvarchar(1050), @sql)
exec sp_executesql @nsql
Declare BuilFieldListCursor Cursor For
Select-- top 10
TableName
, [TableID]
from #SiftTableList
order by TableName

open BuilFieldListCursor
Fetch Next From BuilFieldListCursor
into @TableName, @TableID
@FETCH_STATUS = 0
Begin
set @sql =
'Insert into #SiftTableAndColumnList
Select '
+ @q
+ @TableName
+ @q
+', '
+ @q
+ convert(nvarchar(10), @TableID)
+ @q
+ ', [name]
from ['
+ @dbName
+ '].dbo.syscolumns c where convert(varchar(10),c.[id]) = '
+ convert(varchar(10),@TableID)
--print @sql
set @nsql = convert(nvarchar(1050), @sql)
exec sp_executesql @nsql
Declare BuildSQLStatement Cursor For
Select
TableName
, [TableID]
, [ColumnName]
from #SiftTableandColumnList
where [ColumnName] like 'S%'
order by TableName
open BuildSQLStatement
Fetch Next From BuildSQLStatement
into @TableNamec, @TableIDc, @ColumnNamec
--print @TableNamec + ' ' + cast(@TableIDc as varchar(10)) + ' ' + @ColumnNamec
Set @SqlDel = 'Delete From .[dbo].'+ @TableNamec
Set @SqlWhere = ' Where '
Set @SqlField = @ColumnNamec
Set @SqlWhere = @SqlWhere + @SqlField + ' = 0 '
set @TableNamec2 = @TableNamec
Fetch Next From BuildSQLStatement
into @TableNamec, @TableIDc, @ColumnNamec
@FETCH_STATUS = 0
Begin
Set @SqlDel = 'Delete From .[dbo].'
--Set @SqlWhere = ' Where '
Set @SqlField = @ColumnNamec
Set @SqlWhere =
case @TableNamec2 when @TableNamec
Then @SqlWhere + ' And ' + @SqlField + ' = 0 '
Else ' Where ' + @SqlField + ' = 0 '
end
set @TableNamec2 = @TableNamec
Fetch Next From BuildSQLStatement
into @TableNamec, @TableIDc, @ColumnNamec
end
Close BuildSQLStatement
Deallocate BuildSQLStatement
Set @Sql = @SqlDel + @SqlWhere
print @Sql
print getdate()
set @nsql = convert(nvarchar(1050), @sql)
exec sp_executesql @nsql
print @Sql
print getdate()
Fetch Next From BuilFieldListCursor
into @TableName, @TableID
end
Close BuilFieldListCursor
Deallocate BuilFieldListCursor

Drop table #SiftTableList
Drop Table #SiftTableAndColumnList

Administrator

SIFT Maintenance for NAV and SQL Server
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 a lso has t o 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!

--- Changes in 1.03 ---
Fixed some bugs regarding data-type overflow errors

--- Changes in 1.04 ---
An error could occur when tables were created which "look like" SIFT tables, but aren't. Fix: Enhanced validation of SIFT tables. (thank you Stephen!)

--- Changes in 1.06 ---
Several minor changes and fixes.

http://www.mibuso.com/dlinfo.asp?FileID=812

Discuss this download here.

stryk

Hi!

I received some questions about how to handle this procedure, so I would like to publish my reply to this here as well:

Well, SIFT in NAV is a somewhat annoying issue. First of all the structure of the SIFT tables needs to be optimized, please read my BLOG about this for further details: http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx

The SIFT tables contain aggregated values – sums – of the underlying tables (e.g. G/L Entry, Item Ledger Entry, etc.). Many of these “sums” sooner or later will just include the value 0 (zero).
For NAV it doesn’t make a difference if it could find a “Zero SIFT Record” or no SIFT record for a query – in both cases the value 0 is processed.
Hence, these “Zero SIFT Records” are records which aren’t required, they are just wasting precious disk-space and system resources, slowing down read- and write-performance. Thus, the SIFT tables need to be cleaned up periodically by deleting these records.
With standard NAV this could only be done by using the “Table Optimizer”, but it is highly recommended NOT to use this feature for various reasons …

The procedure “ssi_delzerosift” is dynamically generating a TSQL statement to delete all these records from the SIFT tables where ALL sum-fields are zero.

To check what it would do, you could run it in a “script”-mode:

exec ssi_delzerosift @script = 1

Here you could see which TSQL would be executed. To execute the statement instantly run

exec ssi_delzerosift

For the periodic clean up you could create a SQL Server Agent Job which actually performs the clean up:

exec ssi_delzerosift @nomsg = 1

The @nomsg parameter suppresses the detailed output, you just get a summary about how many records/bytes were deleted (this summary could be included in the Job History).

Please refer to the readme file for the license-terms for using it.

Finally, please have in mind that with NAV 5.0 Service Pack 1 there are NO MORE SIFT tables – these are replaced by “Indexed Views” so SIFT maintenance MUST NOT be performed!

Kind regards,
Jörg

WL

Hi Jorg,

Before running your script, I have captured the database information.
After running your script, I compare the database information against the one captured earlier. I do see a decrease in Record Size and Size (KB).
Next, I went to Navision Application and run Optimize for one of the table that has reduced in Size. I realised that it further decrease the Record Size and Size (KB).

Please advice why.

stryk

Hi,

well, the SIFT Maintenance Script is just deleting those records from the SIFT tables, where ALL sums equal zero. Of course, this reduces the number of records, this the total table size. The "Record Size" in NAV is just an average calculation, so it is just "statistical coincience" that this figure is changing, too.

The NAV Table Optimizer performs two things:
It deletes the zero-SIFT records (same as the procedure), PLUS it re-creates the Indexes! This Index-Defragmentation is the operation which might cause further reduction of the size, as Pages might get cleared, thus free space released.

The idea of the SIFT Maintenance procedure is, to segregate both operations: SIFT maintenance is usually a rather quick process, while the Index-Optimization could take a long time.
Index Maintenace could be performed separately via SQL Server Maintenance Plans or other tools (http://www.stryk.info/english/toolbox.html).

Kind regards,
Jörg