Partager via

What is the cost of maintaining an indexed view?

  • Article

In previous versions, Microsoft Dynamics NAV maintains SIFT totals in SIFT tables. So updating the main table is done in one query. Updating the related SIFT tables is done by seperate queries run from triggers on the SQL tables. This makes it difficult to idenitfy the real cost of updating the table with a SQL Profiler trace, since you need to take several queries into account to get the real cost.

From NAV version 5 SP1, the SIFT tables are replaced with Indexed Views which makes it simpler to trace the full cost of an update to a base table and its associated indexed views:

In a SQL Profiler Trace, enable the event Performance:Showplan XML. With this event, when you see an update to a base table (for example INSERT INTO "W1500SP1"."dbo"."CRONUS International Ltd_$G_L Entry" etc), then the "Showplan XML"-event for this query will show not just the insert into the base table, but also which indexed views were updated, and the percentage of cost that each indexed view added to the whole query. So with this event you will have the full cost of an update and associated SIFT indexes in just one place - not spread over multiple queries in the Profiler trace.

You can read more details and screenshots of this, and other changed in NAV 5 SP1 in this post:

Changes to Microsoft Dynamics NAV 5.0 SP1 with Microsoft SQL Server

 

 

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