Knowledge Base

The VMware Knowledge Base provides support solutions, error messages and troubleshooting guides
 
Search the VMware Knowledge Base (KB)   View by Article ID
 

Defragmenting VirtualCenter performance data indexes on a Microsoft SQL database

Purpose

For troubleshooting or maintenance purposes it may be necessary to defragment the indexes on your Microsoft SQL database server. This article provides the steps perform a defragmentation of the indexes to increase performance of the database.

Resolution

Overview

Fragmentation of indexes occurs when the logical order of pages is different than the physical order on the disk. In VirtualCenter fragmentation occurs most noticeably due to the statistics collection and consolidation. 
 
When the indexes are excessively fragmented, performance of queries to the VirtualCenter database is slow.
 
Warning: If you do not have experienced DB administrators, shutdown the VMware VirtualCenter Server service and do a backup prior to performing any kind of database maintenance. If you have experienced DB administrators you can do the tasks online. For information on stopping the VMware VirtualCenter Server service, see Stopping, starting, or restarting the vCenter Server service (1003895).
 
Follow the steps below depending on the version of VirtualCenter that you are running:  

VirtualCenter 2.0.x

With VirtualCenter 2.0.x, the performance data is stored in a single table (vpx_hist_stat ) and fragmentation of the indexes can cause access slow access to the performance data. The procedure in this section explains how to verify if there is fragmentation in the indexes and the steps to defragment the indexes if necessary.  
 
Verifying performance data index fragmentation
 
To verify how fragmented the performance data indexes are on a SQL 2000 database:
  1. Log in to Microsoft SQL Query Analyzer as an administrator.
  2. In the New Query window type:

    Use <database>
    go 
    dbcc showcontig (VPX_HIST_STAT,VPXII_HIST_STAT)
    go

    where <database> represents the name of the database that is running VirtualCenter.

  3. Click Execute Query.
To verify how fragmented the performance data indexes are on a SQL 2005 and 2008 database:
 
Note: You can do these steps on a SQL Express DB as well, but you must download and install the Microsoft SQL Server Management Studio Express program from Microsoft's website to get the management piece.
  1. Log in to Microsoft SQL Server Management Studio as an administrator.
  2. Right-click on the database that VirtualCenter is using.
  3. Click New Query.
  4. In the New Query window type:

    Use <database>
    go 
    dbcc showcontig (VPX_HIST_STAT,VPXII_HIST_STAT)
    go

    where <database> represents the name of the database that is running VirtualCenter.

  5. Click Execute.
In the results window, you see information that looks similar to:
 
DBCC SHOWCONTIG scanning 'VPX_HIST_STAT' table...
Table: 'VPX_HIST_STAT' (805577908); index ID: 3, database ID: 6
LEAF level scan performed.
- Pages Scanned................................: 173933
- Extents Scanned..............................: 21904
- Extent Switches..............................: 79082
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 27.49% [21742:79083]
- Logical Scan Fragmentation ..................: 38.79%
- Extent Scan Fragmentation ...................: 84.35%
- Avg. Bytes Free per Page.....................: 5174.1
- Avg. Page Density (full).....................: 36.07%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 
The key pieces of information to determine fragmentation are Scan Density and Logical Scan Fragmentation. In the example, scan density is 27.49%. In an ideal environment the closer this number is to 100%, the better the database performs. For Logical Scan Fragmentation, the example shows 38.79%. The lower the percentage, the better the system performs. 
 
If either metric shows excessive fragmentation, the database is under performing and defragmentation can help to increase the performance of the system.
 
Performing a performance data index defragmentation
 
Warning: If you do not have experienced DB administrators, shutdown the VirtualCenter Server service and do a backup prior to performing any kind of database maintenance. If you have experienced DB administrators you can do the tasks online. For information on stopping the VirtualCenter Server service, see Stopping, starting, or restarting the vCenter Server service (1003895).
 
To perform a defragmentation of the performance data indexes on a Microsoft SQL 2000 database:
  1. Log in to Microsoft SQL Query Analyzer as an administrator.
  2. In the New Query type:

    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT', 'VPXII_HIST_STAT')
    go

    where <database> represents the name of the database that is running VirtualCenter.
  3. Click Execute Query.
To perform a defragmentation of the performance data indexes on a Microsoft SQL 2005 database:
  1.  Log in to Microsoft SQL Server Management Studio as an administrator.
  2. Right-click on the database that VirtualCenter is using.
  3. Click New Query.
  4. In the New Query window type:

    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT', 'VPXII_HIST_STAT')
    go

    where <database> represents the name of the database that is running VirtualCenter.

  5. Click Execute.
In the results window, you see information that looks similar to:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Pages Scanned Pages Moved Pages Removed
173941        63527       110406

This indicates that the indexes have been reorganized. When this message displays, perform the steps in Verifying performance data index fragmentation in this article to review the results of the defragmentation.  

vCenter Server 2.5.x, 4.x, and 5.0

