Skip to Content
author's profile photo Former Member
Former Member

Monitoring Creation of Index

hi all,

we just created 3 indexes each for table A155 and A071. After that, everything in our system slows down, some were aborted after long running. Our suspect is that the indexes are still in-progress. Can you tell us a way to check if the index creation is still in-progress?

thanks,

sid

Add a comment
10|10000 characters needed characters exceeded

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 18, 2007 at 10:26 AM

    Hi

    i am giving total index and buffering concept details by seeing this you can understand how we can achive performance through these

    reward if usefull

    Performance during table access

    Indexes

    Primary and secondary indexes

    Structure of an index

    Accessing tables using indexes

    Table buffering

    Advantages of buffering

    Concept of buffering

    Buffering types

    Buffer synchronization

    Primary and secondary indexes

    Index: Technical key of a database table.

    Primary index: The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

    Secondary index: Additional indexes could be created considering the most frequently accessed dimensions of the table.

    Structure of an Index

    An index can be used to speed up the selection of data records from a table.

    An index can be considered to be a copy of a database table reduced to certain fields. The data is stored in sorted form in this copy. This sorting permits fast access to the records of the table (for example using a binary search). Not all of the fields of the table are contained in the index. The index also contains a pointer from the index entry to the corresponding table entry to permit all the field contents to be read.

    When creating indexes, please note that:

    An index can only be used up to the last specified field in the selection! The fields which are specified in the WHERE clause for a large number of selections should be in the first position.

    Only those fields whose values significantly restrict the amount of data are meaningful in an index.

    When you change a data record of a table, you must adjust the index sorting. Tables whose contents are frequently changed therefore should not have too many indexes.

    Make sure that the indexes on a table are as disjunctive as possible.

    (That is they should contain as few fields in common as possible. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.)

    Accessing tables using Indexes

    The database optimizer decides which index on the table should be used by the database to access data records.

    You must distinguish between the primary index and secondary indexes of a table. The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

    The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

    If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database.

    When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated

    Database access using Buffer concept

    Buffering allows you to access data quicker by letting you

    access it from the application server instead of the database.

    Advantages of buffering

    Table buffering increases the performance when the records of the table are read.

    As records of a buffered table are read directly from the local buffer of the application server on which the accessing transaction is running, time required to access data is greatly reduced. The access improves by a factor of 10 to 100 depending on the structure of the table and on the exact system configuration.

    If the storage requirements in the buffer increase due to further data, the data that has not been accessed for the longest time is displaced. This displacement takes place asynchronously at certain times which are defined dynamically based on the buffer accesses. Data is only displaced if the free space in the buffer is less than a predefined value or the quality of the access is not satisfactory at this time.

    Entering $TAB in the command field resets the table buffers on the corresponding application server. Only use this command if there are inconsistencies in the buffer. In large systems, it can take several hours to fill the buffers. The performance is considerably reduced during this time.

    Concept of buffering

    The R/3 System manages and synchronizes the buffers on the individual application servers. If an application program accesses data of a table, the database interfaces determines whether this data lies in the buffer of the application server. If this is the case, the data is read directly from the buffer. If the data is not in the buffer of the application server, it is read from the database and loaded into the buffer. The buffer can therefore satisfy the next access to this data.

    The buffering type determines which records of the table are loaded into the buffer of the application server when a record of the table is accessed. There are three different buffering types.

    With full buffering, all the table records are loaded into the buffer when one record of the table is accessed.

    With generic buffering, all the records whose left-justified part of the key is the same are loaded into the buffer when a table record is accessed.

    With single-record buffering, only the record that was accessed is loaded into the buffer.

    Buffering types

    With full buffering, the table is either completely or not at all in the buffer. When a record of the table is accessed, all the records of the table are loaded into the buffer.

    When you decide whether a table should be fully buffered, you must take the table size, the number of read accesses and the number of write accesses into consideration. The smaller the table is, the more frequently it is read and the less frequently it is written, the better it is to fully buffer the table.

    Full buffering is also advisable for tables having frequent accesses to records that do not exist. Since all the records of the table reside in the buffer, it is already clear in the buffer whether or not a record exists.

    The data records are stored in the buffer sorted by table key. When you access the data with SELECT, only fields up to the last specified key field can be used for the access. The left-justified part of the key should therefore be as large as possible for such accesses. For example, if the first key field is not defined, the entire table is scanned in the buffer. Under these circumstances, a direct access to the database could be more efficient if there is a suitable secondary index there.

    With generic buffering, all the records whose generic key fields agree with this record are loaded into the buffer when one record of the table is accessed. The generic key is a left-justified part of the primary key of the table that must be defined when the buffering type is selected. The generic key should be selected so that the generic areas are not too small, which would result in too many generic areas. If there are only a few records for each generic area, full buffering is usually preferable for the table. If you choose too large a generic key, too much data will be invalidated if there are changes to table entries, which would have a negative effect on the performance.

    A table should be generically buffered if only certain generic areas of the table are usually needed for processing.

    Client-dependent, fully buffered tables are automatically generically buffered. The client field is the generic key. It is assumed that not all of the clients are being processed at the same time on one application server. Language-dependent tables are a further example of generic buffering. The generic key includes all the key fields up to and including the language field.

    The generic areas are managed in the buffer as independent objects. The generic areas are managed analogously to fully buffered tables. You should therefore also read the information about full buffering.

    Single-record buffering is recommended particularly for large tables in which only a few records are accessed repeatedly with SELECT SINGLE. All the accesses to the table that do not use SELECT SINGLE bypass the buffer and directly access the database.

    If you access a record that was not yet buffered using SELECT SINGLE, there is a database access to load the record. If the table does not contain a record with the specified key, this record is recorded in the buffer as non-existent. This prevents a further database access if you make another access with the same key

    You only need one database access to load a table with full buffering, but you need several database accesses with single-record buffering. Full buffering is therefore generally preferable for small tables that are frequently accessed.

    Synchronizing local buffers

    The table buffers reside locally on each application server in the system. However, this makes it necessary for the buffer administration to transfer all changes made to buffered objects to all the application servers of the system.

    If a buffered table is modified, it is updated synchronously in the buffer of the application server from which the change was made. The buffers of the whole network, that is, the buffers of all the other application servers, are synchronized with an asynchronous procedure.

    Entries are written in a central database table (DDLOG) after each table modification that could be buffered. Each application server reads these entries at fixed time intervals.

    If entries are found that show a change to the data buffered by this server, this data is invalidated. If this data is accessed again, it is read directly from the database. In such an access, the table can then be loaded to the buffer again.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 18, 2007 at 07:27 AM

    Hi,

    Questions:

    1. How many records are in the tables?

    2. What you think, how long is the creation running? Usually it should not need very long, depends of course on many factors, size of table, hardware, disc setup etc. etc.

    3: Maybe most important, why did you create an index, for which statements, what are other statements on thsi table, are they executed very often, what index do they => maybe they use they use the new index, which is subboptimal.

    4. Maybe Hardware - setup problem ...

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 18, 2007 at 09:35 AM

    Hi,

    Its(sys slows down) not related to creation of indexes.

    Becasue once if you create the index the sys takes or occupy memory what exactly original table have, say expmple mara table have 100 mb once if you create index on mara it will occupy the same memory ie 100 mb and all these things take care by BASIS people. it is better to go basis guys and ask them abt these issues.

    Regards,

    Vijay

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 18, 2007 at 01:29 PM

    If the indexes were still being created, it would show up in SM50.

    However, that probably wouldn't slow the entire system down. It's possible, that because of the new indexes, programs that use the primary index against these tables are getting confused and doing full table scans against them.

    You can update the database statistics. That may help.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 19, 2007 at 12:13 AM

    Thank you all for your responses. Points awarded.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.