SQL Server 2008 R2: Gathering Performance Data

Overview/Description
In order to keep SQL Server 2008 R2 running as efficiently as possible, SQL Server 2008 R2 provides a variety of performance tuning and optimization utilities. Among these utilities are the SQL Server Profiler and the Database Engine Tuning Advisor. The SQL Server Profiler allows you to use trace files and logs to analyze your database’s performance. The Database Engine Tuning Advisor allows you to analyze a database’s workload and effectively configure the physical design structure of databases based on your analysis. SQL Server 2008 R2 offers the ability to view the actual or planned execution plan for queries to ensure transactions are executed with the most efficient path possible. Another set of diagnostic tools provided by SQL Server 2008 R2 are the Dynamic Management Views (DMVs), which help you monitor the health of a server and diagnose problems that may be occurring. This course demonstrates how to view execution plans for transactions and how to use SQL Server Profiler to monitor the databases. It also demonstrates how to tune a database using a workload in Database Engine Tuning Advisor tool and how to use DMVs and catalog views to view system metadata. This course is one of a series in the SkillSoft learning path that covers the objectives for the Microsoft exam: 70-433 TS: Microsoft SQL Server 2008, Database Development. Passing this exam will earn the learner Microsoft Certified Technology Specialist (MCTS): SQL Server 2008, Database Development certification.

Target Audience
IT professionals who have jobs in which database administration is their main area of responsibility, or who work in environments in which databases are central to their job roles. These job roles include • IT generalists who work in environments in which SQL Server 2008 R2 is part of an overall solution • IT professionals who manage systems that run SQL Server 2008 R2 • IT professionals who work with third-party applications that are built on SQL Server 2008 R2 • Developers who build applications that use SQL Server 2008 R2

Prerequisites
The prerequisites for the exam are that candidates should have one to two years of experience using Microsoft SQL Server 2008, SQL Server 2008 R2, or two to three years of experience using a previous version of SQL Server

Expected Duration (hours)
1.5

Lesson Objectives

SQL Server 2008 R2: Gathering Performance Data

recognize how to view execution plans in SQL Server 2008 R2

recognize how to use SQL Server Profiler

recognize how to use the Database Engine Tuning Advisor

recognize how to use DMVs

recognize how to use catalog views

collect performance data in SQL server 2008 R2