Bad performance / unable to restore

vpta

Hi – I’m not sure if this is the correct forum, but I have a serious problem restoring a 5.0 NAV database on SQL. It is just a small db, but it contains 510 companies. When I do the restore on a native server it takes app. 2 hours. Now I have tried restoring this several times on SQL, but after 30 -35 hours the process times out.

I am aware of a limitation in the 32-bits SQL, but I don’t think this is the problem. I have restored Navision db on this server >150gb.

When I start the restore, the speed is decreasing rapidly. The first 10 companies are restored in just minutes, the next 10 takes 3 times…and so on…

My settings:

- NAV 5.0 SP1 client
- NAV 5.0 db (backup 2 gb – 510 companies – native base is 4,5gb)
- Server – 2xdual CPU 3,0 ghz / 24 gb memory (yes PAE is enabled with AWE) / Discrack 24 disc – RAID 10e (IBM specific)

bbrown

Where is the transaction log? Do the data and transaction log files has sufficient free space to not expand during the restore?

pdj

This is just a wild guess, but NAV5SP1 tend to re-create all indexed views each time a table is modified. (See KB957733)
If the restore process also trigger this process for each company it will be slower and slower. This will be possible to determine using the Profiler while restoring 2-3 companies.
You could also try restoring in steps of only 10-15 companies. If my guess above is correct it wont help and you will need to request the hotfix for it.

Please let us know what you discover :?

bbrown

Yes, SP1 (and higher) would need to rebuild the indexed views. But I would not expect a huge difference between building 10 views for 10,000 records each or 1 view for 100,000 records. Not sure on the impact of 510 companies. The suggestion to restore a few companies at a time may be helpful. Might experiment a bit. Maybe there's a point in the number of companies where the performance just drops way off.

bbrown

BTW - That hotfix won't help this situation. It is only applicable to modifying a table already in SQL where the indexed views already exist. In this situation the indexed views don't yet exist and must be created.

Slawek_Guzek

510 companies * 1000 (more less) tables = 510 000 tables. Each one is an entry (record) in master..systables
510 companies * 300 (more less) SIFT views = 153 000 views. Each one is an entry (record) in master..systables
510 companies * 20 000 (more-less) fields = 10 200 000 fields. Each one is an entry (record) in master..syscolumns

I would rather ask where master database is placed? Standard location c:\Program Files\Microsoft SQL Server\..... ?

Having such a big numbers of objects to manage might cause serious problems with master database performance. Under normal circumstances no one cares a about master database placement, but here I would rather move it to separate RAID1+0 array.

Regards,
Slawek

vpta

An update :

1) I have tried restoring in bulks (10-40 companies) no luck
2) The translog file is placed on a separate disc on a separate controller.
3) I have initiated very large database, so there should be no reason for increasing on the fly.
4) This is an accountant company who own this database. It is almost just general ledger entries. The main problem seems to be the amount of tables/views.
5) I have restored 100 companies – this takes app. 4 hours from this database. (total size of restored data is 700 mb) This is the same amount of time I use for restoring one of my other db on the same server. The only difference is that this db has 30 companies and 125 gb of data!
6) I have not tried to move the master table. This table is on the same drive that I have my logfile on. Perhaps this can help. The mayor problem is not the amount of time, is more that I am not able to restore at all..

bbrown

vpta wrote:

An update :

6) I have not tried to move the master table. This table is on the same drive that I have my logfile on. Perhaps this can help. The mayor problem is not the amount of time, is more that I am not able to restore at all..

The transaction log needs not only be separate from the data drive, but needs to be on its own dedicated disk array. This is to keep drive heads in constant position for writing. An improperly postitioned log file can be one of the single largest impacts on SQL write performance.

A couple years ago, I was doing a test upgrade of a NAV database. On my first test both the data and log were on the same array (RAID 10) and the conversion took about 17 hours. For my next test, I move the log to its own dedicated RAID 1 array and the conversion took under 6 hours.

vpta

bbrown : my data and my logfile is on seperat disc and arrays. It is my logfile and my master.db that is on the same disc and array.

vpta

Correct me if I´m wrong, but now I´ve tried to restore 5 backups with 40 companies in each. Every restore in a different db but performed on the same time – this takes about 20 min. This should write the same amount of data to the mastertable – or?

bbrown

Here's an idea to try. It's a bit of a "shot in the dark" but the idea comes from some basic "NAV upgrading 101" concept.

First, in the original native database, export the "GL Entry" table object. Next remove all SumIndex fields from the "GL Entry" table and export again as a different file. Then remove all secondary keys from the table. I mention only the "GL entry" table as you indicate this is where much of the data is.

