Create database maintenance plans in MS SQL Server 2005 using SQL Server Integration Services (SSIS)
Last Updated January 22, 2009
The Symantec Endpoint Protection Manager (SEPM) is not performing as well as desired, and interaction with the SQL server is suspected as a bottleneck. How to improve the performance of the SQL SERVER 2005 database? How to ensure that any improvements seen as a result of maintenance do not degrade over time?
SQL server maintenance decisions are ultimately the responsibility of an enterprise's own database administrator, but Symantec Technical Support has seen that SEPMs which use MS SQL 2005 for their database perform best when the following recommended maintenance tasks are configured. The document below provides information on how to use SSIS, a component of MS SQL 2005, to schedule a regular database maintenance plan.
Create a database maintenance plan from scratch
You’ll find Maintenance Plans under the Management tab in the server object explorer window in SQL Server Management Studio. (Figure A) Right-clicking the folder gives you the option of creating a new maintenance plan from scratch or creating one via the wizard. Select the New Maintenance Plan option to create one from scratch. (Figure B)
Creating a new maintenance plan brings up a familiar looking SSIS interface with several maintenance plan tasks. These tasks are also available for use in regular SSIS projects.
As part of my maintenance plan, we want to create a full backup of all of my all databases on one database instance. The Backup Database task will be the task we will use to accomplish this.
The Backup Database task gives us the option to back up: all databases on the instance; all system databases; all user databases; or specific databases. For this maintenance plan, We want to back up all databases on the instance. (Figure C And D)
Below is the Backup Database properties window. Our plan is to make a full backup of all databases and place the .bak files on C:\SQLDatabaseBackup directory.
We want to rebuild the indexes in them. These tasks can be interchangeable in terms of the order in which they execute. We can back up the databases before the rebuild so that we know we have a good working backup copy of the databases in case some tragic error occurs during the rebuild process.
Rebuilding indexes resorts and defragments indexes on database tables for views to improve their efficiency when sorting or searching. RebuildIndex Task will be the task we will use to accomplish this (Figure E).
Sort results in tempdb
For an index to retrieve results effectively, it must remain in sorted order. When an index is rebuilt, it must resort the data in the index. This resorting of data is typically done in the database in which the index resides. You now have the option to sort these indexes in the tempdb database; this has advantages and drawbacks. If the tempdb on my system is on a different set of disks than my user databases, it may be quicker to sort the index in the tempdb database; however, this requires the index rebuild to use more disk space. If space is not an issue for your system, it might not be a bad idea to play around with this option to see if it speeds your rebuild time.
Below is my almost complete maintenance plan. Notice the red failure precedence from the Backup Database Task and Rebuild Index Task to the Send Alert task. This precedence will cause my Send Alert task to execute only if one of the tasks encounters a failure. (Figures F and G)
All that is left to do is schedule the maintenance plan so that it will run. We want our backups to run daily at midnight, and we want the SQL Agent job to run daily at midnight. (typically like to run full backups and rebuild indexes during off-peak hours of the day.)