mibuso.com
Home› NAV/Navision Classic Client
Options

Restore Performance problem when upgrading from 3.60 to 4.00

cnicolacnicola Member Posts: 181
edited 2006-12-04 in NAV/Navision Classic Client
Currently we have a customer on Navision 3.60 SQL 2000 with a db of approx 200GB. Their hardware is as follows:
4 Xeon 3.2 Processors
8 GB RAM
1 log file on a Raid 10 with 4 15k SCSI drives (internal to server)
4 data files on a Raid 10 with 7.2K SATA drives (on a SAN they recently purchased and according to their IT guy it is fiber to fiber).

They bought the SAN box 2-3 weeks ago and they did a full backup and Navision restore on the above layout when db was 200GB and it took 23 hours to finish (with 3.60 and SQL 2000).

Now this weekend we tried to upgrade them to 4.00 SP2 and SQL 2005. We also deleted some old data reducing their db to 150GB before the upgrade.

They closed on Friday, installed SQL 2005 and Nav 4.00 SP2 and then started the restore. The restore took from Sat 4 am to Sun 7 pm, so that is 24 + 15 = 39 hours (vs 23 hours) for a db that was 50 GB smaller. Needless to say, though we tried, we did not have time to finish the upgrade routines by this morning so we’ll have to revert back to 3.60 for the time being.

Given that the hardware and the file layout was the same in both cases (only new software installed) I would have expected the Navision restore to take less time given the smaller db (not to mention that SQL 2005 and SP2 are supposed to be faster). And therefore the issue lies in the Nav 4.00 SP2 and SQL 2005 combo somewhere.

So now I am stuck as to how to do an upgrade in the only window the client has which is the weekend. I am sure I am doing something wrong but I just cannot tell wheat. So any suggestions (even the "look at the searches" type :lol: ) are more than welcome.
Apathy is on the rise but nobody seems to care.

