Performance issues with restore on SQL Server 2005

Business Central/NAV Technical Forum
1

After upgrading a 3.7 - to 5.0 I have a performance issue during restore on SQL. After upgrade a restore to a navtive db takes 6 min. When I do the same on my SQL server - the restore takes 4 hours! This is a db with 110 companies, but not very many transactions in each. Next weekend I will do the same restore, but with 490 companies. db size is 2 gb

I also get problems with “out of memory” when I do this with more than 40-50 companies in one restore.

The server is a 2CPU - 8gb mem - 24 discs RAID5. (windows 2003 ent - SQL2005)

We did a simmilar restore with 24 companies, but with a 135gb db. Her we had almost no difference between the native restore and SQL restore.

Anyone?

2

Are you on NAV 5.0 or NAV 5.0 SP1? SP1 is known to have a few important performance changes.

3

Hi. I tried both…after applying a HOTFIX from Microsoft, and splitting the restore jobs into max 60 companies - I managed to restore this in 12 hours.

/Trond

4

Ok. Which hotfix?

And of course you also did run it from a client installed directly on the SQL Server?

5

Also have in mind that NAV handles such a “native” restore in one huge transaction, thus causing super-load on the SQL Server! For each company the table-objects have to be created, and their related indexes and SIFT/VSIFT structures!

This all is causing tremendous write transactions, so also a powerful disk-subsystem is essential. RAID5 is actually an absolute NO GO for databases, especially for the Transaction Log file.

To reduce the “single impact” on the SQL Server, you could also proceed like this:

Preparation: SQL Database: “Recovery Model” SIMPLE. The “Security Model” should be “Standard” (anyway).

  1. Native DB: Export all Tables into a FOB
  2. Native DB: For all NAV Keys set “MaintainSQLIndex” and “MaintainSIFTIndex” to FALSE (except for the clustered index; this could be done with a few lines of C/AL code, modifying table 2000000067 “Key” or manually just for the most important/large tables)
  3. Native DB: Create the native Backup (FBK)
  4. SQL DB: Restore the FBK (maybe not all companies at a time) - only the table objects have to be created but no indexes and SIFT/VSIFT
  5. SQL DB: Import the FOB from Step 1; maybe large tables alone (one by one) - this will create the indexes/SIFT/VSIFT; but after each import-batch you could commit, thus reducing the load on the system

Hope this helps a little.

Regards,
Jörg

6

Hi. Tried this on a test server. With approximately 400 companies this solution decreased the restore time with 200%. I have made a restore of 2 additional companies, and with hotfix for MS this is done in minutes (with a standard 5.01 client this takes 6-7 hours!)