Effect of Dropping SIFT Buckets within Navision

sgg

Hello,
Considering the following Scenario.
I have Navision 3.6 running on SQL 2000.
Looking at The Item ledger Entry Table (32) I find a Key with the following fields:
Item No.,Variant Code,Drop Shipment,Location Code,Bin Code,Posting Date

The Resulting SIFT Buckets are as follows:

1. Item No.
2. Item No.,Variant Code
3. Item No.,Variant Code,Drop Shipment
4. Item No.,Variant Code,Drop Shipment,Location Code
5. Item No.,Variant Code,Drop Shipment,Location Code,Bin Code
6. Item No.,Variant Code,Drop Shipment,Location Code,Bin Code,Posting
Date:Year
7. Item No.,Variant Code,Drop Shipment,Location Code,Bin Code,Posting
Date:Month
8. Item No.,Variant Code,Drop Shipment,Location Code,Bin Code,Posting
Date:Day
9. Item No.,Variant Code,Drop Shipment,Location Code,Bin Code,Posting
Date:Day,Entry No.

Looking at the Structures, It seems that Buckets 1 to 5 are "included" in Buckets 6 to 9.
What effect will dropping/Disabling Buckets 1 to 5 have on the System Performance ?

Marije_Brummel

If someone reads a flowfield based on this table with a filter on Item No. and Location code, in the old scenario Navision would read the sift bucket for this value (1 read) and return this to the client.

Since you will disable this bucket Navision will read a higher level. This could be with posting date included. If you have been working with Nav for 8 years and the item has a lot of enties on a lot of posting dates, this can result in maybe 200 reads per year x 8 = 1600 reads.

This decision has to be made per customer weather it is good or bad.

Good luck.

sgg

Thanks Mark,
Does it therefore mean that the Write Operations will become faster than Before disabling the Buckets 1 to 5 while the read operations will become Slowers?

Which of these two operations could be freezing the screens in Navision?
Slow Reads or Slow Writes?

Marije_Brummel

Freezing screens are almost always reads, not always caused by Navision though; it can also be caused by SQL issues.

Disabling will give less write overhead thus better performance durung posting, unless offcourse the posting routine reads the SIFT. :?

Good luck.

sgg

Suppose I have 2 Keys defined as follows :

1. Item No.,Variant Code,Drop Shipment,Location Code,Bin Code,Posting Date


2. Item No.,Variant Code,Source Type,Source No.,Posting Date


From Key 1, The following SIFT Buckets Are created:

1. Item No.
2. Item No.,Variant Code
3. Item No.,Variant Code,Drop Shipment
4. Item No.,Variant Code,Drop Shipment,Location Code
...
...
...


From Key 2, The following SIFT Buckets Are created:

1. Item No.
2. Item No.,Variant Code
3. Item No.,Variant Code,Source Type
4. Item No.,Variant Code,Source Type,Source No.
...
...
...

from these 2 sets of SIFT Buckets we see that Bucket 1 is same for Key 1 and Key 2. Same goes for Bucket 2 in Key 1 and Key 2.

What will be the effect of Disabling Buckets 1 and 2 in KEY 2 given the fact that the same set of SIFT Buckets exists on the Previous Key(1) and also given the fact that These 2 sets of Buckets contains the same number of SIFT Records essentially?

Marije_Brummel

You can safely delete all sift buckets which are exactly the same.

Navision is smart enough to figure out another one to use.

Always disable the last one though since navision will search top-bottom.

sgg

Thanks Mark.

If Navision figures things out in this way, then It is Good News when talking about Performance Boosting by reducing SIFT Buckets.

However one might be tempted to ask Microsoft to further enhance the Design of Navision (Dynamics NAV) in such a way that it should automatically Detect Existing SIFT buckets such that it will disable those already existing when Creating Buckets for SIFT keys.

What do you think?

ara3n

5.0 Sp1 is redesigning SIFT implementation using indexed views.

sgg

That will be Much more better.

Thanks guys.

bbrown

sgg wrote:

That will be Much more better.

Thanks guys.

Maybe. Since indexed views are updated with base table updates, they could potentially have some of the same performance issues as SIFT

kriki

bbrown wrote:

sgg wrote:

That will be Much more better.

Thanks guys.

Maybe. Since indexed views are updated with base table updates, they could potentially have some of the same performance issues as SIFT

The speed for updating will be a lot faster because for each record only 1 indexed view-record (per index for which SIFT is maintained) is updated and NOT 1 SIFT-record per field in that index. So instead of updating 100 extra records in real tables, only a few extra records (1 per index with SIFT-maintenance) is updated. So out of the box, writing will be a lot faster.

Multiple users updating data that goes into the same indexed view could still be a problem. But taking account that less time is spent on writing, this also should be less of a problem than before.

bbrown

Would it not be 1 view per SIFT level? Also what about field that use different Sift levels?


Also creating too many SIFT levels will have the sameperformance impact

kriki

bbrown wrote:

Would it not be 1 view per SIFT level? Also what about field that use different Sift levels?


Also creating too many SIFT levels will have the sameperformance impact

I did some performancetesting on my portable (meaning NOT a server!) with indexed views. I had a table with some fields (Item No.,Location code,posting date, quantity) with a view on "item No.location code,posting date" to simulate item ledger entries and created some records in it (50 items per 5 location codes for each date in the year), I had some 18.000.000(!) records. So I had a date range that went into 1/1/2000 to 01/11/2200. With this broad range of dates it was slow, but if I limited it to 10 or 20 years, it was lightening fast.
So I do not see why it would be a view per SIFT-level. I think that this SIFT-implementation will be fast enough for most implementations.

