Page 164 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes 11.4 Factors Affecting Performance

The following areas affect the performance of SQL Server:

System resources (hardware)
The Microsoft Windows NT® 4.0 and Windows® 2000 operating systems
Database applications

Client applications
Network
Before these areas can be monitored, you must know what level of performance is reasonable
given normal working conditions. To do this, establish a server performance baseline by
monitoring Microsoft® SQL Server™ performance at regular intervals, even when no problems
occur.

Troubleshooting Problems

You can monitor the following areas to troubleshoot problems:
SQL Server stored procedures or batches of SQL statements submitted by user applications.

User activity, such as blocking locks or deadlocks.
Hardware activity, such as disk usage

11.5 Tools and Techniques for Monitoring

There are some Microsoft tools that can help monitor your database performance:

1. SQL Profiler: SQL Profiler enables you to monitor server and database activity (for example,
number of deadlocks, fatal errors, tracing stored procedures and Transact-SQL statements,
or login activity). You can capture SQL Profiler data to a SQL Server table or a file for later
analysis, and also replay the events captured on SQL Server, step by step, to see exactly
what happened. SQL Profiler tracks engine process events, such as the start of a batch or a
transaction.

2. System Monitor: System Monitor enables you to monitor server performance and activity
using predefined objects and counters or user-defined counters to monitor events. System
Monitor (Performance Monitor in Microsoft Windows NT® 4.0) collects counts rather
than data about the events (for example, memory usage, number of active transactions,
number of blocked locks, or CPU activity). You can set thresholds on specific counters to
generate alerts that notify operators. System Monitor primarily tracks resource usage,
such as the number of buffer manager page requests in use.

System Monitor works only on Microsoft Windows® 2000 and can monitor (remotely or
locally) an instance of SQL Server on Windows NT 4.0 or Windows 2000 only.

3. Current activity window (SQL Server Enterprise Manager): Graphically displays
information about processes running currently on an instance of SQL Server, blocked
processes, locks, and user activity. This is useful for ad hoc views of current activity.
4. Error Logs: Contain additional information about events in SQL Server than is available
elsewhere. You can use the information in the error log to troubleshoot SQL Server-
related problems. The Windows application event log provides an overall picture of

158 LOVELY PROFESSIONAL UNIVERSITY