NAV4SP3 vs ”NAV4SP3 Hotfix 6+6.1+6.2+6.3 and IndexHint=No”

pdj

(Using MS-SQL 2005 – no servicepacks, yet)

I’m currently in the process of migrating a Native NAV4SP3 to SQL, but encountered a strange behavior.

First step was to create the DB and import a backup. This was done without problems and the performance was acceptable. This was done using a plain NAV4SP3 without any hotfixes.

Then we updated NAV client by adding Hotfix 6 and hotfix 6.1 to 6.3. The next time we opened the database it had to upgrade it (fix the SIFT bug) and that also went ok. Performance was still acceptable, but now it used IndexHints everywhere. I prefer the old behavior, so I made the IndexHint=No entry, to globally disable this feature. (It’s not a bug; it is just an annoying feature )

Now it stopped sending IndexHints, but now it suddenly started doing tablescans very often! I know the IndexHint is used to prevent tablescans, but we didn’t have any problems with tablescans before we updated the client.

So what have changed in the SQL-statements or table definitions from an out-of-the-box NAV4SP3 to the fully updated NAV4SP3 with hotfixes till version 6.3 with disabled IndexHint? (And is there anything I can do to restore the old behavior?)

PS: I know we need to upgrade the SQL server with recent servicepacks. This is to be done by an external company – but just because they are behind schedule doesn’t mean I have to be :-)

kriki

-install latest SP+hotfixed on SQL
-do a indexrebuild. This way the SQL-statistics are refreshed.
-if this doesn't help, I would check the C/AL code that generates the SQL-commands that uses tablescans
-Did you already do some SQL-tuning?

Last week, I did an upgrade from SQL2000 to SQL2005 and from 4.00SP3 to 4.00SP3U6+lockrelease on error-hotfix. I have tablescans but the server doesn't feel it:the disks are begging us to have something to do.

pdj

kriki wrote:

-install latest SP+hotfixed on SQL

We (they) will. Right now I would just like to change one thing at a time.

kriki wrote:

-do a indexrebuild. This way the SQL-statistics are refreshed.

This did the trick, thanks. My mistake that I thought the IndexStats were flushed by a server restart.

kriki wrote:

-if this doesn't help, I would check the C/AL code that generates the SQL-commands that uses tablescans

Simple drill-down from Chart of Accounts...

kriki wrote:

-Did you already do some SQL-tuning?

Not yet on this database. It is just a 25gb db with 30 users, so I don't expect it needs extensive tuning. But this is to be determined by tests.

kriki wrote:

Last week, I did an upgrade from SQL2000 to SQL2005 and from 4.00SP3 to 4.00SP3U6+lockrelease on error-hotfix. I have tablescans but the server doesn't feel it:the disks are begging us to have something to do.

Tablescans without the disks feel it... But what about the users? 8)

kriki

-For th G/L Entries : try to create an index "G/L Account,Entry No." and use it as the SETCURRENTKEY of the G/L Entries in case there is only a filter on "G/L Account".
-SQL-tuning: after this, your DB will probably be 12-13GB! PS:this has to be done by a specialist.
-If all else fails, try to create an indexhint directly in SQL for the G/L Entries form.