Optimize Nav 2009 with SQL Server

nverma
nverma Member Posts: 396
edited 2012-05-24 in SQL Performance
Hey yall,

I was wondering what are some of the best practices to optimize nav with sql server. I tried searching on google and i cant really find a clear set of best practice. Our company is trying to fine tune the database for one of our clients. Also, we are looking for ways to increase (make efficient) the READ and WRITE capabilities of the system while reducing the size of the database.

Any tips or properties that we can setup in the system that might help achieve this goal will be greatly appreciated.

Also, if you know of any tools (paid/free) that might help with this optimization.

Comments

  • Sog
    Sog Member Posts: 1,023
    Contact J. Strike is the best recommandation I can give you.
    He's one of the best SQL-experts for NAV.
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • nverma
    nverma Member Posts: 396
    edited 2012-05-24
    ummm...ok...thanks
  • kriki
    kriki Member, Moderator Posts: 9,121
    [Topic moved from 'NAV/Navision Classic Client' forum to 'SQL Performance' forum]

    Joerg Stryck: http://www.stryk.info/

    but also http://www.sqlperform.com/
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • DenSter
    DenSter Member Posts: 8,307
    First of all, you need to make sure it is all set up and maintained properly. Here's a couple of videos that might be useful for you:
    SQL Server Setup for NAV: http://youtu.be/pOIjde-xpz4
    SQL Server Maintenance for NAV: http://youtu.be/0KbZkKdyZps
    No external tool needed to do this. If that is not enough, you will need to get an expert involved. Talk to your partner about getting some help in this area.
    Daniel Rimmelzwaan
    RIS Plus, LLC
  • nverma
    nverma Member Posts: 396
    Thanks Den and Kiriki.

    @Den - I will definately check out the vidoes on your youtube channel.
  • davmac1
    davmac1 Member Posts: 1,283
    One minor but important tip.
    If you are running recovery model: full (recommended) then backup the full database and the log files, else the log files keep growing until you run out of spaces and SQL Server crashes.
    Backing up the log file does not shrink it, but it does release space by removing completed transactions that have been backed up in the database backup.
    From SQL Server 2008 on, you can no longer truncate the log file which was the easy fix.
    (I just ran into this yesterday - 980GB in 2 log files on a 1TB disk.)

    The rest of the tuning - research, read books, and as the budget permits use a NAV SQL Server performance specialist.
    David Machanick
    http://mibuso.com/blogs/davidmachanick/
  • bbrown
    bbrown Member Posts: 3,268
    davmac1 wrote:
    One minor but important tip.
    ....
    From SQL Server 2008 on, you can no longer truncate the log file which was the easy fix.
    ....


    Well actually you can (sort of). In 2008 (and up) this is accoumplished by switching to Simple Recovery and the back to Full.
    There are no bugs - only undocumented features.

Categories