IM Manager Database Indexes Use a Large Amount of Space

Article:TECH93679  |  Created: 2009-01-28  |  Updated: 2011-07-13  |  Article URL http://www.symantec.com/docs/TECH93679
NOTE: If you are experiencing this particular known issue, we recommend that you Subscribe to receive email notification each time this article is updated. Subscribers will be the first to learn about any releases, status changes, workarounds or decisions made.
Article Type
Technical Solution

Product(s)

Subject

Issue



A large portion of used space in the database is used by indexes. Typically the indexes for the messages table are up to two thirds (2/3) of the total space used for the messages table.


 


Cause



IM Manager is not efficiently creating indexes.


Solution



Symantec is aware of this issue.  This article is updated as more information is available.  Subscribe to the article to receive updates.


Workaround

Use one of the following workarounds:

1. Get the status of the index for the messages table

The messages table is the table that stores the messages for the IM Manager database. This is the table is the most likely have index fragmentation and/or size issues. To get information about the status of this table's index follow these steps:
 

             1. Open SQL Query Analyzer or SQL Studio Management tool and open a new SQL Query
             2. In the Query Editor enter in the follow command:

            DBCC SHOWCONTIG (messages)

             The results of this command will show a similar result:

      DBCC SHOWCONTIG scanning 'messages' table...
      Table: 'messages' (2073058421); index ID: 1, database ID: 21
      TABLE level scan performed.
      - Pages Scanned................................: 2569
      - Extents Scanned..............................: 329
      - Extent Switches..............................: 339
      - Avg. Pages per Extent........................: 7.8
      - Scan Density [Best Count:Actual Count].......: 94.71% [322:340]
      - Logical Scan Fragmentation ..................: 2.14%
      - Extent Scan Fragmentation ...................: 98.78%
      - Avg. Bytes Free per Page.....................: 237.1
      - Avg. Page Density (full).....................: 97.07%
      DBCC execution completed. If DBCC printed error messages, contact your system administrator.

      This result shows that the Logical Scan Fragmentation is 2.14%. High values for logical scan fragmentation can lead to degraded performance of index scans. Consider defragmenting indexes with 20 percent or more logical fragmentation.

      This result shows that the Average Page Density is 97.07%. Low values for Average Page Density can result in more pages that must be read to satisfy a query. Reorganizing the pages so they have a higher page density can result in less I/O to satisfy the same query. Generally, tables have a high page density after initial loading of data, and page density may decrease over time as data is inserted, resulting in splits of leaf pages.

      There are 2 methods to reduce fragmentation and increase page density, DBCC INDEXDEFRAG and DBCC REINDEX

       

2. Using DBCC INDEXDEFRAG



DBCC INDEXDEFRAG allows you to rebuild a specific index. Similar to using DBCC DBREINDEX, you do not have to know about the underlying table structure; however, with DBCC INDEXDEFRAG you cannot rebuild all indexes with a single statement. You must run DBCC INDEXDEFRAG once for each index you want to defragment.

Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG is an online operation; therefore, the table and indexes are available while the index is being defragmented.

To use the index defrag procedure follow these steps:
1. Open SQL Query Analyzer or SQL Studio Management tool and open a new SQL Query

                2. In the Query Editor enter in the follow command:

             DBCC INDEXDEFRAGE (database_name,messages)
                   In this example, replace database_name with the name of the IM Manager database, this this typically IMManagerDB.

 

 

3. Using DBCC DBREINDEX


DBCC DBREINDEX can be used to rebuild one or more indexes for a specific table. DBCC DBREINDEX is an offline operation. While this operation is running, the underlying table is unavailable to users of the database. DBCC DBREINDEX rebuilds indexes dynamically. You do not have to know anything about the underlying table structure, nor any PRIMARY KEY or UNIQUE constraints; these are preserved automatically during the rebuild. DBCC DBREINDEX completely rebuilds the indexes, so it restores the page density levels to the original fillfactor (default); or you can choose another target value for the page density.

NOTE: In addition to making the table unavailable, performing the rebuild requires adequate free space in the data file(s). With not enough free space in the data file(s), DBCC DBREINDEX may be unable to rebuild the indexes, or the indexes may be rebuilt with logical fragmentation values above zero. The amount of free space needed varies and is dependent on the number of indexes being created in the transaction. A good guideline is: Required free space = 1.2 * (average row size) * (number of rows).

To use the index defrag procedure follow these steps. It recommended to do this during a maintainence window or system down-time when users will not be impacted:
1. Open SQL Query Analyzer or SQL Studio Management tool and open a new SQL Query

                          2. In the Query Editor enter in the follow command:

                 DBCC REINDEX (messages)

       





 





 


Supplemental Materials

SourceETrack
Value1496070


Legacy ID



2009042810413354


Article URL http://www.symantec.com/docs/TECH93679


Terms of use for this information are found in Legal Notices