SQL Optimization

megawavez · 2005-02-01

Hi,

We are looking to move to the Microsoft SQL backend. Our initial test seems to indicate that it's slower, and in some instances, quite a bit slower, than the proprietary server.

What has been people's experience with this?
Can anybody specify any specific server level optimizations they've done to speed things up?

Thanks!

Mega

Ian_piddignton · 2005-02-01

Hi

A number of things can impact on the performance of Navbision on SQL.

How do you have your SQL server set up? whats teh Specs of the machine, how are your drives set up, what RAID set are you using etc etc .

Inside navision you need to examine the use of SIFT fields. For Navision on SQL the SIFT information is held in tables and everytime you write or delete a record it is possible that a number of SIFT tables also need to be amended. This can be tuned to get teh best performance.

Are the areas you are seeing considerable differences custom? It maybe that the code that has been written isn't particularly good when it comes to SQL. In a number of ways the Native database is very forgiving of the way code is written, while SQL is not.

You need to sit down with the developers/implementors and look at these various issues. It is often not as simple as takeing a copy of the database and moving it to the SQL back-end.

regards

Ian

megawavez · 2005-02-02

Hi,

Currently working on the SIFT fields - any idea how much they affect performance?

We have a 2.8 ghz dual cpu machine running vmware. Our SQL license is only 1 cpu.

We have a one 36gb scuzzi drive for the database. The navision database is about 11 gb.

It's definitely slower than the proprietary backend - some operations can take 4-5 times as long (not navision ops, our own propriertary code).

We not familiar with microsoft SQL - any ideas what the recommended specs are for a navision server?

g_dreyer · 2005-02-02

Proprietary database has always been faster than SQL, since you use different database servers.

The theory is that you can have 1 or 1000 users on SQL, without the server slowing down, but on proprietary you will see a decline in performance as you increase the number of users.

Regards,
gus

megawavez · 2005-02-02

g_dreyer wrote:

The theory is that you can have 1 or 1000 users on SQL, without the server slowing down, but on proprietary you will see a decline in performance as you increase the number of users.

gus

Does SQL, particularly MS SQL show this type of rampup in actually use? We'll see the same performance with one person vs. 30?

Ian_piddignton · 2005-02-02

Proprietary database has always been faster than SQL, since you use different database servers.

The theory is that you can have 1 or 1000 users on SQL, without the server slowing down, but on proprietary you will see a decline in performance as you increase the number of users.

Regards,
gus

Proprietary is faster on small system depending on what is being done. I have seen small SQL installations out perform proprietary ones. There have been some recent performance tests done as well that compare the 2 systems and show interesting results.

Currently working on the SIFT fields - any idea how much they affect performance?

We have a 2.8 ghz dual cpu machine running vmware. Our SQL license is only 1 cpu.

We have a one 36gb scuzzi drive for the database. The navision database is about 11 gb.

It's definitely slower than the proprietary backend - some operations can take 4-5 times as long (not navision ops, our own proprietary code).

We not familiar with Microsoft SQL - any ideas what the recommended specs are for a navision server?

The Sift tables can have a great effect on performance. Espically in relation to tables that have lots of writes and deletes on them, like sales orders.

Microsoft would say that the main datafile should be on its own set of spindles and the log file should be on its own set of spindles, so you are looking at at least 3 drive arrays, one for the O?S and SQL server files, One for you dataset and one for log files. If you are using RAID go for 0,1 or 10 generally with a database like Navision you want to Avoid RAID5

Depending on how many users you have concurrently accessing the server should be at least twin processor.

There aer some sizing guides for SQL generally and it would be well worth looking at them or getting access to a SQL DBA that can advise on the correct configuration etc

If your code is taking longer it maybe that it is not allowing SQL to use its full power.

An example is the way in which the 2 backend's get records from the database. Proprietary gets one record at a time, SQL is able to get blocks of records.
What SQL is actually getting a page of data, ask for 1 record SQL gets the page and gives you the requied record, as for the very next record and SQL gets teh page and gives you the record. If your code is bad this will happen, if your code is SQL friendly Navision figures out you want 2 records next to each other and asks for them both at the same time, SQL then only has to get the page once instead of twice.
So it is possible to write some perfectly acceptable code for the proprietary database that will casue performance issues in SQL.