Next make a new backup.

Restore new backup to SQL.

Import the second FOB you made. (the only without the SumIndexes)

Import the first fob. (the one with the SumIndexes)

Slawek_Guzek

vpta wrote:

2) The translog file is placed on a separate disc on a separate controller.
3) I have initiated very large database, so there should be no reason for increasing on the fly.

That's good. However you should also initiate big enough log file (try 2 times estimated DB size if you plan to restore all at once)

Don't forget to increase initial size of master db file and master db log file. master db use simple recovery model however restoring backup is one transaction, which will be quite big in your case. You would not have to increase master db log file so much if you restore database in batches.

vpta wrote:

4) This is an accountant company who own this database. It is almost just general ledger entries. The main problem seems to be the amount of tables/views.

That's why you should increase initial size of master db files, and separate physical location of master db files from its log files.

vpta wrote:

5) I have restored 100 companies – this takes app. 4 hours from this database. (total size of restored data is 700 mb) This is the same amount of time I use for restoring one of my other db on the same server. The only difference is that this db has 30 companies and 125 gb of data!
6) I have not tried to move the master table. This table is on the same drive that I have my logfile on. Perhaps this can help. The mayor problem is not the amount of time, is more that I am not able to restore at all..

Perhaps you'll getting timeouts due to increasing master db on the fly ? Or you simply don't have enough space for it ?

Regards,
Slawek

bbrown

vpta wrote:

bbrown : my data and my logfile is on seperat disc and arrays. It is my logfile and my master.db that is on the same disc and array.

The key work is "dedicated". That means absolutely nothing else is ever placed on that drive. Not even temporarily. The transaction log should be the only file on that drive. Also, just to be clear, when I say drive I am referring to a physical drive (or drives), not to a partion on a shared drive.

bbrown

BTW - System objects, such as sys.objects, are not stored Master. They are stored in mssqlsystemresource.

vpta

OK – now I have deleted keys (doesn´t seems to improve performance very much). The time consume is not when creating keys, but when the company is created and data shall be posted from backup to db. It can take 10 min just to write 1 kb.

As you can see on the table below – there are not many records…

17 Total 1608573
21 Total 122754
25 Total 102262
32 Total 13694
45 Total 22171
46 Total 9841
112 Total 16485
113 Total 46849
169 Total 48155
203 Total 31244
225 Total 2338124
241 Total 6768
254 Total 214464
298 Total 6218
300 Total 2047
355 Total 291182
359 Total 19790
365 Total 105903
379 Total 282238
380 Total 293777
1003 Total 48155
5086 Total 2722
5118 Total 5623
5802 Total 13694
5811 Total 13694

vpta

I have now tried to move the master db to another array – without any luck. The master table is only 5 mb. When I do some lookups on my SQL there is mainly disc i/o on the database that I´m restoring. But the PID is very much in AWAITING COMMAND stat…

Slawek_Guzek

bbrown wrote:

BTW - System objects, such as sys.objects, are not stored Master. They are stored in mssqlsystemresource.

Point taken

However my point is that in this scenario system databases grows significantly more than user databases, and vpta should focus more on system databases than on user databases (syscolumns table for example will be almost 10 times bigger than sum of G/L Entry tables in all user tables), and apply to them some common optimisation rules and techniques (like separating log files from data files, adding some indexes perhaps)

vpta wrote:

The master table is only 5 mb

That's because as bbrown pointed all object goes to mssqlsystemresource. You should also move mssqlsystemresource database. This databases is not normally visible from SSMS, so check it's size directly on disk.

Regards,
Slawek

vpta

Hi Slawek
Thanks for your suggestions – the problem as I have tried to describe, is not keys and user data – it is regarding all companies. I have now just made a db with 500 empty companies (just setup info) – and I actually am not able to restore this…. Are there anyone who have converted this amount of companises from navtive to SQL on a 32 bit 2005 plattform?

David_Singleton

vpta wrote:

bbrown : my data and my logfile is on seperat disc and arrays. It is my logfile and my master.db that is on the same disc and array.

Which part of "The LOG file must be on its own dedicated spindles" do you not understand?

Delete EVERY file of the drive except the LOG file, and make sure it is a real drive with real spindles, and not something virtual.

pdj

vpta wrote:

and I actually am not able to restore this….

Do you get any kind of error, or does it simply freeze?
If it fails: What error do you get? (Either on screen, or anything in the event-log)
If it freezes: What does "dbcc inputbuffer(<session ID of the restoring client>)" return? (And what if you try it several times, just to check for any change)

Slawek_Guzek

vpta wrote:

