Bad performance / unable to restore

vpta

Slawek Guzek wrote:

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 ?

I have tried to restore this on another SQL server - still get timeout. If i make a db with 500 companies on the SQL server, then everything is ok (as long as I don´t need to retore a Navision backup)

vpta

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

The mssqlsystemresource database is only 39 mb.

Slawek_Guzek

bbrown wrote:

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

Slawek Guzek wrote:

Point taken

Actually I've checked details of resource database (which I didn't do before). You are wrong:

http://msdn.microsoft.com/en-us/library/ms190940.aspx

"..The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata...

bbrown wrote:

Slawek Guzek wrote:

However my point is that in this scenario system databases grows significantly more than user databases

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.

Each of standard NAV database has around 20000 fields in 1000 tables. Having 500 companies in ONE database gives 10 million of field definitions. Each row in sys.syscolumns view consists of around 25 integer columns (haven't checked). This information alone gives around 1 GB of metadata.

1 GB doesn't seem to be very big number, but having 10 million records in one 1 GB table might cause troubles managing it, don't you agree ?

bbrown

Put the database in "Single User" mode before beginning the restore. "Single User" mode does not issue locks, therefore does not use lock memory. I've seen this with very large databases but you may be expereincing similar behavior due to the large number of companies.

Slawek_Guzek

vpta wrote:

I have tried to restore this on another SQL server - still get timeout. If i make a db with 500 companies on the SQL server, then everything is ok (as long as I don´t need to retore a Navision backup)

Well, the only difference I can see between creating 500 companies manually and restoring it from backup is that when you're creating company one by one each company is created in one separate transaction. So changes made to system databases what needs to be logged are relatively small.

When you're trying to restore backup having 500 companies everything goes as one big transaction, so even if system databases are using simple recovery all changes needs to go to transaction log, exploding it effectively. After transaction is finished the log entries become reusable, so logical log size will be small, but physical size remains.

Maybe you just don't have enough space for log files of system databases ? Please check the size of your master.ldf on disk after creating one company - do you have enough space for 500 times bigger file ?

BTW - what is size of master database on the server where you've created 500 companies manually ?

Regards,
Slawek

bbrown

Slawek Guzek wrote:

bbrown wrote:

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

Slawek Guzek wrote:

Point taken

Actually I've checked details of resource database (which I didn't do before). You are wrong:

http://msdn.microsoft.com/en-us/library/ms190940.aspx

"..The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata...

bbrown wrote:

Slawek Guzek wrote:

However my point is that in this scenario system databases grows significantly more than user databases

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.

