Microsoft SQL Server has several routine maintenance functions that can help ensure that your application maintains optimum performance. In many environments, a database or system administrator often performs these functions; however, these functions can easily be automated in most cases. This section focuses on how these maintenance functions can be automated using the administrative tools and capabilities provided by SQL Server. Although these topics fall out of the scope of mainstream application development, when used effectively, they provide benefits that are just as important to the overall performance of the system.
The frequency and overall utilization of maintenance tasks is dependent on your application's and customer's environment needs. The following discussion presents a brief overview of each task, along with a strategy for automating or embedding the task within the context of your application.
SQL Server provides the ability to perform both database and transaction log backups that can allow you to recover your database in the event of failure. The frequency of backups depends on your application's and customer's needs; although most SQL Server environments perform these backups on a daily or weekly basis. A systems or database administrator typically performs backups, but the process can easily be automated by embedding the backup task in your application or by using the SQL Server event scheduler to perform them automatically.
SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics when making decisions about which index(es) to use in query processing. The optimization of your application's queries depends on the accuracy of the distribution steps that are maintained by the server. These statistics are not updated automatically. Therefore, if there are significant changes in the key values in your index(es), run UPDATE STATISTICS on that index. This is also applicable for instances when a great deal of data in the indexed column(s) has been added, changed, or removed.
It is recommended that you run UPDATE STATISTICS on your database once a week. However, the frequency may be more or less depending on the needs of your application and customer's environment. Executing UPDATE STATISTICS statement can be easily automated by scheduling a task to run it against the database(s) involved with your application.
SQL Server provides a facility using the database consistency checker statement (DBCC) that checks the logical and physical consistency of a particular database. Regardless of the frequency of database and transaction backups, it is recommended that you perform DBCC CHECKDB, NEWALLOC, CHECKCATALOG, and TEXTALLOC (if your database maintains text data) on a regular basis. Using these DBCC statements consistently ensures that any database consistency issues can be identified and resolved quickly.
Executing DBCC statements can be automated easily. Embedding these statements in your application reduces the routine maintenance tasks that your customer must perform. The example presents a task that is used to execute DBCC NEWALLOC against the pubs database on a weekly basis:
exec msdb..sp_addtask 'DBCC NEWALLOC for Pubs',
@subsystem = 'TSQL',
@databasename = 'pubs',
@enabled = 1,
@freqtype = 8,
@activestartdate = 970101, @activeenddate = 99991231,
@activestarttimeofday = 0, @activeendtimeofday = 235959,
@loghistcompletionlevel = 2,
@emailcompletionlevel = 0,
@command = 'DBCC NEWALLOC(pubs) WITH NO_INFOMSGS'
go
The task above is called 'DBCC NEWALLOC for Pubs' and is added to the msdb database where the SQL Executive Service Scheduler can reference it. The task executes DBCC NEWALLOC with the NO_INFOMSGS parameter against the pubs database on a weekly (@freqtype = 8) basis. The NO_INFOMSGS parameter ensures that only noninformational (severity levels 11 and higher) messages are returned from the DBCC statement. The task is set up to generate an error log any time there is a problem associated with executing the statement so that its output does not have to be examined after each time it is run.
When a stored procedure or trigger is created, a normalized tree for the procedure or trigger is generated and saved. This normalized query tree contains information about tables and views that are referenced within the context of the stored procedure or view. When the procedure or trigger is subsequently executed, its saved execution tree is retrieved, and based on it, an optimized execution plan is created and stored in the procedure cache. Under certain conditions, the execution plan in the procedure cache can become invalid; as a result the stored procedure or trigger needs to be recompiled.
As a database is changed by events such as adding or dropping indexes, the original query plans used to access its tables should be optimized again by recompiling them. This optimization process happens automatically the first time a stored procedure is run after SQL Server has been restarted, or if an underlying table referenced by the stored procedure changes. However, new indexes added to tables referenced by stored procedures are not analyzed until the stored procedure is recompiled. Therefore, a stored procedure cannot take advantage of items such as new indexes until it is recompiled. The system stored procedure sp_recompile forces a recompilation of a procedure the next time it is run. The usage of sp_recompile can easily be automated by creating and scheduling a task that executes it on a regular basis.
Unlike many nonrelational systems, relational indexes are not considered part of the logical database design. Indexes can be dropped, added, and changed without affecting the database schema or application design in any way other than performance. Efficient index design is paramount to achieving good SQL Server performance. For these reasons, you should not hesitate to experiment with different indexes and indexing options.
Because of the volume of data inserted into tables referenced by your application, you may introduce a fill factor within your database. The fill factor is used at index creation time to determine how much free space to allocate within each index page. Whether you choose to use a fill factor to reduce page splitting during high volume inserts or to help evenly distribute the table and index data, the fill factor is not maintained dynamically. As data is inserted into your database, the index pages become full again, eliminating the space provided by the fill factor. Therefore, occasionally you may have to reintroduce the fill factor to retain the benefits it provides.
You can reintroduce the fill factor by rebuilding the indexes within the database. Indexes can be rebuilt by dropping and re-creating them or by using the DBCC DBREINDEX statement. The DBCC DBREINDEX statement rebuilds a single or all index(es) for a table. A database administrator, depending on the number of indexes and tables involved, often performs this task. However, the entire process can be automated by creating and scheduling a task to perform the index drop/creation or the DBCC DBREINDEX statement.
SQL Server provides Sqlmaint.exe, a command-line utility that can be used to perform routine database maintenance tasks. Sqlmaint.exe is a SQL-DMO application that has a variety of different options that you can use to embed many server and database maintenance tasks in your application. The power and flexibility of Sqlmaint.exe allows you to perform tasks from any computer on which it is registered. This allows you to embed many of the server and database maintenance functions in portions of your application that do not have to reside on the same computer as SQL Server.
Sqlmaint.exe has several features that can be used:
Sqlmaint.exe can be executed on demand as a stand-alone executable or can be scheduled and executed as a task using the SQLExecutive service scheduler. This allows you to configure, schedule, and perform most of your database maintenance tasks in a flexible manner by adjusting to the needs of your application and customer's environment. The example demonstrates how Sqlmaint.exe can be scheduled and executed as a task:
exec msdb..sp_addtask 'Pubs Weekly Automated Maintenance',
@subsystem = 'CmdExec',
@databasename = 'pubs',
@enabled = 1,
@freqtype = 8,
@freqinterval = 1,
@freqsubinterval = 0,
@freqrecurrencefactor = 1,
@activestartdate = 19970101,
@activeenddate = 99991231,
@activestarttimeofday = 0,
@activeendtimeofday = 235959,
@emailoperatorname = sysadmin,
@loghistcompletionlevel = 2,
@emailcompletionlevel = 0,
@command = 'SQLMAINT.EXE -D pubs –To sysadmin -CkDB -CkAl -CkTxtAl -CkCat -UpdSts -RebldIdx 10 -BkUpDB C:\MSSQL\BACKUP -BkUpMedia DISK -DelBkUps 4 -Rpt C:\MSSQL\LOG\pubs_maint.rpt',
@cmdexecsuccesscode = 0
go
The sample code creates a task called 'Pubs Weekly Automated Maintenance' that executes Sqlmaint.exe on a weekly basis to perform the maintenance tasks mentioned previously. The reports documenting the status of both the task and the execution of each maintenance option are sent to the sysadmin mail account by SQLMail to be monitored and recorded. This demonstrates the ease of use and flexibility provided by Sqlmaint.exe and DMO.