VMware: Defragmenting VirtualCenter performance data indexes on a Microsoft SQL database (1003990)

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.

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: The VirtualCenter Server service must be shut down prior to performing any kind of database maintenance. For information on stopping the VirtualCenter Server service, see Stopping, starting, or restarting the VirtualCenter 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 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 the following:

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: The VirtualCenter Server service must be shut down prior to performing any kind of database maintenance. For information on stopping the VirtualCenter Server service see: Stopping, starting, or restarting the VirtualCenter 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 the following:

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 is displayed, perform the steps in the verifying index fragmentation section of this article to review the results of the defragmentation.

VirtualCenter 2.5.x

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 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: The VirtualCenter Server service must be shut down prior to performing any kind of database maintenance. For information on stopping the VirtualCenter Server service see: Stopping, starting, or restarting the VirtualCenter 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 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:

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 is displayed, perform the steps in the verifying index fragmentation section of this article to review the results of the defragmentation.

Check out the complete knowledge base article after the jump

Source:

1003990

About Author