Loading

×Sorry to interrupt
CSS Error

Refresh

Cookie Preference Center

General Information on cookies

When you visit our website, we use cookies to ensure that we give you the best experience. This information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience on the site and the services we are able to offer. Further information can be found in our

Cookie Policy.

Strictly Necessary

Strictly Necessary

Always Active

Strictly Necessary

Strictly necessary cookies are always enabled since they are essential for our website to function. They enable core functionality such as security, network management, and website accessibility. You can set your browser to block or alert you about these cookies, but this may affect how the website functions. For more information please visit www.aboutcookies.org or www.allaboutcookies.org.

Cookie Details‎

Performance

Performance

Performance

Performance cookies are used to analyze the user experience to improve our website by collecting and reporting information on how you use it. They allow us to know which pages are the most and least popular, see how visitors move around the site, optimize our website and make it easier to navigate.

Cookie Details‎

Please confirm your settings by refreshing the page.

Functional

Functional

Functional

Functional cookies help us keep track of your past browsing choices so we can improve usability and customize your experience. These cookies enable the website to remember your preferred settings, language preferences, location and other customizable elements such as font or text size. If you do not allow these cookies, then some or all of these services may not function properly.

Cookie Details‎

Advertising

Advertising

Advertising

Advertising cookies are used to send you relevant advertising and promotional information. They may be set through our site by third parties to build a profile of your interests and show you relevant advertisements on other sites. These cookies do not directly store personal information, but their function is based on uniquely identifying your browser and internet device.

Cookie Details‎

Advertising Cookies

Consent Leg.Interest

Select All Vendors

Select All Vendors

Select All Hosts

Select All

Clear Filters

Information storage and access

Apply

Confirm My Choices Reject All Allow All

Skip to main content

Products and Accounts

Support

Knowledge

Communities

Success

Learning

My Subscriptions

Loading

Knowledge Base

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

Last Updated: 3/20/2024Categories: How toTotal Views: 52256Language:
Chinese (Simplified)JapaneseSpanishEnglish

Subscribe to get an email
newsletter with new updates

Please provide article feedback:

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

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:

Log in to Microsoft SQL Query Analyzer as an administrator.

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.

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.

Log in to Microsoft SQL Server Management Studio as an administrator.

Right-click the database that VirtualCenter is using.

Click New Query.

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.

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:

Log in to Microsoft SQL Query Analyzer as an administrator.

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.

Click Execute Query.

To perform a defragmentation of the performance data indexes on a Microsoft SQL 2005 database:

Log in to Microsoft SQL Server Management Studio as an administrator.

Right-click the database that VirtualCenter is using.

Click New Query.

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.

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:

Log in to Microsoft SQL Query Analyzer as an administrator.

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.

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.

Log in to Microsoft SQL Server Management Studio as an administrator.

Right-click the database that VirtualCenter is using.

Click New Query.

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.

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:

Log in to Microsoft SQL Query Analyzer as an administrator.

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.

Click Execute Query.

To perform a defragmentation of the performance data indexes on a Microsoft SQL 2005 and 2008 database:

Log in to Microsoft SQL Server Management Studio as an administrator.

Right-click the database that VirtualCenter is using.

Click New Query.

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.

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.

Related Information

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

Detectable by VMware SkylineTM

Actions

Copy

link to clipboard
copied!

Print

Language:
Chinese (Simplified)JapaneseSpanishEnglish

Attachments

Additional Resources

KB
•
End Of General Availability of the Free vSphere Hypervisor (ESXi 7.x and 8.x) (2107518)

KB
•
PowerCLI を使用した vSphere データストアとの間のファイルのコピー (2001041)

Results 1-2 of 2

Ask The Community

Get answers quickly from VMware experts in the community

Post Subject

CONTINUE IN COMMUNITIES

Related Products:

VMware VirtualCenter

VMware vCenter Server

Related Versions:

VMware VirtualCenter 2.5.x

VMware VirtualCenter 2.0.x

VMware vCenter Server 5.0.x

VMware vCenter Server 4.1.x

VMware vCenter Server 4.0.x

Take Our Survey

Was this article helpful?

YES

NO

Copyright © 2005-2024 Broadcom. All Rights Reserved. The term “Broadcom” refers to Broadcom Inc. and/or its subsidiaries.

Terms of Use

Your California Privacy Rights

Privacy

Accessibility

Cookie Settings

Support Assistant