SQL Server 2008 Database Monitoring and Performance

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.

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

Expected Duration
150 minutes

Description
To keep SQL Server 2008 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 using counters, which monitor specific aspects of the server for performance issues. SQL Server 2008 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 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, 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.

Objective

SQL Server 2008 Profiler and Database Tuning

  • 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 database
  • Using Resource Governor and Performance Studio

  • 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
  • Monitoring Performance and Resolving Locks

  • recognize how to configure System Monitor to monitor SQL Server 2008 performance
  • recognize how locking is used to secure data integrity in a SQL Server 2008 database
  • recognize how to track a deadlock
  • recognize how to resolve a blocked process
  • MONTHLY SUBSCRIPTION

    $129/month
     

    ANNUAL SUBSCRIPTION

    $1295/year

    Multi-license discounts available for Annual and Monthly subscriptions.