LOCKING at INSERT with VSIFT

stefan.jossen

Hello out there.

I like the RECORDLEVELLOCKING of the NAV-SQL.
I also like the new SQL-views VSIFTS inside the SQL with NAV-SQL 5.1 and up.

But:

Can you explain to me WHY the INSERT of a single RECORD LOCKS almost the whole table if there is a SIFT on a KEY?

I thought that next to the actual RECORD and the update of the different INDEXES there would be no additional data written into the database because of the VSIFT beeing a VIEW.

Stefan

Marije_Brummel

I noticed exactly the same.

We have a benchmark database with this issue which we are ready to downgrade to 4.0 SP3 with updates to see what happens...

davmac1

The SIFT is a managed view which means it has its own indexes.
Is this a small or a large table?
I suspect it has to lock the index rows while it is inserting.

It would be interesting to get a more in depth terchnical overview of what is happening.

stryk

@Stefan: Could you explain which table - for example - is affected by this? What do you mean with "almost the whole table" - what is actually locked?

wakestar

Hi all

Does anybody have more information about this issue? Is there a workaround?

wakestar

Belias

From manual 5 of manual i posted here:
http://www.mibuso.com/forum/viewtopic.php?f=34&t=36804

MaintainSIFTIndex
[...]Also be aware that even the new implementation that uses indexed views will
cause blocking in the database. For example if two users update the total for an
account on the same date.

That's all I know

stefan.jossen

Belias wrote:

From manual 5 of manual i posted here:
http://www.mibuso.com/forum/viewtopic.php?f=34&t=36804

MaintainSIFTIndex
[...]Also be aware that even the new implementation that uses indexed views will
cause blocking in the database. For example if two users update the total for an
account on the same date.

That's all I know

Thanks for the info.
I was not aware of that:
The VSIFTs are saved within SQL as indexed tables.
This means that SIFT's on keys will lock the whole range of the filter...

stryk

stefan.jossen wrote:

Belias wrote:

From manual 5 of manual i posted here:
http://www.mibuso.com/forum/viewtopic.php?f=34&t=36804

MaintainSIFTIndex
[...]Also be aware that even the new implementation that uses indexed views will
cause blocking in the database. For example if two users update the total for an
account on the same date.

That's all I know

Thanks for the info.
I was not aware of that:
The VSIFTs are saved within SQL as indexed tables.
This means that SIFT's on keys will lock the whole range of the filter...

This is not correct. VSIFT are indexed "Views",not "Tables" - that's a remarkable difference. Those VSIFT use have a Clustered Index which Leaf Nodes actually contain the aggregated sum values.
Thus, when a record is inserted into a table - e.g. Item Ledger Entry - the CI of the related VSIFT Views are updated. During this update certain rages of these indexes are locked, thus blocking conflicts could occur.
Any "filter ranges" have no impact on this VSIFT update process. When other processes are querying sums from the VSIFT during the the VSIFT update then blocks might occur.
The Ledger table itself is not fully locked - only the inserted record ROW X and the container-page PAG IX.

stefan.jossen

stryk wrote:

This is not correct. VSIFT are indexed "Views",not "Tables" - that's a remarkable difference. Those VSIFT use have a Clustered Index which Leaf Nodes actually contain the aggregated sum values.
Thus, when a record is inserted into a table - e.g. Item Ledger Entry - the CI of the related VSIFT Views are updated. During this update certain rages of these indexes are locked, thus blocking conflicts could occur.
Any "filter ranges" have no impact on this VSIFT update process. When other processes are querying sums from the VSIFT during the the VSIFT update then blocks might occur.
The Ledger table itself is not fully locked - only the inserted record ROW X and the container-page PAG IX.

Thank you for the explanation!
Within Navision I have never tried removing the "clustered index"-property from the primary key.
If we have to keep the SIFTS - would it be a possibility to turn off this property?

Marije_Brummel

Hmm, I think the explanation was correct but the penny has not really dropped yet.

No offense, the VSift is immensly complex to understand.

The clustered index property in the navision table keys have nothing to do with this and cannot be turned of. ( In the last versions).

Please do not change anything on SQL Level. This is extremely dangerous and can lead to corrupt databases.

stefan.jossen

Mark Brummel wrote:

Hmm, I think the explanation was correct but the penny has not really dropped yet.

No offense, the VSift is immensly complex to understand.

The clustered index property in the navision table keys have nothing to do with this and cannot be turned of. ( In the last versions).

Please do not change anything on SQL Level. This is extremely dangerous and can lead to corrupt databases.

No offense taken. I'm eager to learn more.
I keep on hammering on the SIFT-subject because I had to redesign some (standard) reports that fell asleep running under SQL.
The sifts are to me a main reason for bad performance - next to bad code, keys and filters within loops...
Till now I tried to resolve the issues within Navision but I'm really not sure about sifts and SQL.

stryk

Don't worry - even as SIFT/VSIFT is somewhat complex we're not talking about "Recket Scienece" here 8)

Since you are running VSIFT there should be less harm than with the old stndard (non-optimized) SIFT. If a certain VSIFT record indeed is a problem you could find out with proper block/deadlock detection (http://dynamicsuser.net/blogs/stryk/archive/2008/11/03/blocks-amp-deadlocks-in-nav-with-sql-server.aspx) and query profiling (http://dynamicsuser.net/blogs/stryk/archive/2009/11/26/technical-airlift-2009-munich-nav-sql-performance-optimization-indexes.aspx).

Once you have identified a problematic VSIFT you could deal with it: first start an "effectivity analysisis", means check how many records are stored in the base table, how many records contains the VSIFT and how many zero-sum-records contains the VSIFT. If the VSIFT is ineffectivy anyway you could set the "MAintainSIFTIndex" proprety of the related "Key" to FALSE. If the VSIFT is necessary the you could improve its performance by adding optimized indexes (SQL site only) ...

But then again: first thing must be to IDENTIFY the problematic queries and VSIFT ... knowing, not guessing ...

P.S.: You know about this one? http://dynamicsuser.net/blogs/stryk/archive/2009/12/09/the-nav-sql-performance-field-guide.aspx

stefan.jossen

Thank you very much for all this!
=D>
I'll try to pull back from daily business and get to learn the tools properly.