MaintainSQLIndex=no

Mortenr

Hi, I´m no expert but this question has been in my head for some time now, and need to get it out ](*,)

Would one gain any performance if the key is disabled and MaintainSQLIndex=no , I have a database where some keys are disabled but the MaintainSQLIndex=yes , would the Sqlindex then still be maintained somewhere.

The same goes for MaintainSIFTindex , would there be cached some old indexed on the SQL server that would be maintained even though the key is disabled through Dynamics NAV

Regards

Morten

ara3n

once the index is disabled, SQL drops the index and does not maintain it.

If in navision you still use the key, it will work, sql will sort it on the fly.

Same applies to maintain SIFT.

There are several threads on this. You might want to read them.

Also I suggest getting somebody who knows about sql and Navision before making these changes.

Mortenr

Hi,

You write once the index is disabled, but what if the key is disabled, but MaintainSQLindex and MaintainSIFTindex is set to yes.

Does the SQL server then still maintain the index, and can this be a performance Issue sinces the key is disabled.

If I drop the index for keys that are disabled would I gain an performance is my actually question?

If I have tried to find threads about this actually question but I could not find an answer for what I´m asking :-)

I will off course not drop anything before trying above out in a test enviroment, but if one can say that there are not gain, then I would not go about doing some testing.

regards

Morten

ara3n

Mortenr wrote:

Hi,

You write once the index is disabled, but what if the key is disabled, but MaintainSQLindex and MaintainSIFTindex is set to yes.

Does the SQL server then still maintain the index, and can this be a performance Issue sinces the key is disabled.

If you disable the key in navision, it will disable it in sql. But you won't run any object that uses that key.

If I drop the index for keys that are disabled would I gain an performance is my actually question?

Only performance you'll gain is during insert into the db.

kriki

ara3n wrote:

If I drop the index for keys that are disabled would I gain an performance is my actually question?

Only performance you'll gain is during insert into the db.

More detailed : by disabling a key
-you gain INSERT/MODIFY/DELETE performance (and also for indexrebuilding and defragging.
-you might LOOSE reading performance and, very probably, you won't GAIN reading performance. But this depends on the key and on how the customer uses the table and its data.