I am not a developer but I think there are guidelines and examples in the developers guides about writting code for SQL to allow it to do this.
regards

Ian

megawavez · 2005-02-02

Hi and thanks for all the feedback - couple more questions if people have the time:

1) Can anybody point me in the direction of where to find developer notes on optimizing code for SQL?

2) Can anybody gives the specs on their SQL server, their user count and general usage?

Thanks again!

Chris

kine · 2005-02-03

For example: one computer comfiguration for MSS QL with DB<90GB and <100 user.

2x XEON 3.4GHz
3GB RAM

12x18GB HDD RAID0+1 (DB)
1x18GB HDD Hotspare

4x73GB HDD RAID0+1 (Log)
2x146GB HDD RAID 1 (Backup)
1x73GB Hotspare

(Tape backup etc.)

From out experiences, the HDDs are main part of the performance of whole system. You need many HDDs to have enough power to serve all request for data transfers. On system with 4 disks it is all too slow...

Waldo · 2005-02-03

I didn't read everything ... so pardon me if I'm repeating something ...

Indeed, the overall performance of the native DB is better than the SQL Server. BUT:

When you're working with many users (+25), you'll find the benefits of using SQL Server:

- No table locking
- better simultanious user handling
- ...

A big advandage of SQL Server is that it CAN use bigger hardware:
- it CAN use multiple processers while a native db cannot.
- it CAN use more than 1 GB RAM while a native db cannot.

This means, when using more powerful hardware ... a SQL Server can be much more performant than a native DB.

And something else: never use RAID5 !! (in both cases!)

thu · 2005-02-03

megawavez wrote:

1) Can anybody point me in the direction of where to find developer notes on optimizing code for SQL?

Hi Chris,

the pdf to the Upgrade Toolkit should have a chapter on migrating "from C/SIDE to the SQL Server option". This points you at the essential things to do and not to do. Also, the Tools CD of the new 4.0 version has a directory "SQL Server Ressource Kit" (under "Implementation") which could be helpful.

Regards,

Thomas

megawavez · 2005-02-03

Thanks for the feedback. We've done a little more testing and strangely enough, it looks like we're hitting a network bandwidth problem. We're getting packet trasactions in the range of 1700 - 2400 per/second.

Does anybody have experience with navision front end caching. I think what's happening is that it's asking for one record at a time instead of a block of records which SQL is probably much better at handling. If we can somehow get the client to ask for a block of records and cache them locally, that would probably help enourmously.

Thanks,

Chris

kine · 2005-02-04

in some cases Navision is using TOP 1 statement (take only one record). If you check your code with Performance toolkit, you can use the form Client Monitor (Cache Usage) to see, where is the cache wrongly used. Navision client is caching hardly if you have enough RAM...

megawavez · 2005-02-04

kine wrote:

in some cases Navision is using TOP 1 statement (take only one record). If you check your code with Performance toolkit, you can use the form Client Monitor (Cache Usage) to see, where is the cache wrongly used. Navision client is caching hardly if you have enough RAM...

Where do I find the performance monitor / client usage? Thanks,

Chrsi

kine · 2005-02-04

The objects are part of Performance Troubleshooting Guide which is on MBS Tools CD (you can download it from partnerguide or somewhere). It is in directory Implementation. If you want I can send it to you... but you need proper licence for it - developer licence because objects are in 150000 part of numbers and you need client monitor.

megawavez · 2005-02-04

kine wrote:

The objects are part of Performance Troubleshooting Guide which is on MBS Tools CD (you can download it from partnerguide or somewhere). It is in directory Implementation. If you want I can send it to you... but you need proper licence for it - developer licence because objects are in 150000 part of numbers and you need client monitor.

Found it, thanks!

Howdy, Stranger!

Quick Links

Categories

SQL Optimization

Comments