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

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:

• Backup your vCenter Server database before proceeding. For more information, see Backing up and restoring vCenter Server 4.x and 5.0 (1023985).
• These steps must be performed against the vCenter database and not the Primary .

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 数据库的性能