Troubleshooting and Optimizing Database Servers Using Microsoft® SQL Server™ 2005 Training Course
Microsoft Official Curriculum Course Number: 2790
| Unit 1 Building a Monitoring Solution for SQL Server Performance Issues |
| This unit provides an opportunity for the student to build a monitoring solution that will help to identify SQL Server performance issues. Students will design a baseline performance monitoring solution. |
| Lessons |
| 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 |
Labs
- Lab: 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
After Completing This Goal, Students will be Able To
- Explain the methodology of narrowing down a performance issue to a particular database environment area.
- Apply the guidelines for monitoring database servers and instances by using Profiler and Sysmon.
- Apply the guidelines for auditing and comparing test results.
- Determine which indicators to monitor.
- Implement a monitoring solution.
- Audit monitoring results to identify problem areas.
|
| Unit 2 Troubleshooting Database and Database Server Performance Issues |
| This unit provides an opportunity for students to troubleshoot SQL Server performance issues. Students analyze the sample monitoring output to determine the issue. This unit includes information on a new feature in SQL Server 2005 which allows students to automatically sync a Sysmon log and Profiler trace. It also allows students to load and perform analysis against a Profiler trace using SQL Server queries. Finally, it allows students to run SQLdiag.exe as an additional troubleshooting tool. |
| Lessons |
| 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 |
Labs
- Lab: 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
After Completing This Goal, Students will be Able To
- Explain the methodology of narrowing down a performance issue to a particular database environment object.
- Explain the use of SQLdiag tool to analyze outputs.
- Explain the use of Profiler to narrow a troubleshooting search to a specific issue.
- Analyze Sysmon and Profiler traces.
- Analyze Profiler traces using SQL Server queries.
- Determine performance issues by using SQLdiag.exe.
|
| Unit 3 Optimizing the Query Performance Environment |
| This unit gives students an opportunity to determine the database-level reasons for poor query performance, like bad indexes and outdated index column statistics. Students are provided with samples from a Profiler trace or a listing of poorly performing queries and directed to investigate possible reasons. |
| Lessons |
| The Methodology of Optimizing a Query Environment |
| The Query Performance Troubleshooting Process |
Labs
- Lab: Optimizing the Query Performance Environment
- Reviewing an Execution Plan for Clues to Poor Performance
- Performing Index Analysis by Using the Database Tuning Advisor (DTA)
After Completing This Goal, Students will be Able To
- Explain the methodology of optimizing the query environment.
- Describe the query performance troubleshooting process.
- Explain how Database Tuning Advisor can be used to troubleshoot the query environment.
- Review an execution plan for clues to poor performance.
- Perform index analysis by using DTA.
|
| Unit 4 Troubleshooting SQL Server Connectivity Issues |
| This unit explains the troubleshooting of common SQL Server problems. Examples include DNS issues, network authentication issues, and SQL Server 2005 endpoint issues. |
| Lessons |
| The Methodology of Troubleshooting SQL Server Connectivity Issues. |
| Areas to Troubleshoot for Common Connectivity Issues. |
| What Are SQL Server 2005 Endpoints? |
Labs
- Lab: Troubleshooting SQL Server Connectivity Issues
- Troubleshooting Server-Not-Found Issues.
- Troubleshooting an Authentication Error Message.
- Troubleshooting Endpoint Issues.
After Completing This Goal, Students will be Able To
- Explain the methodology of troubleshooting connectivity issues.
- Describe the areas to audit for common connectivity issues.
- Explain the SQL Server 2005 endpoints.
- Troubleshoot Server-Not-Found issues.
- Troubleshoot an authentication error message.
- Troubleshoot endpoint issues.
|
| Unit 5 Troubleshooting SQL Server Data Issues |
| This unit lets students troubleshoot issues at a data level. One exercise will be used to identify and recover a torn page. The second exercise is a business unit report which contains invalid data. The goal is for the Database Administrator to track down the reasons for the invalid data. |
| Lessons |
| 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 |
Labs
- Lab: Troubleshooting SQL Server Data Issues
- Troubleshooting and Repairing Torn Pages
- Troubleshooting a Data Issue
After Completing This Goal, Students will be Able To
- Explain the methodology of troubleshooting data issues.
- Explain the process of troubleshooting data integrity issues.
- Explain how torn pages can be resolved using a single-page restore.
- Troubleshoot and repair torn pages.
- Troubleshoot data integrity issues.
|
| Unit 6 Troubleshooting SQL Server Data Concurrency Issues |
| This module lets the students identify the offending objects that cause concurrency issues. The first exercise shows students how to determine stored procedures involved in a deadlocked situation. The second exercise shows students how to determine the source of a blocking issue. The third exercise shows students how to evaluate wait types and latches. |
| Lessons |
| The Methodology of Troubleshooting Concurrency Issues |
| What Are SQL Server Latches? |
| Activity: Choosing a Blocking Monitoring Solution |
Labs
- Lab: 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
After Completing This Goal, Students will be Able To
- Explain the methodology of troubleshooting concurrency issues.
- Explain what latches are and how they can be useful in troubleshooting long wait times.
- Choose a blocking monitoring solution.
- Identify the objects involved in a deadlock issue.
- Identify the objects involved in a blocking issue.
- Determine concurrency issues by using latch wait types.
|
Current User's Login:
Fill out the information as a first time user:
|
|
Live Instructor-led Troubleshooting and Optimizing Database Servers Using Microsoft® SQL Server™ 2005 Training Dates
|
Filter By...
| Providers: |
|
Location: |
|
| Date Range: |
|
| Provider |
Location |
Date |
Type |
Duration |
Price |
0 course(s) |
|
Online Self-Paced Troubleshooting and Optimizing Database Servers Using Microsoft® SQL Server™ 2005 Training Options
|
No online results
|