*** READ ONLY *** Microsoft Business Solutions online community *** READ ONLY
It is currently Sat Oct 10, 2015 2:33 pm

All times are UTC + 1 hour [ DST ]

Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: Tip #4 - Tuning VSift
PostPosted: Sun Apr 12, 2009 3:23 pm 
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Tue Jul 19, 2005 4:49 pm
Posts: 4217
Location: Olst
Country: Netherlands (nl)
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.


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


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...

Mark Brummel | Freelance Dynamics NAV (Navision) Specialist

Read my new book: Microsoft Dynamics NAV 2013 Application Design

MY BLOG : http://markbrummel.wordpress.com

 Profile E-mail WWW  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC + 1 hour [ DST ]

Who is online

Users browsing this forum: No registered users and 1 guest

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: