Index field order – performance tuning

The design of your database, along with your indexes will determine much of the performance limitations of your system. The indexes are how the system will locate information, and if these are well designed can produce considerable performance improvements. This blog will look at the index field order and the system fields, and the effects it can have on your system.

When an index is created on a table (in DAX) you select the fields to include in the index. The system will then synchronize this index with the database server creating or updating the index in SQL.

A typical example of an index created in DAX might be the on the salestable and an index called SalesIdx. The index in DAX has the field SalesId, and this is a unique index. If you look in SQL and this index you will see three fields, partition, dataareaid and salesid. The system automatically includes the partition and dataareaid, if the table is not shared, into the index.

When we look at the way the SQL index will work for most implementations we find the following:

  1. Most implementations only have a single DAX partition.
  2. Many implementations only have a small number of companies.

If the above are true, and your sales table has many thousands of records, or more, then the index will not work very efficiently.

The design of an index should have the most unique element of the index first, this means the speed to find the data required is quicker. In the above case the salesid is the most unique information, with dataareaid next and partition last. So ideally we would want the index to be formatted with SalesId, DataareaId and Partition.

The good news is this can be achieve within the standard DAX environment. If we go to the index on the SalesTable and go to the SalesIdx index, we can right click and select new field. Then select the new field and open the properties, allowing you to select a new data field, select DataAreaId. Do the same again for Partition. Once all three fields are present, ensure they are ordered in the correct order; salesid, dataareaid, partition.

Now this have been completed ensure you have synchronized your system so this change is transferred to SQL server. If you look at the SQL server you will not see the change to the index field order, and if you run queries you should see an improvement  in performance.

It is important to remember that the design and development of indexes depends on lots of factors, and changes to standard indexes should not be changed without proper analysis and understanding, and making the wrong changes can hurt system performance. Changes required also dependent on the volume and mix of data, as well as the design of the code, all playing factors in the performance of data retrieval in DAX.

Happy performance tuning…

Advertisements

2 thoughts on “Index field order – performance tuning

  1. John Higdon says:

    Excluding the fixing of the 5000 indexes that have this issue in our database, is there another solution? If we fix the indexes, we are tied to fixing the indexes on every upgrade which isn’t very appealing.

    Like

    • I would never recommend mass changing all the indexes. Most of the indexes will not change the performance of the system. You should test small parts of the system, and review the indexes and code for that area and evaluate and test different solutions to improve system performance.

      Performance tuning is quite a time consuming process and after a system infrastructure review is completed, you should focus on business processes to review performance, and you should not just make global changes, or any changes, without testing the results before putting them into production.

      Some changes you make will need to be re-reviewed after big patches are implemented or after upgrades, but this is part of the testing and evaluation stage of implementing these changes.
      I hope this helps.

      Like

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