Rebuilding indexes to improve the performance of SQL Server and Oracle vCenter Server databases
search cancel

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

book

Article ID: 310479

calendar_today

Updated On:

Products

VMware vCenter Server

Issue/Introduction

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

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
       


Environment

VMware vCenter Server 5.1.x
VMware vCenter Server 4.1.x
VMware vCenter Server 4.0.x
VMware vCenter Server 5.0.x
VMware vCenter Server 5.5.x

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


Additional Information


Backing up and restoring vCenter Server 4.x and 5.0
After restarting the vCenter Server, Stored Procedures stop responding in the database causing unexpected results
インデックスを再構築して SQL Server および Oracle vCenter Server データベースのパフォーマンスを改善する
重新构建索引以提高 SQL Server 和 Oracle vCenter Server 数据库的性能

Attachments

2009918_rebuild.zip get_app
2009918_rebuild_51.zip get_app