NAV $ndo$dbconfig updater

Administrator

NAV $ndo$dbconfig updater
These objects allow simple configuration of the NAV V4 and V5 $ndo$dcconfig table from within NAV.

This table is used to tune the SQL statements sent to the SQL-2005 database engine. With the release of SQL-2005 Microsoft have added new optimisation options that the databse engine may choose when compiling an SQL statement. Unfortunatly while these optimisations can give good results on simple databases the combination of the extra optimisation and the caching of the compilied versions of the sql statments is very likely to cause very poor performance with large databses in NAV.

Because of this the NAV group within Microsoft have added the ability to tweak the statments generated by NAV to add the 'workaround options' that the SQL group have specified so that the performance avilable from SQL-2000 can be recovered or improved on.

The way these options are specified is through adding rather complex text strings to the table $ndo$dbconfig, this table is not normally available within NAV and so is not saved in an fbk backup. These objects make the table available within NAV and break up the specification so that it's a lot easier to remember how to use it.

Note the attached image shows a suggested starting setup for NAV V5, the Recompile option is not available for NAV V4. Also for these options to work properly you will need either NAV V4 sp3 Update 6 or NAV V5 SP1 running on SQL 2005 at least SP2 plus the updates to bring it to version 9.0.3152 or MS-SQL 2005 SP3.

http://www.mibuso.com/dlinfo.asp?FileID=1040

Discuss this download here.

Slawek_Guzek

Very nice job, however the idea of exposing those settings and allowing to change in easily accesible way is not good IMHO.

Unexperienced user or consultant by changing any of those settings may actually make things much worse... Epecially that there are many developers and/or consultants which are experienced, but still their SQL knowledge is not enough to safely play with those...

Regards,
Slawek

rdebath

Slawek Guzek wrote:

Unexperienced user or consultant by changing any of those settings may actually make things much worse... Epecially that there are many developers and/or consultants which are experienced, but still their SQL knowledge is not enough to safely play with those...

Normally I'd agree, tuning a database is a specialist job like many others, the problem is Navision and SQL2005 work so badly together that there isn't actually much that a novice could do to make things worse! It would probably take an expert to mess it up any more!

So this gives an easy way to add a few simple rules, like add an "option recompile" to all the big tables, without having to worry too much about the do'h moment that comes when you realise that you've misspelt recompile or even "G/L Entry".

Personally, I don't expect this whole problem to be fixed properly until Navision starts using the SQL2008 option "OPTIMIZE FOR UNKNOWN" (which sounds like it's the bugfix for this) to workaround the unpredictably of the query optimiser, until then all we've got is this painful set of tweaks that I hope I've made a little more comfortable.