This two-day workshop will teach you how to determine and troubleshoot performance issues using Microsoft SQL Server 2005. The primary focus of this workshop is to teach the overall process of troubleshooting. It includes establishing monitoring standards and baselines, determining performance thresholds, and focusing the investigation on specific issues.

Elements of this syllabus are subject to change.

This course incorporates materials from the Official Microsoft Learning Products (OMLP):

• 2790 - Troubleshooting and Optimizing Database Servers Using Microsoft SQL Server 2005

What You'll Learn

• How to determine and troubleshoot performance issues using Microsoft SQL Server 2005
• The overall process of troubleshooting, including establishing monitoring standards and baselines, determining performance thresholds, and focusing the investigation on specific issues

Who Needs to Attend

Database administrators who have three or more years of on-the-job experience administering SQL Server database solutions in an enterprise environment who want to learn troubleshooting and optimizing database servers using Microsoft SQL Server 2005

Prerequisites

• Three or more years of on-the-job experience administering SQL Server database solutions in an enterprise environment
• Working knowledge of SQL Server 2005 architecture including indexing, SQL execution plans, and SQL Server basic configuration
• Basic monitoring and troubleshooting skills
• Working knowledge of the operating system and platform
• Basic understanding of server architecture such as CPU and memory utilization and disk input/output (I/O).
• Basic knowledge of application architecture
• Understanding of Transact-SQL syntax and programming logic
• Basic knowledge of Microsoft Windows networking
• Familiarity with SQL Server 2005 features, tools, and technologies

Follow-On Courses

There are no follow-ons for this course.

Course Outline

1. Building a Monitoring Solution for SQL Server Performance Issues

• Narrowing Down a Performance Issue to an Environment Area
• Guidelines for Monitoring Database Servers and Instances by Using Profiler and Sysmon
• Guidelines for Auditing and Comparing Test Results

2. Troubleshooting Database and Database Server Performance Issues

• Narrowing Down a Performance Issue to a Database Object
• How Profiler Can Help Narrow a Search to a Specific Issue
• How the SQLdiag Tool Can Be Used to Analyze Outputs

3. Optimizing the Query Performance Environment

• The Methodology of Optimizing a Query Environment
• The Query Performance Troubleshooting Process

4. Troubleshooting SQL Server Connectivity Issues

• The Methodology of Troubleshooting SQL Server Connectivity Issues.
• Areas to Troubleshoot for Common Connectivity Issues.
• What Are SQL Server 2005 Endpoints?

5. Troubleshooting SQL Server Data Issues

• The Methodology of Troubleshooting SQL Server Data Issues
• The Process of Troubleshooting Data Integrity Issues
• How Torn Pages Can be Resolved Using a Single-Page Restore

6. Troubleshooting SQL Server Data Concurrency Issues

• The Methodology of Troubleshooting Concurrency Issues
• What Are SQL Server Latches?
• Activity: Choosing a Blocking Monitoring Solution

Labs

Lab 1. Building a Monitoring Solution for SQL Server Performance Issues

• Determining Which Indicators to Monitor
• Implementing a Monitoring Solution
• Auditing Monitoring Results to Identify Problem Areas

Lab 2. Troubleshooting Database and Database Server Performance Issues

• Analyzing Sysmon and Profiler Traces
• Analyzing a Profiler Trace by Using SQL Server Queries
• Determining Database Server Issues by Using SQLdiag.exe

Lab 3. Optimizing the Query Performance Environment

• Reviewing an Execution Plan for Clues to Poor Performance
• Performing Index Analysis by Using the Database Tuning Advisor (DTA)

Lab 4. Troubleshooting SQL Server Connectivity Issues

• Troubleshooting Server-Not-Found Issues
• Troubleshooting an Authentication Error Message
• Troubleshooting Endpoint Issues

Lab 5. Troubleshooting SQL Server Data Issues

• Troubleshooting and Repairing Torn Pages
• Troubleshooting a Data Issue

Lab 6. Troubleshooting SQL Server Data Concurrency Issues

• Identifying the Objects Involved in a Deadlock
• Identifying the Objects Involved in a Blocking Issue
• Determining Concurrency Issues by Using Latch Wait Types