Preventing Locking, Blocking and Deadlocks in the vCenter database

Published On: 2013-07-10By:

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).

image

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

Contact the Author | Contact DCAC

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers    VMWare Partner
Microsoft Certified Master    Microsoft MVP
Share via