Industrial strength vCenter database maintenance (for SQL Express too!)
Over time, vCenter does a huge number of reads and writes to it’s database. Most of these come from the performance stats, and depending on the level of stats you have turned on, the number of writes can be very substantial. After a while, even with a small-to-medium infrastructure, the database will need some love to keep it working like it should. VMware has some recommendations about maintenance on your database, specifically here if you are using SQL Server. However, their recommendations only do a little to help if you have a very badly fragmented database.
You see, dbcc indexdefrag only defragments the index. This means that it will reorganize the pages in the index so that they are near to each other, but if you have a lot of partially filled pages, it still won’t do a whole lot of good. Don’t misunderstand me, it will help as it means that an index scan will perform significantly better since the scan will not require as much I/O work, but there are usually very few pages freed, which means that there could still be a large number of (potentially unnecessary) pages in the index.
Here is the example output that VMware provides in the KB article linked above:
DBCC SHOWCONTIG scanning 'VPX_HIST_STAT1' table... Table: 'VPX_HIST_STAT1' (805577908); index ID: 3, database ID: 6 LEAF level scan performed. - Pages Scanned................................: 173933 - Extents Scanned..............................: 21904 - Extent Switches..............................: 79082 - Avg. Pages per Extent........................: 7.9 - Scan Density [Best Count:Actual Count].......: 27.49% [21742:79083] - Logical Scan Fragmentation ..................: 38.79% - Extent Scan Fragmentation ...................: 84.35% - Avg. Bytes Free per Page.....................: 5174.1 - Avg. Page Density (full).....................: 36.07% DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Notice the “Avg. Page Density (full)” item…each page is only 36% full, this means that there is a huge amount of white space in each page, which under the right circumstances, can be ok, but in this case, we probably don’t want that. Generally speaking, if there is a high number of writes to the table (an consequentially the index) then you want a lower page density (a.k.a. fill factor), a higher number of reads would mean a higher fill factor is appropriate.
vCenter, by my estimation, does quite a few writes to the database, but it also does a fair amount of reads (every time you view the performance tab, every time those graphs refresh, it’s a series of reads). Additionally, the roll-up stats act as a bit of an equalizer, as they do a significant amount of deletions in the tables.
What all this means, to me anyway, is that a medium-high fill factor (70-80%) seems to fit the bill. So, 36% suddenly seems quite low, and that 36% is only marginally improved with the index defrag.
Well, there happens to be a command available that rebuilds the indexes: alter index rebuild. What this means is that the current indexes (and all associated pages on disk) are deleted, after which the database engine will read each row in the table and rebuild the index, making it contiguous on disk, and we can specify the fill factor to what we think is appropriate.
Fortunately, some kind soul over on MSDN posted a very nice script that will check the status of all indexes on all tables in a particular database. To use the script, open SQL Server Management Studio (SSMS), connect to your SQL Server instance and open a new query window. Enter a use statement for the database:
USE VIM_VCDB GO
Then copy the SQL in the post made by N8WEI into the SSMS query window below the above USE statement. There are a couple of parameters that you can set: the fill factor, the index defrag threshold, the index rebuild threshold, and the “report only” value. Fill factor is just that, I set it to 70%. The index defrag threshold (“reorg_frag_thresh”) can stay relatively low (10% or less), the rebuild threshold (“rebuild_frag_thresh”) can stay at 30% (unless you want to force a rebuild on all indexes, in which case set reorg_frag_thresh to 1 and rebuild_frag_thresh to 2). The last option (“report_only”) determines if the script will actually take any action. Leave it set to 1 for the moment.
By leaving report_only to 1, it will only check the indexes, it will not perform any action upon them. So when you press the “Execute” button (or F5), it will scan through the indexes for the database and check their fragmentation level. The result window will have a list of the indexes and the action to be taken.
Before you proceed, be aware of a couple of things. Doing an index defrag is transparent to the users (e.g. vCenter). If the database is very busy, it may seem a little slow while the defrag is occurring, but the database will still be usable. When an index rebuild is performed, the table is locked so that no action can be taken during the operation. This means that for the duration of the rebuild the table will be unavailable for read/write. This probably isn’t a big deal…I run the operation about once a month on all tables and it takes less than 60 seconds to complete. This means each table is only locked for a few seconds, at most, which is probably ok. vCenter updates each host’s performance stats every 20 seconds, and it shouldn’t fail the insert operation if it has to wait a second or two. If you are concerned about it, simply shutdown the vCenter service on the server and it will stop performing queries.
With that said, change report_only to 0 (zero), and hit the button. It will do it’s thing (the first time may take a minute or two depending on your stats level), and when you’re done your indexes should be fresh and new.
If you want a warm and tingly, be sure to run the VMware recommended dbcc showcontig on the history tables before and after executing the index rebuild(s). You should see near 99.9%+ scan density and a page density within a few hundredths of a percent of the value specified.
The end result is that performance is significantly improved, even for operations that don’t involve the performance stats (though those are especially noticeable). I run the above script, with settings to rebuild indexes, once a month. I also run the script with settings to defrag the indexes once a day, which helps to prevent as many page splits and, consquentially, leads to less fragmentation. (Hint: Use the SQL Server Agent to schedule the operations.)
Oh, and this works with SQL Express, though you may have to turn on connecting via TCP before you can use SSMS to execute queries (and there’s no SQL Server Agent, so you’ll have to use scheduled tasks).
And, if you are using SQL Express, you can also use SSMS Express (even if you are using the full SQL Server you can use SSMS Express). The SQL Server Management Stuido 2008 Express is a very nice (and very functional) application, and it’s freely available from Microsoft (::watches for sky to begin falling::).
