Optimize Nav 2009 with SQL Server

nverma

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.

Sog

Contact J. Strike is the best recommandation I can give you.
He's one of the best SQL-experts for NAV.

nverma

ummm...ok...thanks

kriki

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

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

but also http://www.sqlperform.com/

DenSter

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.

nverma

Thanks Den and Kiriki.

@Den - I will definately check out the vidoes on your youtube channel.

davmac1

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.

bbrown

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.