[SQL] Identify & Disable “Expensive” JDE Indexes

About

By default, JDE creates a lot of database table index definitions.  When the “Generate Table” command is used in OMW, all of the JDE-defined Indexes are generated along with it.  But why does JDE have so many indexes to begin with?

The reason for all of those predefined indexes really comes down to design decisions in the JDB middleware.  The designers assumed that if you want to use “ORDER BY” (for example, to grab the last line number for a Sales Order) in a Table I/O, then you ought to have a database index to go with it.

JDE developers often need to use sorting for a specific Table I/O operation, so they are frequently defining new indexes.  The end result is a JDE database that’s filled with large & expensive indexes that slow down database INSERT and UPDATE operations, while rarely providing performance benefits for SELECT operations.

What is an “Expensive Index?”

In this case, I’ve defined “expensive” to be any non-unique indexes that SQL Server is frequently maintaining but rarely accessing.  A valuable metric that I am not including (for performance reasons) is disk usage.  I am using the SQL Dynamic Management View sys.dm_db_index_usage_stats, and I’m looking at the following metrics:

  • The total count of index reads (user seeks + scans + lookups) – A high number means the index is frequently used (beneficial)
  • The total count of index updates (user updates) – A high number means the index is frequently updated (costly)
  • The ratio of updates to reads (index_updates / total_index_reads) – Very low numbers indicate a useful index, very high numbers indicate one that is both expensive and rarely used.
Usage

Run the query to produce a list of all non-unique indexes in the system.  (NOTE: Some indexes are never used and will return NULL statistics.  CAUTION: unique indexes should never be disabled!)  Then evaluate the worthiness of each index based on the following suggested criteria:

  • Highest user_updates should be evaluated first.  They are frequent & they slow down INSERT and UPDATE operations.
  • Low (or zero) total_user_reads should be disabled.  These are not providing much of a performance boost for SELECT operations.
  • Update_to_read_ratio:  Over 300 is likely bad.  (We have some over 100,000!)  Values smaller than 1 means the index is likely valuable because it is read more often than it is written.

After you’ve identified which indexes are the least beneficial, you can disable them (freeing disk space & system performance) by executing the contents in the column disable_sql_command.  It is safe to disable several dozen at a time without negatively impacting performance.  The command is very easy to undo as well, you can rebuild any and all of the indexes that have is_disabled = 1.

My "expensive" SQL indexes query. You can see I have already disabled many of the top results based on high user_updates and update_to_read_ratio values. There are a few questionable indexes that have high enough total_user_read values that I have left enabled.
Query output. You can see I’ve already disabled some of these indexes.
SQL Query
-- be sure to "use" the correct database before running this query
select 
    object_schema_name(i.object_id) as [schema],
    object_name(i.object_id) as [table],
    i.name,
    i.object_id,
    i.index_id,
    i.type_desc,
    i.is_disabled,
    i.is_unique,
    us.user_seeks,
    us.user_scans,
    us.user_lookups,
    us.user_seeks + us.user_scans + us.user_lookups as [total_user_reads],
    us.user_updates,
    us.user_updates / CASE WHEN (us.user_seeks + us.user_scans + us.user_lookups) = 0 THEN NULL ELSE CAST(us.user_seeks + us.user_scans + us.user_lookups AS FLOAT) END as [update_to_read_ratio],
    us.last_user_seek,
    us.last_user_update,
    'ALTER INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(object_schema_name(i.object_id)) + '.' + QUOTENAME(object_name(i.object_id)) + ' DISABLE;' as [disable_sql_command]
from 
    sys.indexes i left join sys.dm_db_index_usage_stats us 
        on  i.object_id = us.object_id 
        and us.database_id = db_id()
        and i.index_id = us.index_id
where
    i.name LIKE 'F%'
    AND i.is_unique = 0
    AND i.is_unique_constraint = 0
order by
    us.user_updates DESC -- most expensive indexes first

Leave a Reply

Your email address will not be published. Required fields are marked *