Page 167 - DCAP508_DATABASE_ADMINISTRATION

Unit 11: Monitoring SQL Server for Performance

11.7 Summary Notes

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).

Optimal performance comes from minimal response times and maximum throughput as
a result of efficient network traffic, disk I/O, and CPU time.

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
There are some Microsoft tools that can help monitor your database performance such as
SQL Profiler, system monitor, error log, Current activity window, sp_who, sp_lock,
sp_spaceused, sp_monitor, DBCC statements, etc.
Microsoft® SQL Server provides a comprehensive set of tools for monitoring events in
SQL Server. Your choice of tool will depend on the type of monitoring and the events to be
monitored.

11.8 Keywords

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).
Response Time: Response time is measured as the length of time required for the first row of the
result set to be returned to the user in the form of visual confirmation that a query is being
processed.

SQL Profiler: SQL Profiler enables you to monitor server and database activity
System Monitor: System Monitor enables you to monitor server performance and activity using
predefined objects and counters or user-defined counters to monitor events.

Throughput: Throughput is a measure of the total number of queries handled by the server
during a given time.

11.9 Review Questions

1. Why do you need to monitor performance of sql server?

2. What are the various factors which affect the performance of SQL server?
3. Explain in detail the monitoring process of sql server.
4. What are the different criteria for evaluating performance?
5. Explain how would you a choose a monitoring tool in SQL server?
6. Write short notes on following:

(a) Simple Network Management Protocol (SNMP)
(b) Built-in functions

LOVELY PROFESSIONAL UNIVERSITY 161