Database size reduced after restore

akman
akman Member Posts: 67
edited 2010-04-12 in NAV/Navision Classic Client
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

Comments

  • Maximus
    Maximus Member Posts: 105
    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
    kriki Member, Moderator Posts: 9,124
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • akman
    akman Member Posts: 67
    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
    Belias Member Posts: 2,998
    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 :wink: : 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).
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • stryk
    stryk Member Posts: 645
    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.
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool

Categories