SIFT bucket

colin_leversuch-roberts

please excuse the question but I'm trying to get a handle on Navision. I have a SIFT table with 56.5 million rows and three bucket values - the tables's clustered index is almost the entire table. So I read some comments about removing SIFT tables and placing columns as includes within an index to gain performance.
As the SIFT table is maintained with a trigger I figure this may be an area to examine as I have poor performance ( sql 2005 sp2 )
The resource kit doesn't tell me how to interpret data in the SIFT table so I don't know if this table is good or bad .. any comments appreciated
I also have a few hundred empty SIFT tables - could I delete these ?

kriki

Navision does not yet support adding columns as includes within an index on SQL2005, so better don't use it.
The SIFT-bucket tables should NOT be touched! Only Navision can change something in it.
To gain some performance, you can go into the Navision table and decide which siftlevels to maintain in Navision. In general it is not needed to maintain all siftlevels. In general it is better that a specialist does this work.

For deleting some empty records, there are a few tools you can use to do that:
-http://www.mibuso.com/forum/viewtopic.php?t=17035
-http://www.mibuso.com/dlinfo.asp?FileID=812

nunomaia

Can you describe better your performance problem?
It’s during posting? Don’t edit SIFT tables manually.

colin_leversuch-roberts

I posted a couple of questions into the large thread below this one - I'm looking at this as a SQL Server DBA with a poorly performing system. I have a terrabyte database and it doesn't run very well - The SIFT tables are maintained by triggers, triggers can cripple performance, I have a 56.5 million row table being maintained by triggers, if I could remove this I suspect I'd get some performance improvements - hence the question. I will be talking to the Navision developers ( on site ) to establish what is needed or not. From a housekeeping point of view if I have several hundred SIFT tables without any data then I'd like to delete them.
As for the including columns in indexing, I have to establish if the maintenence of an aggreagte table is greater than changing a few indexes. From a purely database engine view indexes can be added outside of the application and the optimiser will still use them. The Resource Kit actually hints at changing indexing for SQL Server deployment. Just interested thats all, thanks.

kriki

Playing with SIFT and indexes in Navision will give a BIG performance increase. The problem is that only few persons are able to do it.
In general, there are companies specialized in this work. E.g. "Mark Brummel" is specialized in this.

colin_leversuch-roberts

hmm .. thought so! Well indexes I can do, been doing this for many years for different applications, will be discussing with the team here about the SIFT tables, I suspect we may have too fine a granularity in some and apparently we haven't tried to clear out the zero/blank rows (?) I will continue to research - thanks very much.

kriki

colin leversuch-roberts wrote:

hmm .. thought so! Well indexes I can do, been doing this for many years for different applications, will be discussing with the team here about the SIFT tables, I suspect we may have too fine a granularity in some and apparently we haven't tried to clear out the zero/blank rows (?) I will continue to research - thanks very much.

Remember to fix indexes INSIDE Navision, and NOT directly in SQL! Otherwise you might get some nasty surprises like when importing a fob they might be recreated or the import gives some error that an index does not exist.

colin_leversuch-roberts

Yeah, now I thought about that, I've encountered a couple of apps that like to control indexes this way. According to the info I have, and please correct me if it's wrong, you can't have a non-unique index in Navision - well this is ok other than it makes some of the indexes inappropriate, which means the complier may not use them, and to be blunt indexes which are non unique can often bring great performance gains and reductions in io, especially if it's a clustered index. I'd also mention in passing indexed views which can in effect create additional clustered indexes for a table ( has to be used with care ) So I would figure I'd have to add non unique secondary indexes outside of Navision, likewise if I want to make use of the include within an index.
I'm currently evaluating unused indexes, but I'm usually not too keen on removing indexes unless I'm 200% sure - and I need to understand the Navision relationship wiith indexes too.

kriki

-Navision supports non-unique indexes for its secondary keys BUT NOT FOR ITS PRIMARY KEY.
-creating views is only necessary if you point a Navision-table to it (there is a property in Navision to do that), and if it is used in Navision.

-Unused indexes : in SQL2005SP2 there is a beautiful thingy "SQLServer2005_SP2_PerformanceDashboard.msi" and this can be used to search for indexes that are not used. But like I said:they need to be changed by Navision, NOT directly on SQL.

colin_leversuch-roberts

well ....

How Indexes Work in Navision
• All the indexes in Navision are unique. Note that in the earlier example, the index is Customer No., Posting Date, Currency Code, Entry No. to enforce uniqueness.
• A primary index in Navision translates to a unique clustered index on SQL Server and a secondary index in Navision translates to unique non-clustered index in SQL Server.

