Index

What is an index?

Before we start talking about indexes, let’s think about the a question: What is an index?

Take a look at the yellow papers; there are millions of phone numbers in it, and they are categorized on City, Business, Business type etc. If you want to find something, you look into the categorized area; there you find some information with a page number. Next you find the page, and start reading this page till you find the thing you need. This categorized information acts the same like an index, and reading the page till you find what you needed is called a page scan. So when I have a phone number and I need the business that belongs to that number, I have to read all the pages of the yellow pages till I have found it. There is simply no support of categorized items. This scanning of the complete yellow pages is called a table scan in a database.

But what will happen if I get new business information into my yellow pages.  Or the name of a business has changed. My categorized area needs to be update for giving correct information. In a database we call this reindexing This reindexing can become heavy if the data has to be moved from on page to another page. This is also the reason why fields of an index value should have the property allow edit on create.

A highly optimized index is a cluster index, there is no additional storage, and the data is stored physical in the correct order.  If we look closely at the physical storage of a clustered index, we see that the data is grouped in pages; every page belongs to an index key value. When we have a lot of inserts into a clustered indexed table we get also performance issues. The database has some free space between the pages, but when this free space is consumed, the storage of the data gets fragmented into the database. Reindexing the database can solve this.

In general, an index knows on which page in the SQL database the record exists. By using indexes only the SQL database pages that have related information, are scanned to find the record. So let’s pretend we have an index on a Boolean field of a table.  The records with yes and no values exist on all pages, so the index will use all pages. (Index scan)

Types of Indexes

There are mainly 2 types of indexes which are as follows:

Clustered Index – A clustered index is a special type of index that arranges the way records in the table are physically stored. You can create only a single clustered index against one or more columns of a table or a view. A maximum of 16 columns can be a part of a clustered index. A clustered index is identified in the sys.partitions table and has an Index_ID value of ‘1’. When Clustered indexes are created a B-Tree structure is created and the leaf nodes of a B-Tree contain the data pages.

Non Clustered Index – A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. When Non Clustered indexes are created a B-Tree structure is created and the leaf node of a B-Tree of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain a pointer to the data pages. You can create up to 249 nonclustered indexes for a table or a view. These indexes are created to support the queries that are used to retrieve data from a table or a view. A nonclustered index is identified in the sys.partitions table with anIndex_ID value of 2 to 250.

A table which has no Clustered Index is referred to as a Heap.

It becomes important to know the basics of how indexes work as a correct index will boost performance and a wrong index will drastically reduce performance.

select * from sys.indexes ind where ind.object_id = (select OBJECT_ID from sys.tables tab where  tab.name = ‘CustTable’)

Note the column ‘type_desc’ which gives the type of Index in CustTable. The same indexes can be viewed within the AOT as shown in Figure 2. However the levels of details shown in SSMS are more and are required by administrators to get details on Indexes. Dynamics AX Kernel by default adds the RecId as a Non Clustered Index which can be seen from SSMS.

How Indexes Improve Performance

Indexes can optimize not only SELECT, but also UPDATE and DELETE statements. The reason is that the SQL Server must find the records prior to modifying or deleting them. Keep in mind however, that SQL Server has to maintain clustered and non-clustered indexes every time data is INSERTED, UPDATED or DELETED. Having numerous indexes on a table can cause significant overhead on the SQL Server during data modifications.

When a query is sent to SQL Server, the query optimizer comes up with the most efficient execution plan. SQL Server does not have to use an index to satisfy a query; in fact, if it is quicker to scan the entire table, SQL Server will do so. Usually if a query requires returning more than about 1% of table rows and there is no covering index, it is quicker to scan the whole table than to seek on the nonclustered and then lookup the missing columns from the clustered index/heap.

The popular understanding is that adding more indexes to a table will improve performance but this is not true. In fact over-indexing can degrade performance drastically. So how do we know how many indexes to add and where the thin line is which differentiates between over- & under-indexing? Well, there is no magic formula for defining the number of indexes but it depends on a lot of important factors like data type in the tables, work load, query structure etc.  With CRUD statements, indexes undergo fragmentation and when the level of fragmentation increases above a threshold the query performance degrades. We will be discussing more on index fragmentation later on in this chapter.

Indexes in Dynamics AX

Indexes are created inside AX in the AOT Tables Node. Prior to index creation, index design has to be done as to determine the columns we want to index, what type of index we should consider, the number of columns in an index and the queries which we will use. It is relatively easy to create indexes inside AX, but some pointers must be kept in mind.

• Do the task of index design diligently. There is no magic formula which will tell you the number of indexes you must create for a table. You must work out and consider all possible scenarios.
• Ensure that you select the Primary Index & Cluster Index property in the Table Property sheet as shown below.
• Apart from properly indexing tables, also consider table caching properties to improve performance.
• Ensure all tables have a Cluster Index and a Primary Key.
• Reduce the number of fields in queries
• Monitor indexes and ensure that unused indexes are removed.

Apart from these tips, there are some best practices which will help you decide what indexes to create in AX. These are discussed under the best practices section in this chapter.

Best Practices for Index creation in AX

• Keep indexes lean. Try to build indexes on one or few columns. Wide indexes take longer to scan than narrow indexes.
• The number of indexes on a table should not be very high. Usually 7-9 indexes in a standard Master, Transaction table is recommended. For Setup & Parameter tables, limit the number of indexes to the important columns only.
• Create a clustered index on every table. However, choose the column(s) for the clustered index judiciously.
• Try to create the clustered index on the column which is used most frequently for retrieving data.
• Try to create the clustered index on the column with high selectivity; that is the column that does not have many duplicate values.
• Try to create the clustered index on the column that will never be updated or will be updated infrequently. Every time the clustered index key is updated, SQL Server must maintain not just the clustered index but also the non-clustered indexes since non-clustered indexes contain a pointer to the clustered index. This is yet another reason why you shouldn’t create the clustered index on multiple columns.
• By default, SQL Server creates the clustered index on the PRIMARY KEY column(s) unless the clustered index is created prior to creating the primary key. It is often beneficial to have the clustered index on the primary key, but sometimes you’re better off saving the clustered index for other column(s
• SQL Server has to perform some maintenance when index keys are updated, so each additional index could impose a slight performance penalty on the performance of INSERT, UPDATE and DELETE statements. Nevertheless the cost of index maintenance could be far less than the performance improvement you’ll reap for your SELECT statements.
• Be sure to eliminate duplicate indexes, that is, multiple indexes created on the same set of columns. Such indexes provide no additional benefit but use up extra disk space and might hinder performance of INSERT, UPDATE and DELETE statements.
• Check the default fill factor level configured at the server level. To control the rate of fragmentation of an index, you can use an index option called the fill factor. Modifying fill-factor is a DBA task and must be done carefully. For Dynamics AX the recommended value is 80%. You can enable this from the UI or use the CREATE INDEX or ALTER INDEX statements to set the fill-factor value for individual indexes. To modify the server-wide default value, use the sp_configure system stored procedure. To view the fill-factor value of one or more indexes, use sys.indexes.

Fill factor only affects the leaf nodes. For the intermediate nodes please refer to PAD_INDEX.