Database size reduced after restore

akman

Hello,

I just made a technical upgrade from NAV4SP2 to NAV5SP1. To do so, I restored a navision backup from the new client. I use the sql server option (sql server 2008).
The database size before the technical upgrade and after a database shrink, was about 30 gb (just the data without the transaction log). After the technical upgrade, the new database is just about 13 gb.
I had never deleted zero sift entries. Is this size reduction normal?

Regards

Maximus

Hi,

I think this has to do with the fact that 5.0SP1 works with SQL Server views and doesn't use the SIFT tables any more. The number of tables of which the database consists is therefore much smaller and requires less space. But I never thought that the effect would be so great. This should give you a huge permance boost.

kriki

At the moment, you don't have 0-sift records anymore and ONLY the lowest level is saved in the DB with 5.0SP1+.
So you that can change a lot.

akman

kriki wrote:

At the moment, you don't have 0-sift records anymore and ONLY the lowest level is saved in the DB with 5.0SP1+.
So you that can change a lot.

Thank you all for your answers.
What do you mean by lowest level? I can change a lot?

Belias

akman wrote:

kriki wrote:

At the moment, you don't have 0-sift records anymore and ONLY the lowest level is saved in the DB with 5.0SP1+.
So you that can change a lot.

Thank you all for your answers.
What do you mean by lowest level? I can change a lot?

there's a long explanation to do about SIFT and VSIFT to explain what is the "lowest level": search mibuso for "SIFT buckets" or similar, and you'll find it.
anyway...the so called VSIFT (indexed views that substituted the old SIFTs) cannot be changed except by sql side, but if you want to do it...don't do it : ask an expert, because it is not easy, and there are better ways to optimize your database, before this one...moreover, if you re-save the table which vsift have been modified by sql, you'll lose the modification.
Morale: you can't do anything about vsift, except activating/deactivating them (that is, remove maintainsiftindex flag on key list of a nav table).

stryk

akman wrote:

kriki wrote:

At the moment, you don't have 0-sift records anymore and ONLY the lowest level is saved in the DB with 5.0SP1+.
So you that can change a lot.

Thank you all for your answers.
What do you mean by lowest level? I can change a lot?

Well, actually we HAVE Zero-SIFT records with VSIFT, too: when the VSIFT is summing by SELECT SUM(...) GROUP BY ... it happens naturally that certain sums equal zero (I don't know if this still applies to the most recent NAV versions). The problem with VSIFT is, that you cannot simply delete those records as it was possible with old SIFT.
That is the reason why some queries perform slower with VSIFT than with optimized SIFT - if there are too many of those Zero-Sum-Records calculated by the VSIFT, reading data might be slowed down.
As mentioned, you cannot delete those "records" (as they are no real physical records), thus VSIFT optimization is somewhat tricky ...

Regarding "lowest level": this means to aggreagate / group the sums by the complete defined "Key" (without trailing PK fields); not by an increasing multi-level aggreagtion as with standard SIFT.