VCenter deadlock

SQL Error logs were reporting deadlock from VMWare VCENTER.  I previously decided not to touch this db, since it’s third party.  But I was pretty tired of deadlocks in my SQL error log.  

The deadlocks have been on the VPX_PROPERTY_BULLETIN table.

I tried the following to remove them:

1. This one is a new index suggested on this site.  

CREATE INDEX mrdenny_OBJECT_MOID ON dbo.VPX_PROPERTY_BULLETIN
([OBJECT_MOID])
INCLUDE ([GEN_NUMBER])
WITH (FILLFACTOR=50, ONLINE=OFF, PAD_INDEX=ON)

2.  The second is one I came up with.  Just alter an already existing index to include the remaining columns.  This will prevent the SELECT from making a bookmark lookup to the clustered index, which may avoid the deadlock.

CREATE INDEX [VPXI_BULLETIN_GEN_IDX] ON [dbo].[VPX_PROPERTY_BULLETIN]
( [GEN_NUMBER] )
INCLUDE(MOD_TIME, OPERATION_TYPE)
WITH FILLFACTOR = 90, DROP_EXISTING

Good luck!

Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s