SQL Database fine tuning

rjverma

Hi,

We are using Nav4.0 SP#3, with SQL database. we are Electronics Manufacturing Co., Inventory transaction is very huge. In our Item master there is 12k to 15k item, BOM is approx 4k and each BOM has approx 400 lines, we have multiple Prod. Floor.

We are using Manufacturing, Inventory, Purchase, Sales.

In item ledger entry record become approx 17L in a six month.

our process become slow day by day.

Is there any solutions for fast access the database.

Please help us to smooth and fast process of ERP.

kine

I recommend to contact someone who is offering the SQL performance tunning. I hope that local Microsoft can help you or you can look e.g. at SQLPerform copany site to look for some near partner...

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]

garak

There are many little "bolts" to tune up Navision on SQL.
So, if you are new on thes theme it's better to contact some local company which is good on this or you ask MS self in india what they say or which firm they advise.

Here on the forum, the sqlperfom company is a good tip. But i don't know if they support india, but maybe they will expand ;-)

Regards

Marije_Brummel

Hmm, India. :-k

Yes we do that occasionaly. Hynek or David do the onsite work and we do the remote backoffice stuff. Most of the time we are experienced to be bery expensive but hey... we solve the problem right?

Thanks for the confidence guys. Really appreciate it. \:D/

David_Singleton

We are working on a few performance tuning projects in India. In general the problems are common to what is seen everywhere, but there are some things that add to the situation. The first one is that we see excessive use of dimensions and analysis views, with automatic posting. This causes a lot of blocking. Secondly we are finding a lot of under powered hardware, and what's more many end users that are unwilling to purchases the proper hardware, even though the cost is not that much. but by far the biggest issue with the implementations that we are seeing India has to do with the way projects are implemented. Seniority is measured by no of years and no of certs, instead of actual skills. Also "freshers" are often put on site to "fill seats" and make head counts, and often are given too much authority and too little training. Add to this that analysis and development we see as being done a lot in parallel rather than as separate project phases, so what we see common to all these tuning projects we are working on, is that development is very much done with each developer working on their own island, so there is often code from one developer clashing heavily with that of another.

I am saying this mostly to point out that its unlikely that you will be able to "fine tune" this database. You are going to need someone to look at the problem from the outside and that all the issues as interacting and affecting one another and look at this as a Full Tuning project. I should point out that the projects we are working on are all in the south, and maybe in the North it's different, but I do hope this helps at least a little bit.

stryk

Hi!

Well, as some sort of "first aid" you should look into the SIFT structure of your most important tables and reduce the number of "buckets".
Here, as a "rule of thumb", you could disable all buckets EXCEPT the one before he last one; for example if you have 5 buckets starting from 0 counting to 4, then 0 (GRAND TOTAL) and 4 (Primary Key level) is disabled anyway, leaving 1,2 and 3 enabled. In this case you could disable 1 and 2, finally remaining only bucket 3 (see also http://dynamicsuser.net/blogs/stryk/archive/2007/10/28/included-columns-vs-sift-tables.aspx)
If you are using "Dimensions" I also recommend to look into this example about "Clustered Index" changed in T357: http://dynamicsuser.net/blogs/stryk/archive/2008/05/12/blocks-amp-deadlocks-in-nav-with-sql-server.aspx

And of course: browse the forum about recommendations/hints about optimized hardware, db-configuration, maintenance and more!

Regards,
Jörg

P.S.: As for the advertising part: there are also other NAV/SQL Performance companies around - check it out! 8)

chandrurec

Ask the technical team to reduce the number of joins and stored procedures in order to fasten the process.

Regards,
chandrurec

David_Singleton

chandrurec wrote:

Ask the technical team to reduce the number of joins and stored procedures in order to fasten the process.

Regards,
chandrurec

This is exactly why there is so much work for NAV SQL performance specialists. ](*,)

Marije_Brummel

Haven't done a smiley post in a long time but this one begs for it

Just don't know which smiley to use...

](*,)

garak

no comment,
but maybe a new customer job for you Mark :-D