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 StudioUSE [vcenter database name] GO
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..Use VIM_VCDB
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')
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….