Page 161 - DCAP508_DATABASE_ADMINISTRATION

Sartaj Singh, Lovely Professional University
Unit 11: Monitoring SQL Server for Performance

Unit 11: Monitoring SQL Server for Performance Notes

CONTENTS
Objectives

Introduction
11.1 Need of Monitoring
11.2 Monitoring Process
11.3 Evaluating Performance

11.4 Factors Affecting Performance
11.5 Tools and Techniques for Monitoring
11.6 Choosing a Monitoring Tool
11.7 Summary

11.8 Keywords
11.9 Review Questions
11.10 Further Readings

Objectives

After studying this unit, you will be able to:

Understand the need of monitoring in a SQL server
Conceptualize the monitoring process
Learn about different criteria for evaluating performance

Know how to choosing a monitoring tool in SQL server
Introduction

Microsoft® SQL Server™ 2000 provides a variety of tools that can be used to monitor the
performance of an instance of SQL Server and the user activity that occurs in databases. Monitoring
allows you to determine whether your database application is working efficiently and as expected,
even as your application, database, and environment change. For example, as more concurrent
users use a database application, the load on SQL Server can increase. By monitoring, you can
determine whether the current instance of SQL Server or system configuration must be changed
to handle the increased workload, or whether the increased load is having no significant effect
on performance.

11.1 Need of Monitoring

Monitoring SQL Server allows you to:

Determine whether it is possible to improve performance. For example, by monitoring the
response times for frequently used queries, you can determine whether changes to the
query or indexes on the tables are necessary.

LOVELY PROFESSIONAL UNIVERSITY 155