indexes and MSSQL 2008 R2

[cncjay]

Hey All,

In an effort to manage indexes for E1 on MSSQL server 2008 R2,


Question 1: After careful monitoring, if it is determined that some of the indexes on JDE delivered tables are simply not being used - can I delete them ?

A contractor E1 developer recently mentioned that, for example, F4316 has the following indexes:

F4316_PK
F4316_1
F4316_4
F4316_5,

If I was to delete F4316_4 because it has not been in use, then the existing indexes get renumbered which will cause havoc.

I have doubts about the remaining indexes getting renumbered - can anyone confirm this, if it is a fact or a myth.


Question 2:

The SQL query analyzer will often recommend indexes that don't exist for standard delivered JDE tables, is this safe to do outside of E1? I've done a few so far and they 'seem' to be helping, I can't tell if there's a negative impact. Any best practices I should be following when creating additional indexes?

Thanks a million in advance

 

[JEMILLER]

A1: You can delete indexes from the backend. Leave the JDE index definitions as they are and the code referencing them will be fine. I suggest they only be deleted if they are not being used. Keep in mind that they might be used by an infrequent process such as a month-end job or a rare ad-hoc process. Dropping an index from the backend will not trigger a resequencing of the indexes within JDE.

A common misconception is that the index selected in the ER code is the index that will actually be used by the database. The DB query engine decides what indexes (if any) will be used for a particular query. Unfortunately the JDE development tools are limited in terms of what "queries" you can construct and they usually require an index to be defined in order to fetch by a particular list of fields. I figure this is a throwback to World and the AS/400 concept of a logical file where you can specifically read through a logical file.

A2: You can add whatever indexes from the backend you want to add. Wherever possible I suggest adding them from JDE so that they can be tracked within OMW but that is not a requirement. Further there are times when index types such as bitmap or other specialty index types cannot be added through JDE so you must add them through the backend if you want them.

For good transaction performance it makes sense to have as few indexes as needed. It is not suprising to find lots of indexes not used for a particular JDE site. Out-of-the-box JDE has indexes delivered for all functional modules and all usage patterns that Oracle has thought of. Your particular usage of JDE may not require all those indexes and may need indexes not delivered by Oracle.

I say go for it. Just keep good records of what you are removing or adding and have historical performance monitoring data to gauge whether you indexes changes have helped or hindered overall performance. Also keep in mind that what you remove may someday need to be added again. There is no substitute for an fully engaged DBA. JDE may be packaged software but it still needs active maintenance and tuning from a database perspective.

 

[brother_of_karamazov]

"In an effort to manage indexes for E1 on MSSQL server 2008 R2..."


Why?


I leave SQL Server indexes only...completely. In almost 15 years of doing this I have never removed or added an index, particularly not the ones SQL Server recommends. The recommendations are a bit generic and usually fail to take into account periodic processes and actual usage over time. There are methods of doing in-depth index usage analysis (See: http://jeffstevenson.karamazovgroup.com/2011/10/sql-server-most-utilized-tables.html) but I do not believe that any potential performance gain is worth the hassles associated with changing the packaged indexes.

The only time I would consider adding an index would be if there was a need that was proven by testing and the addition of the index would have a noticeable value to the business. You may find my approach extreme and others on here may disagree but I have never experienced trouble during upgrades of ESU's related to indexes. I just happen to think that it is not worth the hassle tracking these changes. The same applies for the removal of an index - it better be a darn good reason, provable to be remedied by the removal of the index and of provable business value.







[ QUOTE ]
Hey All,

In an effort to manage indexes for E1 on MSSQL server 2008 R2,


Question 1: After careful monitoring, if it is determined that some of the indexes on JDE delivered tables are simply not being used - can I delete them ?

A contractor E1 developer recently mentioned that, for example, F4316 has the following indexes:

F4316_PK
F4316_1
F4316_4
F4316_5,

If I was to delete F4316_4 because it has not been in use, then the existing indexes get renumbered which will cause havoc.

I have doubts about the remaining indexes getting renumbered - can anyone confirm this, if it is a fact or a myth.


Question 2:

The SQL query analyzer will often recommend indexes that don't exist for standard delivered JDE tables, is this safe to do outside of E1? I've done a few so far and they 'seem' to be helping, I can't tell if there's a negative impact. Any best practices I should be following when creating additional indexes?

Thanks a million in advance

[/ QUOTE ]

 

[Jaise James]

In Our setup, I refrain from deleting any indexes. I had an issue long time back where a particular application was working poorly and After lot of research and working Both Oracle and MS, it was determined that one of the underlying indexes was causing the issue. MS recommendation was to drop the index, when we did that application started to perform in correctly and was showing in correct results

We have setup a real time replicated environment for SQL server. IN this replicated environment, we just keep the primary key indexes and create what ever indexes we need. We use all our reporting solutions ( We use SQL reporting, Insight reporting etc) pointing to replicated environment.

 

[BOster]

[ QUOTE ]

Unfortunately the JDE development tools are limited in terms of what "queries" you can construct and they usually require an index to be defined in order to fetch by a particular list of fields. I figure this is a throwback to World and the AS/400 concept of a logical file where you can specifically read through a logical file.


[/ QUOTE ]

Another common misconception. I often see developers add a JDE defined index because they think they need it to construct the WHERE clause on the fields or are just to lazy to write a few extra lines of code. As a result you get a lot of unessary indexes on tables. You can create the WHERE clause on any field(s) in a table whether they are contained in a JDE defined index or not using the standard, out of the box, JDE toolset/APIs. In fact you can create quite complex WHERE clauses using NEWSELECTSTRUCT and JDB_SetSelectionX (especially on business views). ER code is a little more limiting but still allows you to create a WHERE clause on any field in the table/BSSV even if the field(s) are NOT contained in a JDE defined index. Generaly speaking Indexes should only be added by a developer for performance reasons, NOT because they think they need it to write their code.