VCM relies heavily on its SQL databases for operation. You must update the default settings in order to optimize SQL Server performance. We will create a maintenance plan for VCM databases.
We will perform the fine-tuning at three different levels, as detailed in the following subsections.
To ensure that VCM runs at peak performance and requires little operator intervention during its lifecycle, set up a routine maintenance plan. Take a look at the VCM Administration Guide.
Open SQL Server Management Studio and connect to the VCM SQL Server instance. Then, follow these steps:
Right-click on the SQL instance that you installed and select Properties.
In the Select a page area, select Database Settings.
Configure the following settings:
Default index fill factor: Set the fill factor to
80%in order to keep 20% free space available in each index page
Tip
Note: This sets a percentage value for the amount of free space in each index page when the page is rebuilt. Set the fill factor to
80%to keep 20% free space available in each index page. This setting is part of the SQL maintenance plan wizard. If you configure the default fill factor using this setting, keep space free in an index when you run a maintenance plan.Recovery interval (minutes): Set the value to
5
Tip
Note: This configures the approximate amount of time that SQL Server takes to run the recovery process. The default setting is
0, which causes SQL Server to adjust this value and base the values on the historical operation of the server. In large environments, the recovery interval can affect the overall performance of VCM. Because VCM constantly updates how it interacts with SQL Server to process activities whose intervals differ, such as an inspection request and a compliance run, the server expends a lot of time constantly adjusting this value. By setting the recovery interval to5minutes, SQL Server no longer needs to tune this value.Click on OK to save the settings.
To ensure that VCM runs at peak performance and requires little operator intervention during its lifecycle, you must set up a routine maintenance plan. VCM relies heavily on its SQL databases for operation.
The maintenance plan uses the automated maintenance functions on the SQL Server instances that host the VCM database.
On the VCM SQL Server instance, follow these steps:
Click on Start.
Select All Programs | Microsoft SQL Server {version} | SQL Server Management Studio.
Expand the Management folder, right-click on Maintenance Plans, and select Maintenance Plan Wizard.
On the Maintenance Plan Wizard page, click on Next.
On the Select Plan Properties page, enter a maintenance plan name, select Single schedule for the entire plan or no schedule, and click on Change.
On the Job Schedule Properties - Maintenance Plan page, set the scheduling properties to run the maintenance plan when the SQL Server is idle or has low usage.
Click on OK to return to the Select Plan Properties page, and click on Next.
On the Select Maintenance Tasks page, select the following maintenance tasks and click on Next:
Check Database Integrity
Rebuild Index
Update Statistics
Clean Up History
On the Select Maintenance Task Order page, order the maintenance tasks and click on Next.
On the Define Database Check Integrity Task page, define how the maintenance plan will check database integrity:
On the Define Rebuild Index Task page, define how the maintenance plan will rebuild the index:
On the Define Update Statistics Task page, define how the maintenance plan will update database statistics:
On the Define History Cleanup Task page, define how the maintenance plan will clean up historical data from the SQL Server machine, and click on Next:
Select Backup and restore history.
Select SQL Server Agent job history.
Select Maintenance plan history.
Set the cleanup task to remove historical data older than 4 months.
On the Select Report Options page, save a report of the maintenance plan actions:
Select Write a report to a text file.
Select a folder for the report and click Next.
On the Complete the Wizard page, verify your selections in the Maintenance Plan Wizard summary, expand the selections to view the settings, and click on Finish.
When the Maintenance Plan Wizard progress has finished, verify that each action was successful.