Giving vCenter a kick in the rearend!! – Defragmenting your vCenter Database

I've seen this happen numerous times on a number of our VMware vCenter installations.  For the first few months(sometimes days)  vCenter Server is very snappy and responsive, when moving from page to page things come up instantly and moving between different statistics and metrics on performance graphs is veryquick.  Then, as time goes by, things begin to drag down a bit, tabs start to take a little bit longer to load when moving between them, performance graphs can throw exceptions and things just generally slow down.

Of course there are many issues that could be causing this to happen, but the most common that I have found is a fairly simple one, a neglected VMware vCenter DB that is full of fragmentation and in need of a little TLC.  I don't want to go into what exactly database/index fragmentation is, but a good read if you have the time (and interest) is here.  Also, VMware has released KB Article 1003990 as well, which covers off fragmentation within the vCenter DB.

And then there is also my explanation….

When I'm looking at tuning my vCenter DB, the tables that I find myself always defragmenting are as follows..

VPX_HIST_STAT1
VPX_HIST_STAT2
VPX_HIST_STAT3
VPX_HIST_STAT4
VPX_TOPN_PAST_DAY
VPX_TOPN_PAST_WEEK
VPX_TOPN_PAST_MONTH
VPX_TOPN_PAST_YEAR

As you may guess, these tables hold historical and rolled up performance statistics and data.  Since vCenter is always  collecting data (depending of course on your Intervals and durations) these tables are constantly being updated (New stats coming in, old stats going out).  Just as in file level defragmentation, the large number of writes, updates, and deletes causes some tables to become heavily fragmented.

I'm not going to go through defragmenting all of these as it is the same steps for each table/index.  For this purpose I'll just go through VPX_HIST_STAT3.  First off, to see the fragmentation inside a table just run the following command in SQL Server Management Studio

You can retrieve the names of the indexes either from  KB Article 1003990 or by expanding the Indexes folder in SSMS.  Essentially this command translates to..

In my example this returns the following stats.

To summarize these results for a VI Admin, the lines that you should really be looking at is 'Scan Density' and 'Logical Scan Fragmentation'.  In short, you want Scan Density to be as close to 100% as possible, and Logical Scan Fragmentation to be as close to 0% as possible.  To defrag the indexes in this table we use the following command….

so, after filling in the values we get…

As you can see we now have a lower Logical Scan Fragmentation and a higher Scan Density.  This is the expected result we want from the defragmentation.  Just repeat this step on all of the indexes and tables you want to defragment and you should be enjoying a much snappier, more responsive vCenter Server in no time.  Keep in mind, some smaller indexes and tables will always be fragmented and not much can be done to correct that issue.  Personally, I concentrate on the 8 tables listed above.  Keep these tables/indexes happy and you are well on your way….