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.
.gif)
.gif)
.gif)
.png)
