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?
//Lars
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 [:$]
Regards,
Jörg
Hi Jorg,
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.
/Lars
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.
This is a great post Jorg, maybe a good topic to start blogging on (hint hint) … [;)]
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 …
I haven’t seen this “webinar” but one should be aware that this for sure just has show the extremities; I guess there was no “world” inbetween like reducing SIFT buckets, create covering indexes etc…
The problem is to find the right balance between write and read performance - have in mind that with NAV we have way more reading transactions than writing transactions!
But anyway, this is a good example to proove what IS POSSIBLE with NAV & SQL!
Well, maybe I misunderstood this, but when I talked with Claus & Jesper about the SP1 improvements, they referred to the “Indexed Views”!
To anser Cristis questions:
Yep, with “Indexed Views” all the SIFT trigger stuff will vanish, no more physical insertions into SIFT tables etc… Unfortunately I got no chance to glimpse on the technical details, I’m curious how exactly it will be handled in future. If MS does it right, this will definitely a big improvement!
This is very dangerous. People get their hands on such webinars, and see these magic results, and suddenly believe that they can with the wave of a wand get super perfromance.
During Hynek’s SQL Performance presentation at Convergence, there was one person that brought up this specific example, and was under the clear understanding that their NAV system would easily run twice as fast by using this suggestion. That is complete and utter nonsense, but it came from Microsoft, so it must be true.
Yes of course the insert will be faster. Just turn off all indexes and all Sift maintenance and an insert will be five times faster, but what about reads, what about calculating sums, what about net work, what about client CPU and Ram it goes on and on.
The key work in tuning any NAV system is BALANCE - you need to balance all your resources against all your objectives, not optimizing ONE tasks against one resource.
I need to step in here, because I don’t think that is quite fair to Michael. I saw his chalk and talk about this topic at Convergence in San Diego, and the first thing out of his mouth was a warning that you are not supposed to go in and replace SIFT this way. He very specifically said that his example was just that, an example, and that every case should be looked at carefully.
We can all discuss whether something is a good idea or not, but I consider Michael to be very knowledgeable about these things, and I never heard him spread any BS. The tricky thing about this type of webinar is that many times the viewer hears what they want to hear. The example was mentioned to be a specific example (this is what we did in this one case, don’t go and replace all SIFT), and somehow that got turned into ‘here’s how you should replace SIFT and make your system run twice as fast’.
I don’t know how that happened, but I can guarantee that Michael is not the source.
Based on development schedule there is only on Service pack being released next year in 1st quarter. They did not mention anything about sp2 or sp3. I don’t if they would released 2 more service pack during 6-8 month period till 6 comes out.
I would like to know what the performance difference is between the sift and indexed views. Specically the read perfromance.
.
Well, I guess we are all quite curious! But just “assuming”:
Comparing standard SIFT handling now (SIFT tables) with SIFT handling in future (Indexed Views) I think there will be a big improvement. Currently, primarily the write performance (and related blocking) is the issue; this will be remarkably reduced as no more additional records will be created.
But as the SIFT Indexed Views will SELECT data from the source table - Ledger Entry tables etc. - it is crucial to have optimized indexes there. THen, when querying data from the Views, their indexes have to be optimized as well. If all is indexed properly, it will generally improve performance, at least up to a certain amount of records in the source table. But as mentioned: comparing standard setup before/after.
Between an optimized SIFT structure now and in future, there will not be such a big difference. And - I’m afraid - on pretty large tables the Indexed Views may even perform worse than with real aggregated values in dedicated SIFT tables …
We will see …
First, I have yet to be convinced that say in Customer Ledger Entry table with 20 million entries and say 5 to 10 major customers a Balance calculation will be faster with any kind of SQL query on the table than reading 1 entry in a SIFT table. Let’s not forget that Indexes on a table are helping structures that offer a read speed increase at the cost of a write overhead. The fact that SIFTs are badly implemented now does not mean they are not a good idea.
Second I do realize this is a storm in a teacup since no offense Jorg but haven’t heard this indexed views thing from anyone else. Can anyone else at Convergence confirm they heard this as well? I will make sure to ask at Directions anyway …
P.S. Not that it matters to MS what we talk about it but what about offering both functionalities and letting us decide which one we need in a particular case.
Yes thats the problem when the information is not correctly passed to the readers. There was a question at Convergence by someone that clearly believed that they were told they could double the performance of NAV by making this simple change. And was asking why it was not being done.
Much the same can be said for the Hardware sizing guide, which has some good facts in it, but enough mistakes to cause disasters for those that read it without really knowing what is going on.
OK, here is your second source [;)] I also heard this. (Didn’t I already mention that?)
They indicated that this is a REPLACEMENT to Sift, not an alternative. Mark, Eric, Erik, anyone else want to comment?