New Horizons Computer Learning Center
410-597-9722

2790: Troubleshooting and Optimizing Database Servers Using Microsoft SQL Server 2005

Course Length: 2 Days

Overview:
This two-day workshop teaches database administrators working in enterprise environments 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.

Prerequisites:
Writing Queries Using Microsoft SQL Server 2005 Transact-SQL
Course 2779: Implementing a Microsoft SQL Server 2005 Database
Course 2780: Maintaining a Microsoft SQL Server 2005 Database

Course Content

Lesson 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
• 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

Lesson 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
• 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

Lesson 3: Optimizing the Query Performance Environment

• The Methodology of Optimizing a Query Environment
• The Query Performance Troubleshooting Process
• 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)

Lesson 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?
• Lab: Troubleshooting SQL Server Connectivity Issues
• Troubleshooting Server-Not-Found Issues
• Troubleshooting an Authentication Error Message
• Troubleshooting Endpoint Issues

Lesson 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
• Lab: Troubleshooting SQL Server Data Issues
• Troubleshooting and Repairing Torn Pages
• Troubleshooting a Data Issue

Lesson 6: Troubleshooting SQL Server Data Concurrency Issues

• The Methodology of Troubleshooting Concurrency Issues
• What Are SQL Server Latches?
• Activity: Choosing a Blocking Monitoring Solution
• 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

6940 Tudsbury Road, Baltimore, MD 21244
P: 410-597-9722
www.nhbaltimore.com