Dynamics AX Development and Customization Best Practices -Index

Index considerations:

Basics:

An index is a physical database object that provides efficient access to data in the rows of a table based on key values.

It is important to understand the types of indexes that are available in SQL Server and Microsoft Dynamics AX, because indexing inappropriate columns, or not indexing appropriate columns, can significantly affect query performance.

Indexes are created on a subset of columns of a table and organized as B-trees. The top node of the B-tree is called the root node. The bottom level is called the leaf layer. Index levels between the root node and the leaf nodes are known as intermediate levels. Each index row contains a key value and a pointer to either an intermediate level page in the B-tree or an entry in the leaf layer of the index. The pages in each level of the index are linked in a double-linked list.

Indexes may be unique or non-unique. The key value of an index refers to the columns on which the index is defined. The key value of a non-unique index may not be unique.

SQL Server provides two main types of indexes:

Index design is not an exact science. Often, it is necessary to make some assumptions about how the application will behave, particularly with regard to read and write rates for critical data, and where performance trade-offs will be most beneficial to most users.

SQL Server provides two main types of indexes:

  • · Clustered index – The leaf layer contains the data pages of the underlying table. The clustered index and the table share the same physical allocation. A column defined in the clustered index occupies the same physical space as the column in the table. The columns of the table that are not explicitly declared as part of the clustered index are stored physically adjacent to the clustered index columns (except for large object [LOB] columns). The data rows of the table are stored in the physical order of the clustered index columns. There can be no more than one clustered index per table. A table with no clustered index is called a heap.

Indexes are designed to make frequent queries perform more efficiently. When the clustered index is used to access rows in a table, the leaf layer and the table data are stored together. After the leaf node for a row has been read, the entire row has been read (except for LOB columns). By contrast, a nonclustered index is stored separately from the data row of the table. When a nonclustered index is used to access rows in a table, the data rows must be read in separate I/O operations from the nonclustered index (unless the query is covered by the nonclustered index).

Note: A clustered index can also be defined on a database view. This is called an indexed view in SQL Server. Indexed views are not currently supported in Microsoft Dynamics AX.

Nonclustered index – The leaf layer is comprised of index pages rather than data pages. The clustered index key is used as a row locator for each nonclustered index entry.

All indexes for a table are nonclustered indexes in SQL Server, except for the single clustered index. Indexes (both clustered and nonclustered) can support the following operations:

  • Joins
  • Filters
  • Sorts
  • Aggregations (group by)

Nonclustered indexes are stored separately from each other and from the clustered index. Each nonclustered index occupies its own set of index pages.

Write-time I/O operations on nonclustered indexes include B-tree top, intermediate, and leaf levels. For example, in a nonclustered index with a single intermediate B-tree level, three index pages may be updated during a write operation for a single index entry: one for the top layer, one for the intermediate level, and one for the leaf level. More pages may be written if page splits occur at any of these levels.

By comparison, for each clustered index defined on a table, additional write I/O is required for the following scenarios:

  • Rows are inserted and deleted.
  • Indexed columns are updated.

You can create an index in Microsoft Dynamics AX in the Application Object Tree (AOT) Tables node. Before you create an index, you must design the index based on the following:

  • The columns to index
  • The type of index
  • The number of columns in the index
  • The queries that you will use

Relationship of clustered and nonclustered indexes:

  • A nonclustered index uses the clustered index key as a row locator if a clustered index is defined on the table. This means that all columns of the clustered index key are present in the nonclustered index entry, even though they may not be explicitly defined as part of the nonclustered index. A column on the clustered index is propagated
  • The length of the clustered index key affects the length of the nonclustered index key.
  • The length of nonclustered index keys influences how many nonclustered index entries are present on an index page. This influences how many index pages must be read when multiple index entries are read. A page (8 KB) represents the basic size of an I/O operation. The number of index entries per page influences the amount of I/O required to read multiple index entries, and I/O is a critical determinant of query performance.
  • The presence of the clustered index key columns in the nonclustered index affects queries that the nonclustered index can cover.

