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..


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

USE [vcenter database name] GO
dbcc showcontig([tablename],[indexname])

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..

dbcc showcontig ('VPX_HIST_STAT3','PK_VPX_HIST_STAT3')

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….

dbcc indexdefrag('[databasename]’,'[tablename]’,'[indexname]')

so, after filling in the values we get…

dbcc indexdefrag ('VIM_VCDB','VPX_HIST_STAT3','PK_VPX_HIST_STAT3')
which then returns….

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….
  • Pingback: Migrating vCenter to a new server |

  • Guest

    what about Oracle installations?

    • mwpreston

      not my forte… 🙂 Sorry

  • mwpreston

    I’ve seen it be a night and day change, and I’ve seen it not do very much. Sometimes you need to do a complete index rebuild which would require you to stop i/o into the database…I’ve never had to do this with vCenter DB, but I have with others. I think doing it online is fine….and I’ve never done a snapshot per se, but backing up your database before messing around with it is always a good idea 🙂 Thanks for the comment!

  • bortoelnino

    Thanks alot for this – made a big difference in my environment!

  • Sam TheMan

    Hi, I run into the error below in SQL2012. Any idea? Thanks!

    Unable to process object ID 566293077 (object ‘VPX_HIST_STAT1’) because this DBCC command does not support objects of this type.