Each of standard NAV database has around 20000 fields in 1000 tables. Having 500 companies in ONE database gives 10 million of field definitions. Each row in sys.syscolumns view consists of around 25 integer columns (haven't checked). This information alone gives around 1 GB of metadata.

1 GB doesn't seem to be very big number, but having 10 million records in one 1 GB table might cause troubles managing it, don't you agree ?

Whether you have 500 companies in 1 database, or 500 databases with 1 company each, the number of entries in sys.syscolumns is going to be the same. So if I have a real-world example of 91 databases (probably around 150 companies) resulting in a 4 MB master DB and a 38 MB mssqlsystemresource, I don't see how you arrive at an estimated 1 GB for 500 companies.

vpta

Slawek Guzek wrote:

vpta wrote:

I have tried to restore this on another SQL server - still get timeout. If i make a db with 500 companies on the SQL server, then everything is ok (as long as I don´t need to retore a Navision backup)

Well, the only difference I can see between creating 500 companies manually and restoring it from backup is that when you're creating company one by one each company is created in one separate transaction. So changes made to system databases what needs to be logged are relatively small.

When you're trying to restore backup having 500 companies everything goes as one big transaction, so even if system databases are using simple recovery all changes needs to go to transaction log, exploding it effectively. After transaction is finished the log entries become reusable, so logical log size will be small, but physical size remains.

Maybe you just don't have enough space for log files of system databases ? Please check the size of your master.ldf on disk after creating one company - do you have enough space for 500 times bigger file ?

BTW - what is size of master database on the server where you've created 500 companies manually ?

Regards,
Slawek

I have plenty of space on my rack (> 400 gb free on all arrays). After creating 500 companies the master database is 8 mb.

Slawek_Guzek

vpta wrote:

The master table is only 5 mb

Slawek Guzek wrote:

That's because as bbrown pointed all object goes to mssqlsystemresource. You should also move mssqlsystemresource database.

bbrown was wrong and I was wrong. mssqlsystemresource is read-only database and remains constant in each MSSQL instalation, only service pack installation can make change to it (by overwriting database file with new version)

Anyway - 5MB of master db it was before you've tired to restore backup ?

Slawek_Guzek

vpta wrote:

...After creating 500 companies the master database is 8 mb.

OK now I'm lost I need to do some test on my SQL Server

Slawek_Guzek

bbrown wrote:

Whether you have 500 companies in 1 database, or 500 databases with 1 company each, the number of entries in sys.syscolumns is going to be the same. So if I have a real-world example of 91 databases (probably around 150 companies) resulting in a 4 MB master DB and a 38 MB mssqlsystemresource, I don't see how you arrive at an estimated 1 GB for 500 companies.

True. One database or 100 doesn't make any difference when it comes to field count.

My estimation was:
1 company = 1000 tables and 20000 fields (roughly)
500 companies : 500 * 20000 = 10 000 000 fields
each row in syscolumns has around 25 fields, most of them looks like integers - 25 * 4 = 100 bytes
10 000 000 fields * 100 bytes metadata = 1 000 000 000 bytes of metadata = 1 GB

Number of columns in sys.syscolumn is just of the top of my head, so size of one recod in this view too. But I cannot be VERY far from correct numbers on this

bbrown

Slawek Guzek wrote:

bbrown wrote:

Whether you have 500 companies in 1 database, or 500 databases with 1 company each, the number of entries in sys.syscolumns is going to be the same. So if I have a real-world example of 91 databases (probably around 150 companies) resulting in a 4 MB master DB and a 38 MB mssqlsystemresource, I don't see how you arrive at an estimated 1 GB for 500 companies.

True. One database or 100 doesn't make any difference when it comes to field count.

My estimation was:
1 company = 1000 tables and 20000 fields (roughly)
500 companies : 500 * 20000 = 10 000 000 fields
each row in syscolumns has around 25 fields, most of them looks like integers - 25 * 4 = 100 bytes
10 000 000 fields * 100 bytes metadata = 1 000 000 000 bytes of metadata = 1 GB

Number of columns in sys.syscolumn is just of the top of my head, so size of one recod in this view too. But I cannot be VERY far from correct numbers on this

So how do you explain my "real world" numbers above? Based on your example calculation my master database should be ~200 MB.

Slawek_Guzek

bbrown wrote:

So how do you explain my "real world" numbers above? Based on your example calculation my master database should be ~200 MB.

I can't. That's why I said I'm lost and I need to get my hands on my SQL Server box and do some tests.

bbrown

vpta wrote:

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.

Regardless of whether the master database is 50 MB or 1 GB, I don't think that is the issue. I think he is running out of memory due to the number of locks being issues. See my response about single-user mode.

Remember that a NAV restore is really a bunch of "create table" statements and a massive data import. This requires many locks to be issued. Having 500 companies compounds this situation. I believe he mentioned that he's using 32 bit SQL. This put a further limit on the available lock memory. When running in single-user mode, the system does not issue locks. It doesn't need to since no one else will be accessing the tables.

Slawek_Guzek

vpta wrote:

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.

:oops: #-o #-o I've missed that response #-o :oops:

bbrown wrote:

Regardless of whether the master database is 50 MB or 1 GB, I don't think that is the issue. I think he is running out of memory due to the number of locks being issues. See my response about single-user mode.

I was more concerned about number of records in metadata, not it's size itself.

bbrown wrote:

Remember that a NAV restore is really a bunch of "create table" statements and a massive data import. This requires many locks to be issued. Having 500 companies compounds this situation. I believe he mentioned that he's using 32 bit SQL. This put a further limit on the available lock memory. When running in single-user mode, the system does not issue locks. It doesn't need to since no one else will be accessing the tables.

Right. SQL "out of memory" error changes my point of interest considerably.. However restoring backup in batches of 10 - 50 companies *should* work if it is problem with lock memory. Restoring 10 companies at a time is not pushing SQL server to it's limits...

vpta also mentioned use of PAE and AWE. I remember very roughly some articles describing problem with PAE memory allocation using low memory for mapping, reducing overall memory available to applications. It was OS related. And as I remember 32 bit SQL can use AWE memory only for page buffer...

Still I need to do some test and find where I am wrong with my estimations (what I do not know...)

bbrown

Slawek Guzek wrote:

... However restoring backup in batches of 10 - 50 companies *should* work if it is problem with lock memory. Restoring 10 companies at a time is not pushing SQL server to it's limits...

I have seen this occur even with a single company under 32 bit SQL.

vpta

bbrown wrote:

Put the database in "Single User" mode before beginning the restore. "Single User" mode does not issue locks, therefore does not use lock memory. I've seen this with very large databases but you may be expereincing similar behavior due to the large number of companies.

Tried it - no difference

Slawek_Guzek

Well, let's try stupid soultions then.

Estimate the biggest batch which can be restored as first. Then try to restart mssqlservice proces (or whatever it is called if it is named instance) after each batch.

At least we *should* have clear situation with memory issues.

BTW - Before you try restarting server service between restores - can you try to limit SQL memory to 1.5 GB for example and turn off AWE ?

Can you post here latest SQL errorlog after restore crashes ?

Regards,

vpta

SOLVED!

As we have been discussing – the problem is in the finsql.exe file. I just received this hotfix with new exe files (date 13.07.09) and it worked!!

Thanks for all the help. =D>

---

Article ID : 969125
Last Review : N/A
Revision : 2.0


This problem occurs because all table data of existing companies in the database is duplicated before the restore process of any new company starts. After the new company is restored, all the duplicate data is deleted. If you have many companies in the target database or some companies in the target database contain much data, this problem occurs.

https://mbs.microsoft.com/knowledgebase ... upqkrwusyl

pdj

Great! =D>
This hotfix was already described here. That is the first time I found more information about Hotfixes from Microsoft than on Waldo's site. Maybe Microsoft finally started taking this kind of information seriously... (Well, it is not even from Microsoft HQ, but "just" from the Baltic region office. Glad that atleast some MS employees are trying to help us proactively.)

kriki

There is a little other trick you might consider.
Now you have 500 companies having each about 1000 tables. That means 500,000 tables! SQL has a hard time to manage those.

Considering you are using very little functionality (and thus tables), you can put the table-property DataPerCompany to NO and thus reducing by 499 tables each table you change. This trick I learned from Mark Brummel.

pdj

:-k I see the problem in using SSMS with all these tables, but the risk of the customer or another NAV consultant getting very strange and maybe even fatal errors would stop me from doing it. Imagine that in 2 years time the customer consider using a granule they aren't using today. Then their consultant might copy one of their Companies to a TestCompany in the same database and try setting it up and using it. This could influence posting in the other companies and in worst case result in inconsistant posting tables.
I don't have a different solution to the problem, I just wouldn't do it...

kriki

Of course you need to be sure those tables won't be used in the future.
A way could be to put an error in the insert/modify/delete-triggers.

bernatrm

solved