General indexing best practices

The primary index and clustered index are critical properties for a table. You should analyze and set the properties correctly.

You should analyze the usage and queries for customized tables (new and modified) and create necessary indexes. Depending on how the table is queried, necessary indexes should be created. Indexes can help boost performance for queries, but at the same time, they add cost to inserts and updates. Analyze your usage scenarios before creating indexes.

Design indexes to avoid index scans. An index scan requires the entire index to be read. A scan of the clustered index is equivalent to a table scan.

Use nonkey or included columns in nonclustered indexes to provide query coverage. Benefits include the following:

  • They impose somewhat less overhead than key columns. They are stored only at the leaf level of the index. Nonkey columns on nonclustered indexes are similar to columns in a table that are not part of the clustered index key.
  • A common example is in date-effective tables, where the ValidTo column is defined as an included column.
  • The main performance trade-offs of nonkey columns are that fewer index rows may fit on a page, and there is increased I/O in write operations. As with key columns, avoid including unnecessary nonkey columns in indexes.

Important: SQL Server Database Tuning Advisor (DTA) often recommends indexes with a large number of nonkey columns. If you are using DTA, make sure that changes are validated by a database administrator (DBA) and tested thoroughly before implementing its recommendations, especially recommendations for indexes with a lot of nonkey columns.

  • In general, if you know that there are specific queries that benefit from the presence of the clustered key columns in the nonclustered index, you can explicitly define them as nonkey (included) columns to ensure that they will continue to cover even if the clustered index is changed. However, this should not be done as a matter of course but only when there is a specific need.

For example, consider the AccountingEvent table indexes. Assume that your system includes a frequently executed query that was covered because RecId was effectively included in the index. If the clustered index on this table was changed at a later time so that RecId was no longer a clustered index key, the nonclustered index would no longer cover the query. In this case, you can explicitly define RecId as a nonkey column on the nonclustered index. When the clustered index key is comprised of RecId, this definitional change to the nonclustered index has no real effect in the physical storage of the index. However, by explicitly making RecId a nonkey column, you preserve the covering effect of the index if the clustered index is changed later.

Key information about nonkey columns includes the following:

    • A column cannot be defined as both a key and nonkey (included) column in the same index.
    • The same column cannot be defined as a nonkey column more than once.
    • A computed column can be used as a nonkey column.
    • Included columns can be of data types that cannot be used as key columns, such as NVARCHAR(MAX) and XML. Included columns are not considered by the database engine when it calculates the number of index key columns or index key size (16 columns and 900 bytes, respectively); a maximum of 1,023 nonkey columns can be included in and index, and the total size of the nonkey columns is limited by the size of the columns.

Note: For any performance testing to be effective, data volumes must be sufficiently realistic; otherwise, the execution plans produced by the following procedure will not be useful for determining actual performance.

  1. Use Profiler to prepare the application for tracing by navigating to the point where you want to capture SQL activity. This will help minimize the volume of SQL statements captured by Profiler.
  2. Prepare Profiler. Under the Stored Procedures event class, select the RPC Starting event. Then start Profiler.
  3. Start the application process, and let it run to completion.

Index design best practices

Index design is not an exact science. Often, it is necessary to make some assumptions about how the application will behave, particularly with regard to read and write rates for critical data, and where performance trade-offs will be most beneficial to most users.

Prerequisites

  • Understand the SQL Server index storage architecture.
  • Understand how indexes are used in queries.
  • Understand how data is most likely to be accessed.

Initial index design

  • Identify all indexes in the physical data model.
  • Microsoft Dynamics AX modeling guidelines require all indexes to be identified in the physical data model. We recommend that you use Microsoft Visio for this process.
  • In Visio, unique indexes are identified by the alternate key designation; non-unique indexes are identified by the inversion entry designation. The order in which columns occur in a composite index is important and must be accurately reflected in the physical data model.
  • Visio does not currently support the following:
  • Distinguishing clustered and nonclustered indexes
  • Identifying nonkey (included) index columns
Advertisements

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