Best practices for multiple VMware vCenter Server database instances running on the same SQL server

Products

• VMware vCenter Server

Issue/Introduction

This article provides information on best practices for improving database performance when multiple vCenter Server databases are running on the same SQL server.

VMware vCenter Server database performance issues may include:

• Aggressive growth of table sizes
• Longer execution times for rollup jobs, indexing, and database backup operations
• Tables lock intermittently
• Database tuning methods are ineffectual
• Several vCenter Server database instances run on the same SQL server

Environment

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

Cause

Database performance can be affected by multiple vCenter Server databases in the same SQL instance. This performance issue may be caused by rollup jobs created from each vCenter Server database generating locks and overlapping.

Resolution

To resolve this issue, these changes are recommended:

1. Implement new rollup jobs (one combined, one per vCenter Server).
2. Disable daily, weekly, and monthly rollup jobs:
   
   • Create a new rollup job per vCenter Server
   • Add a step for each stored procedure of database with Type Transact-SQL script:
   • Past day stats rollup
   • Delete past day rolled up records
   • Past week stats rollup
   • Delete past week rolled up records
   • Delete usage stats rollup
   • Past month stats rollup
   • Delete past month rolled up records
3. Schedule rollup jobs so they do not overlap. Set a five minute gap between each vCenter Server rollup job.
   
   An example schedule pattern:
   
   1am – vCenter1, 1.05am – vCenter2, 1.10am – vCenter3….. 2am – vCenter1, 2.05am – vCenter2
   
   
   
4. Alter the rollup job schedules again by not interfering with the weekly index maintenance job that runs on the weekend.

Note: To dramatically reduce the chances of database locks, combine VMware default rollup jobs into one job.