Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 305 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Tip #4 - Tuning VSift
Marije_Brummel
Member, Moderators Design Patterns Posts: 4,262
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...
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...
0