2,187
edits
Difference between revisions of "Maintenance of MS SQL Database (vCentre)"
Jump to navigation
Jump to search
m
Maintenance of MS SQL Database (vCentre) (view source)
Revision as of 11:13, 1 November 2016
, 11:13, 1 November 2016Typo fix
(Initial creation - content from vCentre page) |
m (Typo fix) |
||
| (4 intermediate revisions by the same user not shown) | |||
| 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> | |||
For tables without an index (shows <code>NULL</code> in IndexName in the results), rebuild the whole table, so for example | |||
<source lang="sql"> | |||
USE VCDB // Update with your database name | |||
ALTER TABLE VPX_STAT_COUNTER REBUILD | |||
</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 75: | ||
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 91: | ||
# 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 114: | ||
# 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. | ||