Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 302

Introduction
Maintenance and Monitoring are essential parts of database administration and any
relational database has no exclusion from these tasks. There are various tools available
from the SQL Server product in addition to the tools from the Windows operating system. The
key aspects of SQL Server maintenance is to keep up the availability and achieve optimal
performance, thereby minimizing the time for users to obtain the results of queries and
provide out of the box scalability. In order to reduce manual intervention, monitoring will
help us to track the resource usage, network traffic, and transactions usage on the data
platform. For a DBA to improve the system performance, it is essential to line-up baseline and
benchmarking of the data platform.
Baseline: It is a series of measurements taken when a system is newly
configured or an existing system has been extensively reconfigured.
Benchmarking : It is an inclusive report of system performance that is
captured at multiple times and loads.
Monitoring involves taking periodic snapshots of the system state to isolate processes that are
causing problems and to gather information continuously over time to track a performance
trend. In practical terms, it is essential to record system performance against predefined
metrics, when the server becomes operational with a measurable load. This information is
beneficial to establish the baseline of the database server, which can indicate the areas in
need of tuning or reconfiguration. In addition to baseline setup, the information can supply
the benchmarking of the platform that will clarify how the system resources are used under
heavy load. Maintenance tasks help the system to keep up performance and scalability over
a period of time, and the tasks must be continued irrespective of database sizes due to the
changes in data. In turn, this is effective to define capacity planning and to identify the system
statistics at peak hour traffic and lean periods. By monitoring and maintaining the response
times for frequently used queries, we can determine whether the changes to the query or
indexes on the tables are required. It also helps us to determine whether security is set up
adequately and test the applications. Database Maintenance and Performance Monitoring are
two sides of the same coin that must flip on a regular basis. Both of these practices can keep
up with the baseline and benchmarking of the platform. This chapter covers all of the aspects
of maintaining and monitoring best practices with real world examples. The recipes will help
the DBAs to adopt best methods with available technologies to the database functionality at
peak performance.