Virtualization Pro

Mar 11 2008   3:55PM GMT

VirtualCenter 2.5 database index defragmentation

Rick Vanover Rick Vanover Profile: Rick Vanover

I, like many virtualization administrators, have worked very hard to get the VMware virtual environment set up and running as expected. Now, one of my main tasks is to make sure that we do not do anything to adversely effect server performance. A good place to start in this regard is the VirtualCenter (VC) database. That being said, the VC database is critically important to a successful ESX implementation, so do not do anything that is not advised by VMware documentation or support services. Let’s discuss index defragmentation in particular here when using Microsoft SQL server 2000 for the VC database.

Index defragmentation on statistics

I will save you some work in what to look for in determining which tables will need index defragmentation – statistics. While we all like the statistics and graphing options available in the VMware Infrastructure Client and virtual appliances that may use the table, there can be a great amount of data in that table and it can quickly become fragmented. A fragmented index in a database is similar to a fragmented file system where the ordering of an index is not in the order of the index.

In my VC 2.5 environment, the VPX_HIST_STAT1 table is the heavy hitter. For this database maintenance, I’m going to start with the white paper entitled “VirtualCenter Database Maintenance” available from the VMware website. Here there is a command to check your current fragmentation level:

USE
GO
DBCC SHOWCONTIG (VPX_HIST_STAT1)
GO

I have modified the command to use the table name, as the white paper is VC 2.0 based on the table name, whereas this example is on VC 2.5. The result will look something like the following:

DBCC SHOWCONTIG scanning 'VPX_HIST_STAT1' table...
Table: 'VPX_HIST_STAT1' (800721905); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned................................: 505458
- Extents Scanned..............................: 78097
- Extent Switches..............................: 457307
- Avg. Pages per Extent........................: 7.4
- Scan Density [Best Count:Actual Count].......: 22.34% [113183:905308]

- Logical Scan Fragmentation ..................: 3.81%
- Extent Scan Fragmentation ...................: 0.47%
- Avg. Bytes Free per Page.....................: 187.2
- Avg. Page Density (full).....................: 97.69%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The key takeaway is the Scan Density percentage. The white paper advises that a number close to 100% is good, meaning that the table index in the example above is quite fragmented. The white paper goes on to identify two correction levels for improving the index. Index defragmentation and, more aggressively, rebuild are the standard options to address the index. If the scan density after a index defragmentation does not do enough to improve the index, database admins will have to begin the rebuild process. A caveat: rebuilding requires VC downtime to perform the database maintenance.

By comparison, here the same command on the VPX_EVENT table. This table is busy, but not near as much as the statistics table:

DBCC SHOWCONTIG scanning ‘VPX_EVENT’ table…
Table: ‘VPX_EVENT’ (36195179); index ID: 1, database ID: 16
TABLE level scan performed.
- Pages Scanned…………………………..: 594
- Extents Scanned…………………………: 86
- Extent Switches…………………………: 132
- Avg. Pages per Extent……………………: 6.9
- Scan Density [Best Count:Actual Count]…….: 66.39% [75:133]

- Logical Scan Fragmentation ………………: 6.73%
- Extent Scan Fragmentation ……………….: 98.84%
- Avg. Bytes Free per Page…………………: 150.5
- Avg. Page Density (full)…………………: 98.14%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This table is in better shape, but is much smaller than the statistics table.

Configure statistics logging level

As virtualization administrators, one safeguard we can perform is to limit the logging levels within the VMware Infrastructure Client. To access the logging levels select Administration menu, then VirtualCenter Management Server Configuration, then Statistics. Here you want to limit the number of high level logging to keep the VPX_HIST_STATx tables in check:

Statistics Level Configuration

In selecting which level works best for your environment, be sure to identify any monitoring tools or virtual appliances that may read the selected tables. Also be sure to benchmark your database size and index fragmentation to see if you gain any improvements. Identifying the parts of the entire VMware Infrastructure environment that you can keep in maintenance mode will make your job as a virtualization administrator much easier.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: