Search the VMware Knowledge Base (KB)
View by Article ID

Rebuilding indexes to improve the performance of SQL Server and Oracle vCenter Server databases (2009918)

  • 129 Ratings

Symptoms

  • SQL execution times are extended and take a long time to complete.
  • You see gaps in host performance chart data when connected to the vCenter Server.
  • You do not see gaps in performance data when connected directly to the same host.
  • In the vpxd.log file , you see entries similar to: 

    [VdbStatement] SQL execution took too long: BEGIN process_temptable0_proc; END; [2010-12-10 05:42:47.685 04476 warning 'App'] [VdbStatement] Execution elapsed time: 20402 ms
    [VdbStatement] SQL execution took too long: BEGIN process_temptable1_proc; END; [2010-12-10 05:47:47.538 02004 warning 'App'] [VdbStatement] Execution elapsed time: 20272 ms
    [VdbStatement] SQL execution took too long: BEGIN process_temptable2_proc; END; [2010-12-10 05:52:47.762 01000 warning 'App'] [VdbStatement] Execution elapsed time: 20510 ms


    Note: vpxd logs are located at %ALLUSERSPROFILE%\Application Data\VMware\VMware VirtualCenter\Logs.
    • In Windows 2003 – C:\Documents and Settings\All Users\Application Data\VMware\VirtualCenter\logs
    • In Windows 2008 – C:\ProgramData\VMware\VMware VirtualCenter\Logs

Purpose

This article provides steps to rebuild the vCenter Server database indexes as part of regular scheduled database maintenance. 

Cause

This issue is caused by data being deleted from the vCenter Server 5.0 database hist_stat tables through a purge script or a reduction in statistic levels.

In a vCenter Server 5.1 and 5.5 database, hist_stat tables are not problematic and are not rebuilt in the attached .sql script. The attached 5.1 script will rebuild the sample_time tables and is valid also for vCenter Server 5.5.

Resolution

To resolve this issue, rebuild the vCenter Server database indexes.

Note: To rebuild the vCenter Server database indexes:
  1. Download and extract the .sql files from the 2009918_rebuild.zip file attached to this article.

    Note: For a vCenter Server 5.1 and 5.5 database, download and extract the .sql files from the 2009918_rebuild_51.zip file attached to this article.

  2. Connect to the vCenter Server database, for example using Management Studio for SQL Server or SQL*Plus for Oracle.
  3. Execute the .sql file to create the REBUILD_INDEX stored procedure:

    • Oracle: rebuild_indexes_oracle.sql or rebuild_indexes_oracle_51.sql
    • SQL Server: rebuild_indexes_sql.sql or rebuild_indexes_sql_51.sql

  4. Execute the stored procedure for either Oracle or SQL Server that was created in the previous step:

    execute REBUILD_INDEX

See Also

Update History

04/04/2013 - Updated with 5.1 .sql script zipped attachment. 11/26/2013 - Added vCenter Server 5.5 information.

Attachments

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

  • 129 Ratings

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)




Please enter the Captcha code before clicking Submit.
  • 129 Ratings
Actions
KB: