When is SQL Performance Tuning Needed?

Alex_Chow

Assuming the database is native 5.0 with Lanham E-Ship/EDI addon implemented.

If there are no custom modifications to the Navision table keys, will we ever need to tune the database?

If so, why doesn't Microsoft release a "pre-tuned" database?

davmac1

Every installation uses Navision differently.
This means the key usages will vary plus the data per table.

As the database grows, the statistics have to be updated so SQL Server knows the most efficient way to execute queries.

This is why demo databases usually run so well - very little data and typically one user. Then the real world collides and everything changes.

Just like the highways in LA - all of them are fast with no traffic. When the traffic builds up, they need to be tuned. Since that does not happen, you get gridlock in certain areas.

Alex_Chow

So can it be assumed that SQL tuning is just analyzing the keys and indexes? So there's no "programming" involved?

stryk

Well, "Tuning" actually involves several aspects.

First, when you install a NAV database, no kind of periodic maintenace is included. To keep the database(s) performing good, you need to take care about sufficient statistics, re.indexing, backups, etc..

Further, it is necessary to configure the SQL Server and the database properly, especially to grant the optimal usage of resources (CPU, RAM, Disks, etc.).

The next step is to compensate some "out-of-the-box-product-insufficiencies": Index-, SIFT- and Query-Tuning, Block/Deadlock Detection, etc.. This, as previously mentioned, depends on the system and how you use it ...

Hence, IMO "tuning" includes common and specific aspects. The common stuff should be implemented anyway, the specific stuff is to get rid of occurring problems ...

Alex_Chow

If the common stuff should be implemented anyway, why doesn't MSFT release a database with all the common stuff built in? (I think I know the answer already, but what the heck )

DenSter

The 5.0 standard database has been pretuned already (by the same person that developed the SQL Perform tools by the way), so there are already big improvements there over the 4.0 standard database.

You'd have to verify with Lanham if they have done any SQL tuning. The last time I spoke to them (convergence 2007) they maintained a single code base for all versions going back to 3.6, so it was not possible to implement the new SQL Server keywords for their products. Unless they have changed that policy since then, it might be beneficial to do a code review for the most commonly used processes for your specific implementation.

Like Jorg said, you MUST set up proper maintenance on SQL Server, to prevent performance degradation. Key tuning can be done manually but can be done much more quickly by using the proper tools. Then I would save code review for last, and only if index tuning has not given you the required performance improvements. You could do some 'preventative code review' but it would be difficult to determine the scope, and how much time to include for this in the budget.

Alex_Chow

So does this mean that someone who doesn't have NAV experience but have SQL performance tuning experience can go in and enhance performance for NAV on SQL?

David_Singleton

Alex Chow wrote:

So does this mean that someone who doesn't have NAV experience but have SQL performance tuning experience can go in and enhance performance for NAV on SQL?

No.

basically that's like getting a quantum physicist to fix that banging noise under the hood of your car.

DenSter

LOL

It is possible for a SQL Server only person to tweak performance, but that would only address SQL Server specific things. You would not be able to address SIFT, and modifications to indexes would not be supported in the NAV table designer. The biggest gains in performance of NAV database on SQL Server are tuning the indexes and SIFT from within the NAV table designer. Going the NAV route is much more efficient than only looking at SQL Server.

Alex_Chow

David Singleton wrote:

Alex Chow wrote:

So does this mean that someone who doesn't have NAV experience but have SQL performance tuning experience can go in and enhance performance for NAV on SQL?

No.

basically that's like getting a quantum physicist to fix that banging noise under the hood of your car.

In this example, who would be the quantum physicist? The NAV person or the SQL person?

davmac1

I beleive we have our quantum physicist in the forum now and he is a little frustrated with us Navision yahoos.

David_Singleton

Alex Chow wrote:

In this example, who would be the quantum physicist? The NAV person or the SQL person?

The SQL person of course.

The thing about quantum physics other than the obvious things (like poisoning cats in black boxes) is that quantum physics is not about actually understanding the problem, is about using statistics at a quantum level to get an idea of what might be happening. When you add up all the averages, you get a picture of whats going on at a global level.

Well much easier is to go to a garage, find a mechanic that knows your model car, and knows immediately what the problem is and fixes it.

Now of course understanding how Iron and Carbon atoms bind at a quantum level is very usefull, and when you are racing an F1, you need both the Mechanic and the Physicist, but you still need to start looking at the real issue, i.e. the mechanics of making the car run.

David_Singleton

By the way Alex, your cat is too big to fit in one of those boxes, so you should be fine

Alex_Chow

David Singleton wrote:

By the way Alex, your cat is too big to fit in one of those boxes, so you should be fine

LOL!!!!

colin_leversuch-roberts

SQL Server out of the box does in fact contain a number of self tuning features which will suffice for many applications and databases; wizards are provided for creating simple maint plans, you'll need to visit these wizards if you want to back up your database so ticking a few boxes to update stats and rebuild indexes shouldn't prove too difficult.
If you have the window update stats and rebuild indexes every night, failing that pick a suitable quiet time. If your app is busy 24 x 7 then if you're using enterprise edition these tasks can be run aschronously to not interfere with the sytem. Server settings should be left "out of the box" it's very rare to have to make any changes to them. You should disable Hyperthreading at bios level if you have it.
On the whole SQL Server is pretty good at tuning itself - the SQL Server 2000 performance tuning manual by microsoft press is a good introduction to sql server tuning.
You should also apply a few basics such as NTFS defrag on a regular basis.

Per

E-Ship/EDI is optimized in the later versions (since 2005) to run on both SQL and Native Database. Actually some of the tuning done for SQL ended up benefitting more for the Native Server than SQL.

A couple of Key-Groups has been added to disable keys that are only used for some parts of the applications.

Marije_Brummel

5.0 is better equiped to handle SQL Server, that's a fact. Our dear friend Hynek has put himself out of business by tuning the base product AND helping Per.

Just kidding. It is partly right. Tuning is different for everyone. Like the traffic example, the higways are used differently in the morning, evening, weekend and even in vacation period. Do you want to have perfect trafic in a vacation period? Is it possible to have no jams if there is an accident?

You can translate an accident to having and enduser entering a wrong (non indexed) filter. It is unavoidable and happens. But TRAINING will help you.

If you have a normal implementation, just go ahead using the correct hardware. I always recommend using Hynek's maintenanceplan. It is so easy to setup in a few seconds and perfect for Navision.

For bigger customers, larger databases, just make sure to put your clients expectations in the right perspective and make sure to have budget for tuning.