See Also: Main_Page - Monitoring

Monitoring performance is an ongoing effort. In some environments, degraded performance can cause lost revenue and irate customers. In others, mild to moderate performance problems are acceptable, but they need to be resolved eventually. Regardless of the environment DBAs cannot monitor SQL Server performance on a 24 / 7 schedule. Fortunately there is a better way - making SQL Server monitor itself and notify the administrator when performance is less than acceptable. SQL Server jobs, operators and alerts help you do just that.

SQL Server operators are people that need to be notified when a certain performance condition occurs or when a particular job fails. Operators can be notified through a net-send message, email message or a pager message. Note that SQL Server Agent must be running in order for operators, jobs and alerts to work. In addition, to send messages to operators you must have configured SQL Mail. To use pager messages you must sign up for paging service and configure SQL Server to send notifications to the pager.

Alerts are pre-defined performance conditions that initiate the sending of notifications to the operator(s). For example, you could define an alert that emails the DBA when tempdb database becomes 80% full. Or you could setup an alert that pages the administrator when CPU utilization on the database server is greater than 90%. You can configure the alert to notify you repeatedly until the condition that triggered the alert no longer exists. Alternatively you can configure the alert to execute a job as a response to a performance condition. For example, if the transaction log becomes 90% full the alert could initiate a job that truncates the log.

A number of built-in alerts are available within SQL Server Enterprise Manager. Before these alerts can be useful, however, you must define the operator to notify and the ways of notifying the operator.

SQL Server jobs are Transact-SQL, command line, or ActiveX script routines that can be executed once or repeatedly according to a pre-defined schedule. Jobs can consist of multiple steps; you can also configure each step to call a different job step or quit the job execution at success or failure. The job can be configured to notify an operator through net-send, email or pager about the completion status, record the message in the Windows application event log and even to delete itself at completion. If you have configured multiple SQL Servers for remote management you can execute jobs on multiple remotely managed servers.

Jobs provide the flexibility to perform numerous activities on SQL Server without manual intervention. For example, you could schedule a time-consuming Transact-SQL script to run at night, during off-peak hours. Similarly you could run a job that kicks off a Data Transformation Services (DTS) package for importing or exporting data.

1. Ensure the general health of the database
    
2. Tune database performance

Jobs that fall into these categories should be set up on every production server. Since such jobs can be resource intensive you should monitor their execution at least a few times, determine the length of their execution and schedule them at a time when the performance impact on the overall system will be minimal.

The general health of the database can be examined by running maintenance DBCC statements. If you can afford a maintenance window (a timeframe when database is in a single-user mode), you should examine the health of the entire database by executing DBCC CHECKDB each night. DBCC CHECKDB examines allocation and structural integrity of all data and index pages in the database. If your database is large and you have multiple file-groups, you can execute DBCC CHECKFILEGROUP against the file-groups that are more likely to be modified during each day. The file-groups that are used pre-dominantly for read-only purposes can be examined less frequently, for example once a week or once every two weeks.

DBCC CHECKDB runs DBCC CHECKTABLE and DBCC CHECKALLOC for every table in the database and can be very time consuming. For databases upwards of 100 Gigabytes in size DBCC CHECKDB can take over an hour to complete. If you can't afford this much maintenance time you should execute DBCC CHECKALLOC against the entire database and then execute DBCC CHECKTABLE against individual tables. The tables that are mainly used for read-only activities can be examined less frequently than the ones that are constantly modified by INSERT, UPDATE and DELETE queries. Since DBCC CHECKDB performs an extensive check of all tables, if you use that command it is no longer necessary to execute DBCC CHECKTABLE on individual tables.

To examine the consistency of data within your user tables you might also wish to execute the DBCC CHECKCONSTRAINTS command. DBCC CHECKCATALOG checks data consistency within the system tables.

After you ensure the database consistency you should perform regular backups. You must have a valid full database backup at all times in case of a hardware failure or natural disaster. In addition you might also wish to perform differential and transaction log backups periodically, depending on your needs.

In addition to checking database integrity and performing backups you should also ensure that your database has appropriate amounts of space and it isn't using more space than it needs. You can shrink a database using the DBCC SHRINKDB command or you can shrink individual data or log files using DBCC SHRINKFILE.

Tuning database performance might involve numerous activities: maintaining indexes and updating statistics, partitioning tables, changing the database structure, deploying improvements to the existing stored procedures, and tweaking SQL Server configuration options.