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
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] )
WITH FILLFACTOR = 90, DROP_EXISTING