from:- Microsoft Business Solutions–Navision SQL Server Option Resource Kit

Yes I'm familiar with the performance dashboard and the dmv's concerning index usage - have it installed.

kriki

colin leversuch-roberts wrote:

All the indexes in Navision are unique. Note that in the earlier example, the index is Customer No., Posting Date, Currency Code, Entry No. to enforce uniqueness.

Standard yes. But you can change it in Navision. I already did this a lot of times and with improvements in performance (together with only maintaining certain SIFT-levels).

Marije_Brummel

colin leversuch-roberts wrote:

well ....

How Indexes Work in Navision
• All the indexes in Navision are unique. Note that in the earlier example, the index is Customer No., Posting Date, Currency Code, Entry No. to enforce uniqueness.
• A primary index in Navision translates to a unique clustered index on SQL Server and a secondary index in Navision translates to unique non-clustered index in SQL Server.

from:- Microsoft Business Solutions–Navision SQL Server Option Resource Kit

Yes I'm familiar with the performance dashboard and the dmv's concerning index usage - have it installed.

What version are you using? In 4.0 SP1 we have introduced new features like changing the clustered index and being able to have an alternate SQL Index.

Wow, I am typing we, looks like I am working for Microsoft to often.

colin_leversuch-roberts

sorry should have clarified - 4.0 sp2 just going to sp3 ( to try to improve performance! ) I asked MS if they were going to update the docs and possibly for version 5.
OK I take the point about the indexes, I have to find some docs on Navsion and talk to the developers - currently I'm looking only as the DBA.
I think, as I mentioned in another post, it's a matter of scale, my database is nearly 1 terrabyte so inappropriate indexes are more likely to be a problem - I've just done a calc on a small table , 195k rows which has 22 secondary indexes, the addition of the PK column adds 43Mb to the size of the leaf levels of these indexes, I suspect many of the indexes are not very useful anyway - but that's another story!
But back to the SIFT table with 56 million rows, if I could change indexing on the base table to eliminate the SIFT table I would save a large amount of storage and cease to have to maintain the table which as it's maintained by triggers must be expensive. I just can't find the forum posts, or maybe a blog, which mentioned using includes in sql2005 to eliminate the use of the SIFT tables.
Currently MS are providing plan guides in an attempt to improve performance, these force recompile, and we're looking at a fix which removes the FAST option in queries which can give problems ( this is a SQL Server issue ) and relates ( afaik ) to using order by with FAST.
Any observations on these areas would be appreciated.

kriki

colin leversuch-roberts wrote:

I just can't find the forum posts, or maybe a blog, which mentioned using includes in sql2005 to eliminate the use of the SIFT tables.

Here is a post about it : http://www.mibuso.com/forum/viewtopic.php?t=16358.
But Navision is not ready to use them, so I think that for the moment it is better not to use them.

Marije_Brummel

I have written a post about covering indexes on SQL and NAV, which you can also read as indexes with included columns.

http://dynamicsuser.net/blogs/mark_brum ... dexes.aspx

I would also highly recomend contacting SQL Perform in London UK. These guys are hired my microsoft to tune the code Dynamics NAV product.

Please remember that planguides are really the last and final solution you should try after everything else has been done like tuning indexes, sift and optimizing code and SQL sofrtware/hardware settings.

Good luck.

colin_leversuch-roberts

Thanks very much - appreciated. This is my first expereince of Navision, just over a week, so I'm slowly looking. I have many years exp of tuning sql server right from version 6. The main appeal of trying to remove the SIFT tables is to remove the database triggers.
I've no real feel for how the plan guides are helping, I have around 200 just now, but I can't help thinking recompilation is not really the answer. I think SQL Perform have visited - but I'm not in a position to make any comment.
I can only observe that from a scalability point of view Navision ( 4 ) isn't very SQL Server friendly - but with all the info I'm picking up from you helpful people it seems it's only a matter of configuration.
thanks.

kriki

colin leversuch-roberts wrote:

Thanks very much - appreciated. This is my first expereince of Navision, just over a week, so I'm slowly looking. I have many years exp of tuning sql server right from version 6. The main appeal of trying to remove the SIFT tables is to remove the database triggers.

And also this is done from Navision. Like not keeping all the levels for SIFT.
So instead of updating 100's of records for each "Real" record, only a few are updated. But those will be necessary to keep reading performance for the flowfields in Navision.