Comments

  • Options
    ajhvdbajhvdb Member Posts: 672
    What part took this long..I bet it was the item ledger
    Create dynamic Excel or Word documents, ReportX
  • Options
    SimonWSimonW Member Posts: 77
    Have you considered performing a SQL backup/restore, then opening with the NAV 4.0 sp2 client.

    Also, be aware that SP3 came out last week and that now gives you 2 security models to choose from

    Regards

    Simon
  • Options
    cnicolacnicola Member Posts: 181
    I appreciate the answer but the issue here is that there is a performance degradation compared to same thing on 3.60 and SQL 2000.
    Now I know I can just convert the database but my problem is that I do not want to go to SQL 2005 until I understand what has caused the perf problem.
    So my question is if anyone else has encountered issues like this with 2005 and 4.00 SP2.

    P.S. MS is sending me some fixes that they think might solve the issue. Will keep you posted.
    Apathy is on the rise but nobody seems to care.
  • Options
    SimonWSimonW Member Posts: 77
    Presume you
    1. Ran the restore on the SQL box itself
    2. Switched the db to single user mode
    3. Switched the recovery mode to simple

    Regards

    Simon
  • Options
    cnicolacnicola Member Posts: 181
    SimonW wrote:
    Presume you
    1. Ran the restore on the SQL box itself
    2. Switched the db to single user mode
    3. Switched the recovery mode to simple

    Regards

    Simon

    Hi Simon,

    1. Yes.
    2. No.
    3. No.

    But I did not do that before and never had an issue.

    Again I guess we are getting wrapped up in details:

    Why does 3.60 SQL 2000 restore takes close to double the time of 4.00 SP2 SQL 2005? Hardware was identical both times and same goes for file layout.
    Apathy is on the rise but nobody seems to care.
  • Options
    bbrownbbrown Member Posts: 3,268
    Are any of the SQL files (Data or Log) auto-expanding during the restore? Create the files large enough so they do not need to expand during the restore.

    Simple Recovery mode will not help. The restore is processed as a single transaction.
    There are no bugs - only undocumented features.
  • Options
    VincentNAVISIONVincentNAVISION Member Posts: 1
    According to my 20 years experience, only one reason can cause such performance drop - Big change in database size (add too many records or del too many records) before making a backup without reindexing. For a database size you have, this step will save about 1/4 of the time in restoring. You should be able to complete the restore in about 29 hours instead of 39. :wink:
  • Options
    bbrownbbrown Member Posts: 3,268
    According to my 20 years experience, only one reason can cause such performance drop - Big change in database size (add too many records or del too many records) before making a backup without reindexing. For a database size you have, this step will save about 1/4 of the time in restoring. You should be able to complete the restore in about 29 hours instead of 39.

    I have to disagree. The restore of a Navision backup (*.fbk) is processed as a single logged transaction in SQL. This can result in a rather large transaction log file when restoring a large backup set. If SQL must auto-grow any of the database files (data or log), this can add time to the restore process.

    By the way, Microsoft SQL Server was introduced in 1989. It ran under OS/2 and was called something like "SQL for OS/2". I don't remember the exact name.
    There are no bugs - only undocumented features.
  • Options
    thaugthaug Member Posts: 106
    Expanding all data and log files out to what you think will be needed is suggested, so that the process does not need to do this during the restore. Remember that the log file will need to be at least as large as the data size.

    As discussed, changing the recovery model to Simple will not do much because of the way that the restore process works, which also explains the log file size issue. Nothing worse than doing a restore and finding a problem that stops the restore on one of your last tables in the database, meaning that you have to run the restore process again... not that such a thing has happened to me before :wink:

    I have tried changing to single user mode with no noticeable difference. Indexing should also not make much of a difference, since the PK is only backed up and restored, the secondary keys are done at the end of the restore process.

    Given that this is SQL 2005, I would do the restore with the SP3 client, which fixes many SQL 2005 issues. MS will probably also send you the hotfix that solves these same issues.

    I'm wondering too if you can experiment with another machine, or the same server with a really big hard drive installed in a DAS configuration, to see if there is any tweaking that needs to be done with the SAN. Restore to this direct attached drive instead and see how long it takes. If that does work well and fast, you may be able to use the snapshot feature of your SAN to copy that data to the SAN drives, but I don't know too much about SAN's, so I don't know about this for sure.
    There is no data, only bool!
  • Options
    RobertMoRobertMo Member Posts: 484
    I am at the moment also restoring quite a large DB (70GB) at a customer. They have combination of N3.60/SQL2000. I don't know the exact configuration/attributes of the HW of the SQL server, but it should be quite good. The SQL server is also using SAN as data storage - and they have added brand new disks just to create space for the restore.

    What I have noticed that the restore is running at least 2 times slower, compared to what I have tested on a PC with local disks - a PC worth 1.500$. (I was using same combination N3.60/SQL2000)
    I made there a test to measure the time, because I was sure the "real" server will outperform PC anyway. It seems I was wrong.

    I suggest you try to start restore on a "good" PC - in your case perhaps 2 local disks each of 200GB to separate data and log files.
    After restore (and if the spent time is < 35hrs), then you just do the SQL backup on PC and SQL restore to SQL server on SAN. I am sure you will be faster at total.
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • Options
    RobertMoRobertMo Member Posts: 484
    Some additional numbers:

    During restore of 60.000.000 records of table 355, Navision restore dialog window was showing me that it is copying cca 100KB! per second !?! Yes that means 6MB per minute or 360MB per hour. And I have 16GB of 8 fbk files in total. But I am sure it will speed up.

    Well my asumption is that Navision backup takes a batch of records (eg. 1000 or 2000 or 10000) and inserts them into DB. Additional asumption is that the avergae time to insert specific number of records is more dependant on the number of the records then on the size of each record. E.g. to insert 10000 records of table 355 takes similar time span as 10000 records of table 17. But the record size is very different, thus more KB are reported during restore of T17 then during T355.

    But this is only my assumption ? Can someone confirm or explain how Navision restore actually insert data to DB?
               ®obi           
    ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
  • Options
    thaugthaug Member Posts: 106
    I did a restore this past weekend to a new SQL 2005 server. Our database size is about 100 GB (actually only about 20 GB of actual records, the rest are BLOBs from the Matriks solution), and I was able to do the restore locally on the server in about 6 hours. When we upgraded to 4.0, it was an in place upgrade on the same server, and that restore probably took about 18 hours or so.

    With these servers that are mentioned, I would think that the performance should be better for you guys. I would download a tool called Spotlight for SQL to try and get an idea of what is actually going on, and where you can tweak the performance a bit. With my HP RAID controller, I can set a few different scenarious depending on the usage of the drive (such as 50/50 write/read, or 25/75 write/read, etc.) Maybe you can change some of these settings temporarily while you do the restore.
    There is no data, only bool!
Sign In or Register to comment.