SQL Server with Mr. Denny

Jul 10 2013   7:00AM GMT

Preventing Locking, Blocking and Deadlocks in the vCenter database

Denny Cherry Denny Cherry Profile: Denny Cherry

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

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: