We have a 1TB (approx.) database of Microsoft Business Solutions Navsion v4.0SP2 with LS Retail.
I have found through google search the document "Tuning Navision for better performance" by Patrice Dupont-Roc.
One of the optimisation he proposes is to split the database in 6 filegroups (allocating the tables to each filegroup depending on the data they carry) while stating that this does not apply to striped RAID strategies.
Though this is a fairly old, and probably outdated document, I would like to ask why does this not apply to striped RAID strategies (and taking it a step further I would say to iSCSI SAN configured for RAID 10)?
Also, does it make more sense to have more than one files for the data given the size of the database?
Another optimisation that is proposed (for SQL databases generally) is keeping the indexes (other than the clustered ones) on a separate filegroup. Does this work with Navision?
And one last thing, our iSCSI SAN is a Dell Equallogic (Actually two units) which optimises gradually by spreading the data across it's disks to increase performance. Wouldn't that on it's own cancel out any need
for using different filegroups?
Thank you in advance,
MVP - Dynamics NAV
Splitting the DB into multiple parts is actually not necessary anymore, since the hardware e.g. the storage systems are much more powerful than 10 years ago. Thus, you should have a powerful disk-subsystem, plenty of spindels if using RAID10 (with SSD this might be different) and powerful FC controllers (with lots of caches). Also "high-end" SAN solutions might be feasible.
What actually is really required needs to be investigated and discussed thoroughly (what IS the current I/O, how long are the disk response times?); just the size of a DB is not the most important thing - it is the transaction volume you process every day! I'm pretty sure that your users are NOT moving 1 TB of data around the whole day ...
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool
Also keep in mind that 4.0SP2 in combination with SQL2005 without the right hotfixes whas probably the worst combination for NAV ever.
My suggestion would be to have an expert do a quick scan on you system.
In the mean time I am always looking for more information on how I can get our current version in a better shape just enough to get us till the upgrade.