Interested in creating and selling training? You pick the price, 50/50 revenue share, and you own the content. Find out more at
In SQL2005 Microsoft introduced Included Columns. These can be used as a replacement for SIFT-tables in NAV. Instead of declaring SIFT-fields on keys inside NAV you set these up as included columns on indexes in SQL
This i supposed to increase performance. But has anyone tried it and what's the result?
Yep, I got somewhat of experience ... and as so many things with "performance optimization": it depends ...
This may be a long shot ... but anyway:
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.
The issue herewith is the s? fields, which may be responsible for the large index size - indexing decimal fields could be dangerous. So with SQL Server 2005 it could be an option to not put the s-fields in the index, but to define them as included column - in this cause the information is only added to the leaf-nodes of the index. This index is smaller than a full covering index, but only performing slightly slower.
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 as mentioned: Indexing decimal fields will enlarge the Index tremendously. 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, the short answer is: Yes, 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 "Index Views"!
P.S.: In NAV 5.0 SP1 (1Q 2008) SIFT will be replaced by "Indexed Views"! // sorry
You burried the most important thing at the end:
"P.S.: In NAV 5.0 SP1 (1Q 2008) SIFT will be replaced by "Index Views"!"
What do you mean by that? They are dropping current implementation of SIFTs? And what is "Index Views"?
I've run some test on it and the first thing is that you can't add include fields to the Clustered key.
For my test added "Quantity" , "Invoiced Quantity" , "Remaining Quantity" to the secondary keys
I ran the following code.
SELECT SUM("Quantity"),SUM("Invoiced Quantity"),SUM("Remaining Quantity") FROM "NavisionSP3"."dbo"."NavisionSP3$Item Ledger Entry" WITH (READUNCOMMITTED) WHERE (("Item No_"='PC3'))
Which is what Nav would run if you have sift disabled.
I looked at the Execution plan and SQL did a index seek only as apposed to index seek then cluster key lookup
The item leger had 700K records. And the different between including columns and not including columns in the index time wise is that it cuts in half.
It's an improvement, but not as much when you use SIFT.
Until they add features to the include column, such as keep summation of the include columns, SIFT will have to remain for a while, unless they come up with some other solution.
So trying to provide SQL native support for SIFTs is completely out of the question?
I don't know. They haven't done it so far.
But the big thing about included columns is supposed to be insert and modify. I saw a webinar about this held by Michael DeVoe and it was quite impressive figures when comparing updating of data. So it would be interesting to here from anyone who has done this in real life.
In Michaels examples inserts where going from 1400ms with SIFT to 500ms with included columns (the same difference for delete's). That's a big difference. And the only thing that needs to be done is to disable the maintainance of SIFT-indexes inside NAV and then create the included columns on the index in SQL.
Yes you do get insert and update improvements when you disable SIFTs.
I would like also hear more about "In NAV 5.0 SP1 (1Q 2008) SIFT will be replaced by "Index Views"!" . Is this true?
I think we are losing sight of one aspect: included columns are for an index. Now that is great perhaps for fine tuning a query you already know it uses that index and you just want to make it faster. But from a generic perspective that index may or may not be used (let's not forget the preference of 2005 for clustered indexes or the multitude of ways Navision users look at data).
SIFT Tables are a lot more forgiving when being selected than indexes so you can make a generic one that will be usefull for more than the queries that may or may not use the index we chose to add included columns.
And Jorg you owe us an explanation about those index views.
Jörg Stryk said:Yep, I got somewhat of experience ... and as so many things with "performance optimization": it depends ...
This may be a long shot ... but anyway:
This is a great post Jorg, maybe a good topic to start blogging on (hint hint) ...
Jörg Stryk said:...P.S.: In NAV 5.0 SP1 (1Q 2008) SIFT will be replaced by "Index Views"!...
I am not sure if this is exactly correct, but I may have missunderstood. My understanding was that Indexed Views would be introduced in "A service pack for 5.0" but not necessarily SP1. So it could be SP2 or 3 etc. It was clear of course that Flow Fields are definitely gone for 6.0.
Also my understanding is that SQL 2008 handles indexed Views very well, so I think that they need to wait till SQL 2008 is more main stream before killing Flow Fields.
Of course not. INCLUDED columns are added to the leaf-nodes of an index. The leaf-nodes of the Clustered Index is the table itself, hence there are already ALL fields included, so it's not possible to INCLUDE any fields again ...