Page 162 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Evaluate user activity. For example, by monitoring users attempting to connect to an
instance of SQL Server, you can determine whether security is set up adequately and test
applications and development systems. For example, by monitoring SQL queries as they
are executed, you can determine whether they are written correctly and producing the
expected results.
Troubleshoot any problems or debug application components, such as stored procedures.

11.2 Monitoring Process

Monitoring process consists of a set of procedures to monitor an application, an instance of SQL
Server, or the operating system environment (hardware and software). These Steps have been
briefly discussed below:

1. Determine your monitoring goals.
2. Choose the appropriate tool for the type of monitoring you will perform.
3. Use the tool to monitor SQL Server or the system environment and analyze the captured
data.
4. Identify the events to monitor: The events determine which activities are monitored and
captured. Your selection of events to monitor will depend on what is being monitored and
why. For example, when monitoring disk activity, it is not necessary to monitor SQL
Server locks.
5. Determine the event data to capture: The event data describes each instance of an event as
it occurs. For example, when monitoring lock events, you can capture data describing the
tables, users, and connections affected by the lock event. The following explains the process
involved in capturing event data and putting it to use.
i. Apply filters to limit the event data collected. Limiting the event data allows the system
to focus on the events pertinent to the monitoring scenario. For example, if you
want to monitor slow queries, you can use a filter to monitor only those queries
issued by the application that take more than 30 seconds to execute against a particular
database.

ii. Monitor (capture) events. Once enabled, active monitoring captures data from the
specified application, instance of SQL Server, or operating system. For example,
when disk activity is monitored using System Monitor (Performance Monitor in
Microsoft Windows NT® 4.0), monitoring captures event data such as disk reads and
writes and displays it to the screen.
iii. Save captured event data. Saving captured data allows you to analyze it at a later time
or even replay it using SQL Profiler. Captured event data is saved to a file that can be
loaded back into the tool that originally created the file for analysis. SQL Profiler
allows event data to be saved to a SQL Server table. Saving captured event data is
vital when creating a performance baseline. The performance baseline data is saved
and used when comparing recently captured event data to determine whether
performance is optimal.
iv. Create definition files containing the settings specified to capture the events. Definition files
include specifications about the events themselves, event data, and filters that are
used to capture data. These files can be used to monitor a specific set of events at a
later time without redefining the events, event data, and filters. For example, if you
want to monitor frequently the number of deadlocks and the users involved in

156 LOVELY PROFESSIONAL UNIVERSITY