Hi Slawek
Thanks for your suggestions – the problem as I have tried to describe, is not keys and user data – it is regarding all companies. I have now just made a db with 500 empty companies (just setup info) – and I actually am not able to restore this…. Are there anyone who have converted this amount of companises from navtive to SQL on a 32 bit 2005 plattform?

You are not able to restore this on the same server ? Or on different ? Can you create 500 empty companies on destination server ?

vpta

David Singleton wrote:

vpta wrote:

bbrown : my data and my logfile is on seperat disc and arrays. It is my logfile and my master.db that is on the same disc and array.

Which part of "The LOG file must be on its own dedicated spindles" do you not understand?

Delete EVERY file of the drive except the LOG file, and make sure it is a real drive with real spindles, and not something virtual.

HAVE tried to put this on separate discs. I have 2 x fiberchannel adapters in the server and have 4 arrays (2 controllers x 2 rackcabinets). Anyway how I configure this, the server stops responding

Slawek_Guzek

vpta wrote:

... Anyway how I configure this, the server stops responding

Did server stop responding to NAV session only or it generally freezes ? Did you check SQL server error log, CPU usage, etc ?

Have you tried to trace NAV session with SQL Profiler ?

bbrown

Slawek Guzek wrote:

bbrown wrote:

BTW - System objects, such as sys.objects, are not stored Master. They are stored in mssqlsystemresource.

Point taken

However my point is that in this scenario system databases grows significantly more than user databases, and vpta should focus more on system databases than on user databases (syscolumns table for example will be almost 10 times bigger than sum of G/L Entry tables in all user tables), and apply to them some common optimisation rules and techniques (like separating log files from data files, adding some indexes perhaps)

vpta wrote:

The master table is only 5 mb

That's because as bbrown pointed all object goes to mssqlsystemresource. You should also move mssqlsystemresource database. This databases is not normally visible from SSMS, so check it's size directly on disk.

Regards,
Slawek

I think you may be over-estimating the growth of the system databases. For comparision, I checked one of our development servers. It currently host 91 NAV databases and its mssqlsystemresource database is only 38 MB.

My typical practice is to install the system databases, along with primary NAV data (*.mdf) files, to their own RAID 1 array. Depending on factors, such as system size and disk availability, I may relocate temp DB to it's own array.

bbrown

vpta wrote:

HAVE tried to put this on separate discs. I have 2 x fiberchannel adapters in the server and have 4 arrays (2 controllers x 2 rackcabinets). Anyway how I configure this, the server stops responding

Another piece of information comes to light.

You're apparently running on a SAN. Are you sure you have dedicated physical disk for NAV including having data and log on separate physical disk? Or are your data and log "drives" just separate LUNs on the same SAN array?

vpta

bbrown wrote:

vpta wrote:

HAVE tried to put this on separate discs. I have 2 x fiberchannel adapters in the server and have 4 arrays (2 controllers x 2 rackcabinets). Anyway how I configure this, the server stops responding

Another piece of information comes to light.

You're apparently running on a SAN. Are you sure you have dedicated physical disk for NAV including having data and log on separate physical disk? Or are your data and log "drives" just separate LUNs on the same SAN array?

Hi - nopp no san, direct attached SAS via fiber.

bbrown

vpta wrote:

bbrown wrote:

vpta wrote:

HAVE tried to put this on separate discs. I have 2 x fiberchannel adapters in the server and have 4 arrays (2 controllers x 2 rackcabinets). Anyway how I configure this, the server stops responding

Another piece of information comes to light.

You're apparently running on a SAN. Are you sure you have dedicated physical disk for NAV including having data and log on separate physical disk? Or are your data and log "drives" just separate LUNs on the same SAN array?

Hi - nopp no san, direct attached SAS via fiber.

OK. Just checking.

bbrown

Does your RAID 10e array have an even or odd number of drives?

bbrown

Ignore my last question. If it were an odd number you would have referred to it as RAID 1E.

vpta

Slawek Guzek wrote:

vpta wrote:

... Anyway how I configure this, the server stops responding

Did server stop responding to NAV session only or it generally freezes ? Did you check SQL server error log, CPU usage, etc ?

Have you tried to trace NAV session with SQL Profiler ?

The client freeze for a time, then it return a general "out of memory" msg (the message is different from restore to restore). The SQL is performing a ROLL-BACK.

vpta

Slawek Guzek wrote:

vpta wrote:

... Anyway how I configure this, the server stops responding

Did server stop responding to NAV session only or it generally freezes ? Did you check SQL server error log, CPU usage, etc ?

Have you tried to trace NAV session with SQL Profiler ?

No - what would this give me of information?