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.

Sift

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

 

 

Advertisements
This entry was posted in General, Performance, Keys, Indexes, SQL Server. Bookmark the permalink.

3 Responses to NAV2013R2 | Maintain SIFT Index

  1. Dave says:

    Hi,
    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.

    Dave.

    Like

  2. phenno says:

    Mark,
    could this advice be applied to Item Ledger Entry No.,Entry Type key in Value Entry table which also has disabled Maintain SIFT Index?

    When you list Item Ledger Entries page there is a number of outer joins with Value Entry table, all with SUM.

    Of course, more information could be used for descision such as if FIFO is in use there would probably be only few value entries per item ledger entry, but that could also be applied to Sales Invoice Lines (if there is large number of invoices but with few lines only).

    Phenno

    Like

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s