As hopefully everyone that is using VMware’s vSphere in either data center knows VMware’s vCenter runs off of a Microsoft SQL Server database (by default). Now as good as they guys at VMware are at building a virtualization platform there database leave a little to be desired. I’ve identified a couple of indexes which when created against the VMware vSphere 5.0 database will improve the health of the SQL Server database.
The first index is on the dbo.VPX_PROPERTY_BULLETIN database. On the system that I happen to be looking at this week not having this index caused the SQL Server to scan this table 6977 times since the database was last restarted about 7 days before I began looking at the system. This table on this SQL Server only contains about 3000 records, but this system is pretty small. Just 4 hosts, 4 data stores and about 100 guests at the moment. The larger this table is the more impact not having this query will have.
CREATE INDEX mrdenny_OPERATION_TYPE ON dbo.VPX_PROPERTY_BULLETIN
([OPERATION_TYPE])
INCLUDE ([OBJECT_MOID], [GEN_NUMBER])
WITH (FILLFACTOR=70, ONLINE=OFF, PAD_INDEX=ON)
The nice thing about this index is that is also fills the requirements of another index which is needed by the SQL Server.
The second index to create is built on the same table, but on different columns. While the query which needs this index is run MUCH less often, SQL estimates (on this system at least) that adding it will improve the query performance by 69.94%. In my mind that’s very much worth it, even if the query is only being run a few times a week.
CREATE INDEX mrdenny_OBJECT_MOID ON dbo.VPX_PROPERTY_BULLETIN
([OBJECT_MOID])
INCLUDE ([GEN_NUMBER])
WITH (FILLFACTOR=50, ONLINE=OFF, PAD_INDEX=ON)
The third index that I’ve identified which needs to be created is against the VPX_LIC_USAGE table. This table has something to do with the licensing and the size of the table on your system will vary. This vCenter system has over 16k rows in the table but this system has only been installed for a couple of months at this point. As your vSphere installation ages it appears that this table will continue to grow and grow. Best I can tell there’s a couple of rows entered into this table every hour for each host in the farm. Needless to say this table will grow quite large when you’ve got a large VMware vSphere farm.
CREATE INDEX mrdenny_SAMPLE_TIMESTAMP ON dbo.VPX_LIC_USAGE
([SAMPLE_TIMESTAMP])
INCLUDE ([SAMPLE_TS])
WITH (FILLFACTOR=100, ONLINE=OFF)
As you look at these three indexes which I recommend that you create on your vSphere database you’ll notice that there is an ONLINE=OFF flag. If your vCenter database is running the Enterprise Edition of SQL Server then you can change that on ONLINE=ON which will allow the indexes to be created online instead of causing blocking while the indexes are being created. If you don’t have the Enterprise edition of SQL Server then you can’t create in the indexes online. There should be no impact to the vCenter system if you create the indexes while the system is up and running. The worse that will happen is that queries which are already running slowly will run a little slower than normal.
You’ll notice that I’ve listed this indexes to all start with mrdenny. This is so that these indexes can be easily identified as coming from my blog (in case the next admin wonders where they are from) and so that you never have to worry about the index names colliding with names that VMware would try and use.
Denny
One Response