2,187
edits
m (Sstrutt moved page Maintenance (vCentre) to Maintenance of MS SQL Database (vCentre)) |
(Reworked for SQL2008+) |
||
Line 1: | Line 1: | ||
== SQL | == Index Fragmentation == | ||
The vCenter database's indexes become fragmented over time, this is especially true of the performance statistic history tables, even if you have a [[[MS_SQL#Daily_Maintenance_Plan|Daily Maintenance Plan]] setup. | |||
=== Show Index Fragmentation === | |||
Run the script below in order to show fragmented tables, note that you need to replace the name of your vCenter database. | |||
<source lang="sql"> | |||
USE VCDB // Update with your database name | |||
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, | |||
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, | |||
indexstats.avg_fragmentation_in_percent | |||
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats | |||
INNER JOIN sys.indexes ind | |||
ON ind.object_id = indexstats.object_id | |||
AND ind.index_id = indexstats.index_id | |||
WHERE indexstats.avg_fragmentation_in_percent > 60 | |||
ORDER BY indexstats.avg_fragmentation_in_percent DESC | |||
</source> | |||
=== Defragment Indexes === | |||
To defragment an individual table index use the following... | |||
<source lang="sql"> | |||
USE VCDB // Update with your database name | |||
ALTER IndexName ON TableName REORGANIZE | |||
</source> | |||
so for example | |||
<source lang="sql"> | |||
USE VCDB // Update with your database name | |||
ALTER INDEX PK_VPX_HIST_STAT4_106 ON VPX_HIST_STAT4_106 REORGANIZE | |||
</source> | |||
To generate a list of commands to run, use the following... | |||
<source lang="sql"> | |||
USE VCDB // Update with your database name | |||
SELECT 'ALTER INDEX ' + ind.name + ' ON ' + OBJECT_NAME(ind.OBJECT_ID) + ' REORGANIZE' | |||
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats | |||
INNER JOIN sys.indexes ind | |||
ON ind.object_id = indexstats.object_id | |||
AND ind.index_id = indexstats.index_id | |||
WHERE indexstats.avg_fragmentation_in_percent > 60 | |||
ORDER BY indexstats.avg_fragmentation_in_percent DESC | |||
</source> | |||
=== Pre SQL 2008 === | |||
''Summary of maintenance info from http://www.vmware.com/files/pdf/vc_microsoft_sql_server.pdf'' | ''Summary of maintenance info from http://www.vmware.com/files/pdf/vc_microsoft_sql_server.pdf'' | ||
=== Index Fragmentation === | ==== Show Index Fragmentation ==== | ||
'''This maintenance procedure can be performed inline (ie without stopping the DB or VirtualCenter)''' | '''This maintenance procedure can be performed inline (ie without stopping the DB or VirtualCenter)''' | ||
# Log into the SQL database using Query Analyser | # Log into the SQL database using Query Analyser | ||
Line 26: | Line 70: | ||
You may occasionally encounter a deadlock issue that causes the INDEXDEFRAG session to terminate. If the session terminates in this way, there is no adverse impact on the database, and the database continues running normally. You can issue the INDEXDEFRAG command again and the operation will resume from where it left off. | You may occasionally encounter a deadlock issue that causes the INDEXDEFRAG session to terminate. If the session terminates in this way, there is no adverse impact on the database, and the database continues running normally. You can issue the INDEXDEFRAG command again and the operation will resume from where it left off. | ||
=== Index Reindexing === | ==== Index Reindexing ==== | ||
'''This maintenance procedure can only be performed with the database offline, the VirtualCenter Server Service must be shutdown''' | '''This maintenance procedure can only be performed with the database offline, the VirtualCenter Server Service must be shutdown''' | ||
# Perform query to check the level of fragmentation (may take 2 mins or so to run). Create and execute the following query; | # Perform query to check the level of fragmentation (may take 2 mins or so to run). Create and execute the following query; | ||
Line 42: | Line 86: | ||
# Once complete, restart the VirtualCenter Server Service | # Once complete, restart the VirtualCenter Server Service | ||
== Migrate SQL2000 to SQL2005 == | |||
This should be fairly painless (so long as your DBA knows his stuff!). | This should be fairly painless (so long as your DBA knows his stuff!). | ||
Line 65: | Line 109: | ||
# Login using the VI Client and ensure everything looks good (clusters, ESX's, VM's, Alarms, Guest Customisations etc) | # Login using the VI Client and ensure everything looks good (clusters, ESX's, VM's, Alarms, Guest Customisations etc) | ||
==Other Tasks== | |||
Its also possible to delete old data from the database, this is achieved by running a script against the database, see http://kb.vmware.com/kb/1000125 for further info. | Its also possible to delete old data from the database, this is achieved by running a script against the database, see http://kb.vmware.com/kb/1000125 for further info. | ||