SQL Server 2008 R2: Database Monitoring and Performance

Target Audience
Expected Duration
Lesson Objectives
Course Number

To keep SQL Server 2008 R2 databases running as efficiently as possible, administrators use a variety of performance tuning and monitoring tools. The SQL Server Profiler enables administrators to use trace files and logs to analyze a database's performance. They can also use the Windows System Monitor to monitor SQL Server 2008 R2 using counters, which monitor specific aspects of the server for performance issues. SQL Server 2008 R2 includes the Activity Monitor, which provides an overview of the system's performance in real-time. And the Database Engine Tuning Advisor enables administrators to analyze a database's workload and then implement recommendations to provide better performance for the database. SQL Server 2008 R2 provides a Resource Monitor, which allocates specific resources, and a data collector, which collects specific data and stores it in a management data warehouse. In SQL Server 2008 R2, concurrency issues may arise that require the administrator to intervene by detecting the locking issue and possibly terminating a process that could be blocking a session from performing its task. Concurrency issues can arise when multiple users are trying to access the same data in a database. This course demonstrates how to use SQL Server Profiler, the Database Engine Tuning Advisor, Resource Monitor, and the data collector to monitor and tune the performance of the database. It also addresses how to use the Windows System Monitor, Activity Monitor, and how to detect and resolve locks. This course is one of a series in the SkillSoft learning path that covers the objectives for Microsoft SQL Server 2008 exam number 70-432. Passing this exam will earn the learner the Microsoft Certified Technology Specialist (MCTS): Microsoft SQL Server 2008, Implementation and Maintenance certification.

Target Audience
Professionals typically pursuing a career as a Database Administrator; individuals who don't work directly with SQL Server as part of their primary job but wish to show their breadth of technology experience, including developers and systems administrators.

One to two years of experience using SQL Server 2008, SQL Server 2008 R2, or two to three years of experience using a previous version of SQL Server

Expected Duration (hours)

Lesson Objectives

SQL Server 2008 R2: Database Monitoring and Performance

  • recognize how to create a trace in SQL Server Profiler
  • recognize how to replay a trace in SQL Server Profiler
  • recognize how the Database Engine Tuning Advisor functions
  • recognize how to use the Database Engine Tuning Advisor to tune a SQL Server 2008 R2 database
  • describe the purpose of SQL Server Utility
  • use SQL Server Utility
  • recognize the features of Resource Governor
  • recognize how to use Resource Governor to manage resource allocation
  • recognize the features of the Data Collector
  • recognize how to use Data Collectors to collect SQL Server information
  • recognize how to configure System Monitor to monitor SQL Server 2008 R2 performance
  • recognize how locking is used to secure data integrity in a SQL Server 2008 R2 database
  • recognize how to track a deadlock
  • recognize how to resolve a blocked process
  • use SQL Server’s monitoring and diagnostic tools to optimize database performance
  • Course Number: