NAV2013R2 | Maintain SIFT Index

As I already explained in my last blog post the way performance tuning is done in NAV2013R2 has changed from previous versions.

Rather than issueing a SQL Statement for each flowfield in each row on a listpage NAV now uses OUTER APPLY.

In a large upgrade project I am doing with a 100+GB database the Posted Invoices did not perform both from purchase and sales.

It is more work to analyse a query. Each flowfield adds an OUTER APPLY to the query and sometimes this can lead to queries being a page long. It helps to paste the query in SQL server management studio and search for OUTER APPLY and add a line break for each.

In this scenario the Amount and Amount Incl. VAT flowfields were added to the list hence two OUTER APPLY statements were added.

In this case the SIFT on the primairy key was disabled which was best practive in older versions of NAV.


However by activating the MaintainSIFTIndex property the performance of the page was back to being ultra fast like it should be in NAV2013R2.



1 Comment

  1. Dave says:

    We had an issue with a the same table in a customers database we’re upgrading. While your suggestion fixed it we traced the root cause to the table having no statistics. This was causing SQL to do a scan, rather than a seek on the clustered index on the Sales Invoice Line table. Updating the statistics and clearing the procedure cache fixed the issue.

    I’m not sure if we would still have issue if the database were larger, but ti’s something else worth checking.



Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.