BTW : I also created a view on "Item No.,location code" and used that in case I had no date range, and it was again fast. So if an extra level is needed, just create a new index just BEFORE the original index without the last fields and you have your SIFT-level. This last would fall under SQL-tuning.

ara3n

kriki wrote:

BTW : I also created a view on "Item No.,location code" and used that in case I had no date range, and it was again fast. So if an extra level is needed, just create a new index just BEFORE the original index without the last fields and you have your SIFT-level. This last would fall under SQL-tuning.

would Navision use a different index by default? or will you have to change the code to setcurrentkey on the new key?

dmccrae

There is an indexed view per active SIFT Key; the Levels property is no longer used.

C/SIDE is deciding which indexed view to use based upon the sum fields required and the filter; it is not dependent on SETCURRENTKEY, so no code change is necessary.

Nobody

I would replace SIFT with non-clustered indexes with included columns. The key fields are the indexed columns and the sum fields are the included columns. That way the CALCSUM query could be covered by a simple nonclustered index seek, and included columns require no stats updates so the overhead is no greater than that of a standard non-clusterd index.

Oh well atleast they are trying something. :-)

Miklos_Hollender

Mark Brummel wrote:

Freezing screens are almost always reads, not always caused by Navision though; it can also be caused by SQL issues.

Wow Mark, I think that's something very important. Thanks for that.

Reason: I think the subjective feeling about performance is often more important than objective speed. So basically then I think it means slow writes (too many buckets, too many indexes) mean the progress bar on say posting a journal is slow - that's often not a big problem because the user knows something is going on, it's working, everything is in order.

Though it can lead to blocks and I think blocks cause a freeze too, but I think a deadlock timeout of 10 seconds tells quite clearly whether that freeze is a block or not?

White Screens of Death are what really, really annoy users as they have no clue about what's going on and how long it will take. Subjectively it looks like a software error, not like just some heavy lifting.

So for sorting out White Screens of Death can we generally skip figuring out unused indexes, skip removing SIFT buckets etc. and concentrate on first putting on indexes that match the filters and have a good selectivity first, and it will usually help, then, if it's needed, general optimization (stats, reindex, degfrag,clean up 0 sift records), then, if it's needed, looking at the FIND('-') versus FINDSET(FALSE,FALSE) in the code?

stryk

Hi!

A "frozen screen" generally occurs if NAV is waiting for response: NAV sends a query to SQL Server (SELECT, INSERT, UPDATE, etc.) and waits until a reply from the server has been received - meanwhile NAV "hangs", showing just a "white screen".
The reasons for those response-delays can be different: long/many Reads, Blocks (could be related to a Read problem), I/O problems, Network problems, etc.. Here also the client PC could be the "troublemaker": e.g. if there's not enough RAM to process the resultsets, lacking CPU power, etc.!

If the LOCKTIMEOUT is set, and a process gets blocked, then SQL Server cancelles a transaction after the defined period.
If a Deadlock occurs, SQL Server recognizes this within 3 to 5 seconds and "kills the victim" - this happend independently from any LOCKTIMEOUT settings.

With SQL Profiler you could investigate what the "screen-freeze" caused:

High Reads (> 1000) AND long Duration (> 1000msec) AND long CPU time (> 1000msec) = Read/Index/Filter Problem Normal Reads (< 100) AND long Duration (> 1000msec) AND short CPU time (< 50msec) = Block Normal Reads AND normal Duration AND normal CPU time = Client site or network problem (check with Windows Performance Monitor)

Regards,
Jörg

Miklos_Hollender

Thanks, sounds useful. I suppose the Profiler itself won't hurt much the performance itself if it's always running with just StatementEnd and StatementBatchEnd ticked and filtered at duration>1000? The trouble is ususally with reproducing freezes that happen "sometimes", now the Client Monitor shouldn't always run because it really slows everything down, your block detector is great, but doesn't catch long reads without blocks, so if Profiler can be always on with duration>1000 without slowing things down that can be a big help in catching slow reads.

stryk

The Profiler won't do much harm if you just record few Events (e.g. SP:BatchCompletetd, SP:StmtCompleted, SP:RPCCompleted, TSQL:StmtCompleted, TSQL:BatchCompleted) and if you set appropriate filters, e.g. on "Reads" and/or "Duration", or just monitoring specifiv processes filtereing on "SPID" or "Login Name" etc..
Additionally you should just monitor for a limited period, say a couple of hours.

Hence, if you're "Hunting" bad queries you could filter in "Reads > 1000" AND "Duration > 100", looking for blocks with "Duration > 1000" and "CPU < 1000" (no filter on "Reads").

To force a "screen freeze" you could create some "bad code", depending on what you want to do ...
If you want to test "Blocks & Deadlocks" please look at my BLOG here:
http://dynamicsuser.net/blogs/stryk/archive/2008/05/12/blocks-amp-deadlocks-in-nav-with-sql-server.aspx; the related download http://www.mibuso.com/dlinfo.asp?FileID=958 includes two Codeunits to force Blocks or Deadlocks.
(Please be aware that the block/deadlock-detection TSQL scripts from my BLOG have been improved, the download still includes the "older" versions)[/code]