Again (and I can't repeat it enough!) : maintaining indexes and SIFT is done inside Navision to avoid problems.

DenSter

Do yourself a favor and stay out of the table triggers in the SQL Server tools. Those manage the SIFT buckets, and NAV business logic relies on the accuracy of that information.

The ONLY way that any of that should be modified is through the NAV table designer. You can't just remove everything though, that will not work.

colin_leversuch-roberts

I shall have to read up on Navision internals, I understand there's now an option to use SQL indexes instead of Navision indexes. My gut feeling is that the default indexes might be fine with a small sub set of data, <50GB but may not be totally appropriate for 1,000 GB, well my initial investigation of one of the smaller ( non SIFT ) tables shows this to be so.

No-one has answered about the several hundred empty SIFT tables in my database - can these be removed ( I assume they are disabled from within Navision ).

Marije_Brummel

If you disable a sift level in NAV the sift table gets deleted, so if you have empty sift tables chance is that the sift level is still active but there is nothing to populate.

You might want to use the SIFT tool on the NAV Tools CD to see what SIFT is or is not used.

Please do not delete the tables manualy.

David_Singleton

colin leversuch-roberts wrote:

I shall have to read up on Navision internals, I understand there's now an option to use SQL indexes instead of Navision indexes. My gut feeling is that the default indexes might be fine with a small sub set of data, <50GB but may not be totally appropriate for 1,000 GB, well my initial investigation of one of the smaller ( non SIFT ) tables shows this to be so.

No-one has answered about the several hundred empty SIFT tables in my database - can these be removed ( I assume they are disabled from within Navision ).

Robert, sorry to be blunt, but I think the question HAS been answered:

Basically YES some of these can be deleted, BUT this needs to be done not by a SQL professional, but by a NAV-SQL professional. I suggest that you contact one of them to get help on this. If you have 1Tb database in Navision, you can't just apply general SQL tuning knowledge to this. If you plan to do NAV-SQL tuning for a living, then go to a NAV-SQL course, such as run by SQL Perform or SQL Sunrise and learn to do it the Navision way. But if this is a one off, then get in a NAV-SQL specialist to help you out, other wise you could really screw up the system.

Sorry, I know this is not what you want to hear, but believe me its the correct answer.

Marije_Brummel

SQL Sunrise does not do it the Navision way, but does all kinds of stuff on SQL level instead of NAV.

kriki

Mark Brummel wrote:

If you disable a sift level in NAV the sift table gets deleted, so if you have empty sift tables chance is that the sift level is still active but there is nothing to populate.

Mark means that the whole index is NOT maintained as SIFT, not just 1 level inside the SIFT-index.

DenSter

colin leversuch-roberts wrote:

No-one has answered about the several hundred empty SIFT tables in my database - can these be removed ( I assume they are disabled from within Navision ).

I think you haven't really been paying attention so let me give you a straight answer.

Yes they can be removed. Please go ahead and remove all of them, and when (not if but when) you get into performance problems, you can get in touch with any of the SQL/NAV experts in this community

So the proper answer is: No they cannot be removed without proper analysis, by someone who knows NAV. You are underestimating the influence of NAV in this process. What you seem to refuse to accept, because it has been said in a number of your threads, is that these types of performance issues can't be fixed with SQL Server knowledge alone. You have to get a NAV expert involved, not just answering a question or two in the forum, but sitting next to you working on the issues with you. You'll be surprised at how much you can learn about SQL Server.

colin_leversuch-roberts

OK if that's the way the posts are going to go then I'll duck out of this forum -
I'll just mention in passing that I make my living mainly by tuning production sql server systems for blue chip companies and financial institutions, every application claims you need an "expert" from the application to tune, my experience is that in the main this is not true and fundamentally inappropriate indexes are a common trait, and that of not understanding SQL Server.
I would be interested if anyone who is posting has a >1 terabyte database and what they might have done to it to improve performance.
There has been some consultancy I understand from a "Navision Tuning Expert" - I wasn't here then and it would be impolite of me to make any comment other than we still have performance problems.

DenSter

I'm not dismissing your apparent experience, I am just trying to establish that you won't do the same to NAV knowledge. You are right, it is mostly a function of inefficient (or downright wrong) indexes in the NAV database. Simply turning everything you don't like off in SQL Server is not going to help though, you will need the input of a NAV expert who knows their way around.

I have seen a NAV database be corrupted by a SQL Server expert, because he completely dismissed the notion that I, as a NAV expert with some SQL Server knowledge, could possibly add anything he didn't already know. You sound exactly like that person.

Please recognize that you could provide better service to your customers by including a NAV expert in your work. It will only make you a better consultant.

thaug

colin leversuch-roberts wrote:

OK if that's the way the posts are going to go then I'll duck out of this forum -

And you will be back when you break Navision. I don't think we are being unreasonable. Just like you have experience with some high end databases, we have seen what can/will/does go wrong when things are not done right. Or I should rephrase that, not done the Navision way. Right or wrong from the SQL world, this is what we have to deal with, and it is simply not possible to translate all of your SQL knowledge to a Navision implementation.

DenSter

Furthermore, I would love it if you'd stick around and as you start understanding the NAV side of things, you will understand what we go through and help us understand the SQL Side of things, from a purely SQL Server perspective.

I do not want to get mixed up in right/wrong discussions, I just want everyone to take full advantage of other people's knowledge and experience. Listening and trying to understand the other guy, without being dismissive, is a big part of that.

stryk

Hi all!

Well, reading this thread twice there are a lots of aspects included, I'd like to post "my two cents" as well

SIFT Tuning:

Sorry, this will probably be a long shot ...
The basic state for a table - a Ledger Entry (LE) - is WITHOUT SIFT. This grants maximum write perfromance as only the LE is written. When querying aggregated information, the Read perfromance is worst as always the full result-set is read from the LE.

Example:
LE with Item No, Date, Quantity

Assuming 1000 LE are inserted per Item per Day. To get the summed Qty. per Day one has to sum up 1000 records.

Now SIFT could improve things, if reading the full LE is perfroming too bad. Now one could enable the first level of aggregation, menas in our example: aggregate per Item per Day, thus 1000 LE are combined to 1 SIFT; aggregation 1000 : 1.

Looking at the SIFT structure (Object Designer or Resource Kit), this first aggregation level is the one before the last one (caution: the last one is NO aggregation; it "sums" per Primary Key value, thus "aggregation" is 1 : 1).

Enabling this "bucket" would cost little Write performance, but one could gain a lot of Read perfromance: instead of 1000 recs only 1 is taken.

Now, - in our example - when querying the Quabtity per Month, C/SIDE would not read 30 x 1000 records due to the missing SIFT bucket, it will sum up 30 Day records. Hence, it would cost more Write perfromance to aggregate 30000 : 1; the difference between reading 30 Day records or 1 Month record is marginal.

To make a long story short:
Stnadard NAV is managing the SIFT wrong, because all buckets are enabled by default. One should disable all buckets, EXCEPT the pre-last one = the first aggregation level.
If there is evidence, that a higher aggregati is required, e.g. Month, then this bucket could be enabled, too.

Using the "NAV SQL Resource Kit" one could find out which tabled to improve by following this principle (e.g. Filter: Cost per Record >= 20 and No. of Records >= 500.000) which results actually in all the big Ledger Entry tables (Item Ledger Entry, Value Entry, G/L Entry, VAT Entry, etc.).

For small tables (< 100.000 records) actually all SIFT buckets could be deleted (depends on Businiss Logic!!!).

And: when having the first aggregation enabled, one could create a "Covering Index" on the SIFT tables (as Mark mentioned), this would speed up to generate the "Summ of the Sum" (e.g. the Month from the Day). Maintaining this Index is less costly than maintaining another SIFT bucket.

To clean-up the SIFT tables, means deleting the records were ALL sum-fields are zero, you could use this thing: http://www.stryk.info/ssi_delzerosift.zip
(once published in MIBUSO, for certain reason not anymore)

Index Tuning:

Well, there are a lot of things which could be done, like sorting the CI fields by selectivity, removing the UNIQUE flag and the dispensable PK fields from the NCI, etc.. YOu could achieve this by using the properties "SQL Index" and/or "Clustered" but to re-work the whole database in a more convenient way you need utilities as mentioned in this thread.
<Ad>Just to metion it: there is a third tool available: "The NAV/SQL Performance Toolbox"! http://www.stryk.info/english/toolbox.html</Ad>

Hope this helps ...

Kind regards,
Jörg

colin_leversuch-roberts

thanks, it does help. We will probably run some tests on our test server, it's never an easy task taking apart the queries and indexing on a database - the analysis is done within sql server - it's a possibility that the aggregations may run quicker from the source tables, with approprite indexing, then reading from the SIFT tables - it's a consideration that removing the SIFT tables reduces the overhead associated with their maintenence and with the associated trigger plans many of which spill into the memtoleave area and can give problems, esp on a 32 bit system. Going to full 64bit changes much, however the removal of SIFT tables would also reduce my database size significantly and likely reduce pressure on the procedure and data cache. The SIFT tables often have heavy indexing too which may actually degrade performance and increase storage.
Many thanks again.