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.
When the other fields in the index are heavily used and have a lot of values, the number of reads can be huge!
So what if you have a customer who uses NAV for 7 years thus having potentialy 2500 posting dates.
In the old days you would simply enable the top SIFT bucket but what with VSIFT?
Answer; Create a new key above the old keys so the driver will pick that up and give it priority.
Voila, it is as simple as that.
BUT: Be carefull: You could potentialy be changing business logic. NAV has the nasty habbit of allowing halfsyntaxed SETCURRENTKEY statements. In that case it uses the first key that it can find that starts with those fields. Maybe that in another blog...
With the introduction of VSIFT, a new perception was introduced. There are no more Zero SIFT records.
More about VSift in http://dynamicsuser.net/blogs/mark_brummel/archive/2009/04/12/vsift-the-day-after.aspx
With the classic SIFT it was possible to have SIFT records that did not contain any totals. These were called Zero SIft Records or Sift Zombies.
They were easy to delete by optimising the Dynamics NAV tables but if you forget to do that regularly there could probably be thousands to millions of these records keeping space in your database and potentialy slowing down your processes.
I will explain why they did this in some other blog but this blog is about VSift and those empty records.
Since the VSift do not actualy contain the zero values or any values at all they should potentialy not be there but... even with VSift it is possible to get potential zeroes.
In the VSIft a new field is introduced $Cnt which counts the records in the filter. Now if you use this query:
In a CRONUS database you'll get this result:
Funny isn't it.
Fortunately you will never be ably to fire queries like this from NAV.
One of the big advantages of VSift is that only the fields that are used are included in the SQL Statement like
This time another tip on how to use the SQL Management Studio.
Dynamics NAV (Navision) keeps a copy of all tables on SQL server for each company you create, no matter if the table is populated or not. This way of creating tables can lead to a long list of tables in the SQL Management Studio.
Expanding the table tree can even take minutes on heavy used systems. How to avoid that.
Step 1. Go to the SQL Management Studio, connect to the server and expand the database you want to use
Step 2, right click on the table or view secion and choose filter.
Step 3 - Apply the filter
Remember that it is automaticaly a part of the filter, not an exact match
Some time ago a new technology for SIFT was introduced.
SIFT is a technology that makes it possible in NAV to use the flowfields with the powerfull flowfilter technology. It stands for Sum Index Flow Techology. In the classic database the flowfield data was kept at index level. SQL does not have these capabilities so they had to work their way around it.
As most of you know, the claccic way to do tis was with secondary tables which were updated though SQL triggers. These triggers where created and maintained by the finsql.exe and there have been some different versions. Maybe I will write more about that in a later blog.
But with the introduction of Service Pack 1 for Dynamics NAV 5.0 this changed. The sift tables are no longer maintaned and the triggers are abandoned.
Instead SQL maintains a View for each index on tables with SumIndexFields. SQL Views represent data from tables and are updated when ''viewew'. But when you add an index to a view, that information get's actually stored into the database. Microsoft has decided to use this built in technology instead of the old triggers and tables.
So the question is: is this a bad thing?
The answer is no. But don't expect very much performance improvements either.
Those of you who went to Convergence EMEA last year know that I did some performance comparissons between version 4, 5 and NAV2009. The results where never published becuase of political reasons but in some of my next blog posts I will use some information from those tests.
So how does it work:
Let's have a look at the table G/L Entry. It has 5 indexes where SumIndexFields are maintained
So let's look in the SQL Management Studio. Use the filter (http://dynamicsuser.net/blogs/mark_brummel/archive/2009/04/12/tip-3-filter-in-sql-management-studio.aspx) on the views to see the G/L Entry views
Notice that there are 5 VSift's numbered 1 to 4 and 8. They follow the Key numbering in NAV. This is different from the old SIFT tables which used to be numbered in order.
Expanding the Columns and Indexes learns us that all fields in the index are there including the SumIndexFields and there actualy is a clustered index.
And when reading the data
Voila, it is there
More VSIft in the next Blog posts.
I know that this blogpost is far from orriginal but I am always looking for this information so I am putting it in my blog
The table $ndo$dbproperty has a field DatabaseVersionNo. This is a list of recent versions.
With special thanks to Dean McRae!
/ Database versions for: 2.50// 1 : Beta 1 data formats// 2 : Beta 2 data formats (change in Code field value format from space-padded prefix to hex prefix)// 3 : Beta 3 data formats (change in Code field value format from hex prefix to no prefix or special format)// 4 : Release data formats (SIFT trigger identifiers changed and new indexes on SIFT tables)// Database versions for: 2.60(A->C)// 5 : Release data formats (change in Session view and SIFT triggers to use CONVERT rather than CAST for datetime fields)// Database versions for: 2.60(D->F)// 6 : Changes in SIFT triggers due to UPDATE... WHERE... performance problem// Database versions for: 3.00// 7 : Beta 1 data formats (change in Session view for SQL Server 2000; change in chartable)// 8 : Beta 1 data formats (change in chartable; changes in SIFT triggers due to UPDATE... WHERE... performance problem)// 9 : Beta 1 data formats (added maintainviews dbproperty)// 10 : Beta 2 data formats (mapped invalid characters to "_" for SQL object identifiers)// 11 : Release data formats (added diagnostics dbproperty)// Database versions for: 3.01, 3.01A, 3.01B// 12 : Release data formats (added identifiers dbproperty)// Database versions for: 3.10, 3.10A// 13 : Release data formats (added maintainrelationships dbproperty)// Database versions for: 3.60// 14 : Release data formats (change in Session view for new extensions; identifer conversion dbproperties; per-database license dbproperty)// Database versions for: 3.70// 15 : Release data formats (change in chartable; added checkcodepage, quickfind, maintaindefaults dbproperties;// change in Session view for new extensions and column COLLATE;// change in Database File view for column COLLATE)// 16 : Hotfix 5 data formats (change in Session View for removing duplicate connections based on 'sysprocesses.ecid')// 17 : Hotfix 12 data formats (change in Session View for removing duplicate connections based on 'sysprocesses.ecid';// the identifiers and invalididentifierchars dbproperties are updated to OEM)// 18 : 3.70B data formats (change in Session View, removing join to syslockinfo; also removed from session count query)// Database versions for: 4.0// 20 : Pre-release data formats (change in Session view for new Idle Time column;// change in permission table to include new object types)// 30 : Security release data formats (change in Session view, removing join to syslockinfo;// change in chartable; creation of security objects)// Database versions for: 4.1// 40 : Release data formats (added locktimeout, locktimeoutperiod, hardrowlock and bufferedrows dbproperties)// Database versions for: 4.2// 50 : Update 3 (no actual database conversion was made)// Database versions for: 4.2 Update 4, 4.3// 60 : Alterable security option; fix for SIFT data corruption around Closing Dates (added securityoption dbproperty)// 61 : Rebuilding SIFT triggers in order to correct the Sum problem// 62 : Rebuilding SIFT triggers in order to correct the delete statement// 63 : Prevention of updating SIFT twice// Database versions for: 5.0// 80 : Release data formats (added system tables for office integration and record links; change in table descriptions for clustered property)// 81 : Rebuilding SIFT triggers in order to correct the Sum problem// 82 : Rebuilding SIFT triggers in order to correct the delete statement// Database versions for: 5.0 SP1// 95 : Removed SIFT tables and triggers, creating Indexed Views instead// Database versions for: 6.0// 120 : Release data formats (incl. CTP1) (added several new system tables;// extended record links system table)// 130 : Pre-release data formats (incl. CTP2) (removed SIFT tables and triggers, creating Indexed Views instead)// 140 : Pre-release data formats (removed Assembly and Relationship system tables;// added enabledforservice dbproperty)