Page 170 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Introduction

The goal of monitoring databases is to assess how a server is performing. Effective monitoring
involves taking periodic snapshots of current performance to isolate processes that are causing
problems, and gathering data continuously over time to track performance trends.
Ongoing evaluation of the database performance helps you minimize response times and
maximize throughput, yielding optimal performance. Efficient network traffic, disk I/O, and
CPU usage are key to peak performance. You need to thoroughly analyze the application
requirements, understand the logical and physical structure of the data, assess database usage,
and negotiate tradeoffs between conflicting uses such as online transaction processing (OLTP)
versus decision support

12.1 Benefits of Monitoring SQL Server for Performance

Microsoft SQL Server and the Microsoft Windows operating system provide utilities that allow
you to view the current condition of the database and to track performance as conditions change.
There are a variety of tools and techniques that can be used to monitor Microsoft SQL Server.
Understanding how to monitor SQL Server can help you:

Determine whether you can 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 required.

Evaluate user activity. For example, by monitoring users trying to connect to an instance
of SQL Server, you can determine whether security is set up adequately and test applications
or 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.
12.2 Monitoring in a Dynamic Environment

Monitoring is important because SQL Server provides a service in a dynamic environment.
Changing conditions result in changing performance. In your evaluations, you can see
performance changes as the number of users’ increases, user access and connection methods
change, database contents grow, client applications change, data in the applications changes,
queries become more complex, and network traffic rises. By using SQL Server tools to monitor
performance, you can associate some changes in performance with changing conditions and
complex queries. The following scenarios provide examples:
By monitoring the response times for frequently used queries, you can determine whether
changes to the query or indexes on the tables where the queries execute are required.

By monitoring Transact-SQL queries as they are executed, you can determine whether the
queries are written correctly and producing the expected results.

By monitoring users that try to connect to an instance of SQL Server, you can determine
whether security is set up adequately and test applications or development systems.
Response time is 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. Throughput is the
total number of queries handled by the server during a specified period of time.

164 LOVELY PROFESSIONAL UNIVERSITY