Lost VSIFTIDX ?

Dynamics 365 Business Central/NAV Technical Forum
1

This morinig I received an emergency call:

Suddenly many user are getting errors telling “the NOEXPAND hint is invalid on the VSIFT View”. Well, the NOEXPAND is invalid, if the View is not indexed, so we checked, and indeed the VISFT Views had no more index VSIFTIDX [+o(]

To fix this, we disabled the flags “MantainSIFTIndex” - then save the table - then re-enable the flag to force the recreation of the VSIFT View plus Index.

Well, so far, so good. But still the “One Million Dollar” question remains: How could this happen ???
Who or What was dropping the Indexes?

So far we have not found any “manual” interaction, or any SQL code or maintenance (I’ve seen re-indexing scripts which were dropping indexes, the re-creating - but not here) … or is that a bug? I’ve recognized that the “Table Optimizer” is dropping & re-creating the Indexes and VSIFT (incl. VSIFIDX) …

(Running SQL 2008 SP1 EE x64 with NAV 2009 SP1 29626)

Anyone experienced such an issue? Any ideas?

Cheers,
Jörg

2

Just recently we had the same case on another customer’s site - but here we know what went wrong!
Somebody used the “SQL Server Tuning Advisor”, trying to optimize some TRC and allowing the TA to change the existing index structures … in such a case the TA could drop supposedly unsued indexes and stuff …

This is actually a good example why NOT to use the “Tuning Advisor” (or any othe r auto-pilot tuning thingy) as most of it CANNOT be applied to NAV … NAV/SQL optimization is somewhat different to pure SQL tuning …