As of VirtualCenter 2.5.x, the architecture of how performance data is processed and stored has changed and therefore the procedure is more complex.  The performance data is now stored in 4 separate tables (vpx_hist_stat1 to vpx_hist_stat4) and is much more efficient. The procedure in this section explains how to verify if there is fragmentation in the indexes and the steps to defragment the indexes if necessary.

Verifying performance data index fragmentation
 
To verify how fragmented the performance data indexes are on a SQL 2000 database:
  1. Log in to Microsoft SQL Query Analyzer as an administrator.
  2. In the New Query dialog, type:

    Use <database>
    go 
    dbcc showcontig (VPX_HIST_STAT1)
    dbcc showcontig (VPX_HIST_STAT2)
    dbcc showcontig (VPX_HIST_STAT3)
    dbcc showcontig (VPX_HIST_STAT4)
    go

    where <database> represents the name of the database that is running VirtualCenter.

  3. Click Execute Query.
To verify how fragmented the performance data indexes are on a SQL 2005 and 2008 database:
 
Note: You can do these steps on a SQL Express database as well, but you must download and install the Microsoft SQL Server Management Studio Express program from Microsoft's website to get the management piece.
  1. Log in to Microsoft SQL Server Management Studio as an administrator.
  2. Right-click on the database that VirtualCenter is using.
  3. Click New Query.
  4. In the New Query dialog, type:

    Use <database>
    go 
    dbcc showcontig (VPX_HIST_STAT1)
    dbcc showcontig (VPX_HIST_STAT2)
    dbcc showcontig (VPX_HIST_STAT3)
    dbcc showcontig (VPX_HIST_STAT4)
    go

    where <database> represents the name of the database that is running VirtualCenter.

  5. Click Execute.

    In the results window, you see information that looks similar to the following for each of the 4 tables: 

    DBCC SHOWCONTIG scanning 'VPX_HIST_STAT1' table...
    Table: 'VPX_HIST_STAT1' (805577908); index ID: 3, database ID: 6
    LEAF level scan performed.
    - Pages Scanned................................: 173933
    - Extents Scanned..............................: 21904
    - Extent Switches..............................: 79082
    - Avg. Pages per Extent........................: 7.9
    - Scan Density [Best Count:Actual Count].......: 27.49% [21742:79083]
    - Logical Scan Fragmentation ..................: 38.79%
    - Extent Scan Fragmentation ...................: 84.35%
    - Avg. Bytes Free per Page.....................: 5174.1
    - Avg. Page Density (full).....................: 36.07%
    DBCC execution completed. If DBCC printed error messages, contact your system administrator. 

    The key pieces of information to determine fragmentation are Scan Density and Logical Scan Fragmentation. In the example, scan density is 27.49%. In an ideal environment the closer this number is to 100% the better the database performs. For Logical Scan Fragmentation, the example shows 38.79%. The lower the percentage the better the system performs.  

    If either metric shows excessive fragmentation, the database is under performing and defragmentation can help to increase the performance of the system.

Performing a performance data index defragmentation

Warning: If you do not have experienced DB administrators, shutdown the VirtualCenter Server service and do a backup prior to performing any kind of database maintenance. If you have experienced DB administrators you can do the tasks online. For information on stopping the VirtualCenter Server service, see Stopping, starting, or restarting the vCenter Server service (1003895).
 
To perform a defragmentation of the performance data indexes on a Microsoft SQL 2000 database:
  1. Log in to Microsoft SQL Query Analyzer as an administrator.
  2. In the New Query dialog, type:

    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT1', 'PK_VPX_HIST_STAT1')
    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT2', 'PK_VPX_HIST_STAT2')
    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT3', 'PK_VPX_HIST_STAT3')
    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT4', 'PK_VPX_HIST_STAT4')
    go

    where <database> represents the name of the database that is running VirtualCenter.
  3. Click Execute Query.
To perform a defragmentation of the performance data indexes on a Microsoft SQL 2005 and 2008 database:
  1. Log in to Microsoft SQL Server Management Studio as an administrator.
  2. Right-click on the database that VirtualCenter is using.
  3. Click New Query.
  4. In the New Query dialog, type:

    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT1', 'PK_VPX_HIST_STAT1')
    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT2', 'PK_VPX_HIST_STAT2')
    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT3', 'PK_VPX_HIST_STAT3')
    dbcc indexdefrag ('<database>', 'VPX_HIST_STAT4', 'PK_VPX_HIST_STAT4')
    go

    where <database> represents the name of the database that is running VirtualCenter.

  5. Click Execute. 

    In the results window, you see information that looks similar to the following for each of the 4 tables:

    D    BCC execution completed. If DBCC printed error messages, contact your system administrator.
    Pages Scanned Pages Moved Pages Removed
    173941        63527       110406

    This indicates that the indexes have been reorganized. When this message displays, perform the steps in the Verifying performance data index fragmentation in this article to review the results of the defragmentation.

Tags

vCenter-Database-Health vcenter-slow-performance

Request a Product Feature

To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.

Feedback

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.
What can we do to improve this information? (4000 or fewer characters)
Actions
  • Bookmark Document
  • Email Document
  • Print Document
  • Subscribe to Document
  • Bookmark and Share