Share via

Microsoft Dynamics NAV 5.0 SP1 and SQL Server 2000

  • Article

Microsoft Dynamics NAV 5.0 SP1 introduces a new way to handle SIFT. Instead of maintaining totals in separate tables, Dynamics NAV 5.0 SP1 uses a SQL feature called indexed views. Indexed views will automatically be maintained by the SQL Server.

 

With SQL Server 2000, updating an indexed view can be a time consuming process as the SQL Server 2000 might decide to include a clustered index scan as part of its query plan to update the view. If your Microsoft Dynamics NAV implementation includes tables with many records and is based on Microsoft SQL Server 2000 there is a potential risk for experiencing bad performance.

 

Microsoft Dynamics NAV 5.0 SP1 is optimized for Microsoft SQL Server 2005 and you should consider to upgrade to SQL Server 2005 if you are implementing Microsoft Dynamics NAV 5.0 SP1.

 

The problem described here, only applies to SQL Server 2000. SQL Server 2005 handles the updates of indexed views much more efficiently.

 

 

Lars Lohndorf-Larsen

Escalation Engineer

 

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Comments

  • Anonymous
    June 04, 2008
    May be a stupid or unnecessary question but anyway ... . I've seen some database which the customers "upgraded" themselves to SQL2005 still in compatibility mode '80', being SQL2000.  Is this going to suffer from the same problem?

  • Anonymous
    June 05, 2008
    Hi, Good question! I can't be sure, but as far as I can see the problem does not exist on SQL2005, even in compatibility mode 80. I tested it here, and get the same query plan with compatibility mode 80 and 90, and the same number of reads when SQL updates an indexed view. But to be completely sure, you would need to test this on the customer's database. I use this code to test it: ItemLedgEntry.FINDLAST; ItemLedgEntry.Quantity := ItemLedgEntry.Quantity + 1; ItemLedgEntry."Invoiced Quantity" := ItemLedgEntry."Invoiced Quantity" + 1; ItemLedgEntry.MODIFY; With Profiler running, including the "Showplan Text" event. I then look at the query plan, and see if it contains "Clustered index scan". On SQL200 it does. On SQL2005 it doesn't. SQL2005 has clustered index seek, but no scans. So, even if I cannot be sure, it looks like the indexed views run OK on SQL2005, also in compatibility mode 80. But I would still recommend that customers run a clean SQL2005. Are there some reasons why customers need to run in compatibility mode 80? Best regards Lars

  • Anonymous
    June 06, 2008
    Not that I know of.  But it is also easily overlooked.

  • Anonymous
    July 31, 2008
    Normally, I would have done an article about Sumindexfields on versions previous to 5.0 SP1, but I decided