Chapter 14. Monitoring and automation
In this chapter, we’ll cover
- Activity Monitor
- SQL Server Profiler
- Performance Monitor
- Maintenance plans
- SQL Server Agent jobs and alerts
This chapter is dedicated to the production DBA, whose role, among other things, includes monitoring SQL Server instances for abnormal activity, managing the response to failure conditions, and carrying out a number of proactive maintenance tasks. In large organizations, such DBAs are typically located within, or accessible to, a command center, whose role is to perform similar tasks for all supported infrastructure and applications on a 24/7 basis.
We’ll begin this chapter with coverage of a range of monitoring tools including Activity Monitor, SQL Server Profiler, and Performance Monitor. With a vast range of monitoring tools available, choosing the right tool for the job is an important skill; in addition to covering how these tools are used, we’ll touch on how they should not be used.
Our focus will then shift to the importance of automation and its role in reducing errors and achieving more within the limited hours of a day. We’ll cover maintenance plans and SQL Server Agent jobs before looking at setting up alerts for error and performance conditions.
In SQL Server 2005, we accessed Activity Monitor in SQL Server Management Studio by expanding Management and choosing Activity Monitor. This allowed us to view running processes as well as locks by process or object.