As our VMware environments become larger and larger with more and more hosts and guests more thought needs to be given to the vCenter database that is typically running within a SQL Server database.
With the vCenter database running within Microsoft SQL Server (which is the default) their will be lots of locking and blocking happening as the queries which the vCenter server runs aggregates the data into the summary tables. The larger the environment the more data that needs to be aggregated every 5 minutes, hours, daily, etc.
Then problem here is that in order for these aggregations to run the source and destination tables have to be locked. This is normal data integrity within the SQL Server database engine.
Thankfully there is a way to get out of this situation. That is to enable a setting called Snapshot Isolation level for the vCenter database. This setting changes the way that SQL Server handles concurrency by allowing people to write to the database while at the same time allowing people to read the old versions of the data pages therefor preventing locks. The SQL Server does this by making a copy of the data page when it is being modified and putting that copy into the tempdb database. Any user that attempts to run queries against the original page will instead be given the old version from the tempdb database.
If you’ve seen problems with the vCenter client locking up and not returning performance data when the aggregation jobs are running, this will make these problems go away.
Turning this feature on is pretty simple. In SQL Server Management Studio simply right click on the vCenter database and find the “Allow Snapshot Isolation” setting on the options tab. Change the setting from False to True and click OK (this is the AdventureWorks2012 database, but you’ll get the idea).
If you’d rather change the settings via T-SQL it’s done via the ALTER DATABASE command shown below.
ALTER DATABASE [vCenter] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
Hopefully this will help fix some performance problems within the vCenter database.
Denny
