Defragmenting VMware VirtualCenter or vCenter Server performance data indexes on a Microsoft SQL database
search cancel

Defragmenting VMware VirtualCenter or vCenter Server performance data indexes on a Microsoft SQL database

book

Article ID: 310804

calendar_today

Updated On:

Products

VMware vCenter Server

Issue/Introduction

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

Due to the database schema enhancements made in vSphere 5.1.x and later versions, defragmentation of the vCenter Server database is no longer required.


Symptoms:




Environment

VMware VirtualCenter 2.5.x
VMware vCenter Server 4.1.x
VMware vCenter Server 4.0.x
VMware vCenter Server 5.0.x
VMware VirtualCenter 2.0.x

Resolution

Note: In vSphere 5.1 and 5.5, defragmenting is no longer required due to changes in the VPX_HIST_STAT table structure.

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 take a backup prior to performing any kind of database maintenance. If you have experienced DB administrators you can do the tasks online. For more information on stopping the VMware VirtualCenter Server service, see Stopping, starting, or restarting the vCenter Server service (1003895).
Perform these steps 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 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 output 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 take a backup prior to performing any kind of database maintenance. If you have experienced DB administrators you can do the tasks online. For more 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 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 output 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 are 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.

Note: With the new enhancements and design changes made in the vCenter Server 5.1 database and later versions, these steps are no longer applicable or required on this release.

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 also do these steps on a SQL Express database, 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 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 output 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 take a backup prior to performing any kind of database maintenance. If you have experienced DB administrators you can do the tasks online. For more 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 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 output similar to the following for each of the 4 tables:

    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 are 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.


Additional Information

Desfragmentación de índices de datos de rendimiento en las bases de datos Microsoft SQL
VirtualCenterのパフォーマンスデータインデックスをMicrosoft SQL データベースでデフラグするについて
对 Microsoft SQL 数据库的 VirtualCenter 性能数据索引执行碎片整理

Powered by Wolken Software