SQL Database fine tuning

rjvermarjverma Member Posts: 25
edited 2008-12-17 in SQL Performance
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.
Rupesh Verma
Floor & Furnishing (I) Pvt. Ltd.

Comments

  • kinekine Member Posts: 12,562
    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...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • krikikriki Member, Moderator Posts: 9,094
    [Topic moved from 'NAV/Navision' forum to 'SQL Performance' forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • garakgarak Member Posts: 3,263
    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
    Do you make it right, it works too!
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    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_SingletonDavid_Singleton Member Posts: 5,479
    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.
    David Singleton
  • strykstryk Member Posts: 645
    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)
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • chandrurecchandrurec Member Posts: 560
    Ask the technical team to reduce the number of joins and stored procedures in order to fasten the process.

    Regards,
    chandrurec
  • David_SingletonDavid_Singleton Member Posts: 5,479
    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. ](*,)
    David Singleton
  • Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Haven't done a smiley post in a long time but this one begs for it

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

    ](*,)
  • garakgarak Member Posts: 3,263
    no comment,
    but maybe a new customer job for you Mark :-D
    Do you make it right, it works too!
Sign In or Register to comment.