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
Jörg A. StrykSTRYK System ImprovementPerformance Optimization & Troubleshootinghttp://www.stryk.info
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.
Jörg Stryk: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:...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 ...
In Michaels examples inserts where going from 1400ms with SIFT to 500ms with included columns
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!
lars westman: 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
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.