Tip #4 - Tuning VSift

Marije_Brummel

After all the theory about VSift in my previous blog posts now a tip about how to tune VSIFT.

Before I continue first this.

VSift is not a bad technology but the implementation in NAV has one big downside. The good old SIFT levels have disapeared.

In older versions of NAV there was a SIFT Level property. This enabled you to decide which SIFT level best suited your implementation.

Many people say: Disable all top levels. But I disagree on that. Measure the exact levels you need and only enable those or better: creating your own levels.

That is what this blog is all about: creating your own levels. Before SQL came I used to tune big C/Side databases and also then we did not have this SIFT Levels property, so we had to create our own.

To know how that is done it is critical to know how the NDBCS driver works. This driver decides which VSIFT to use. This driver is as stupid as SQL is intelligent. (Sorry Dean). As far as I know it has always been this way, also in the Natvive days but maybe some MSFT folks can post about this.

When NAV need to calculate a flowfield it reads the Key table top down and grabs the first key that matches the definition.

Now since selectivity and number of reads/records is extremely important for your performance it can be interesting to create keys that are narrower and reduce the number of reads.

Example:

When I open the G/L account in NAV this query is executed:

SELECT

SUM("SUM$Amount") FROM dbo."CRONUS Nederland BV$G_L Entry$VSIFT$1" WITH(NOEXPAND) WHERE (("G_L Account No_"=@P1

))

With an average of xx reads.

Now when I disable this VSIFT$1 level, NAV has to go to the next available level.

Go to http://dynamicsuser.net/blogs/mark_brum ... vsift.aspx to read the rest with images...