Overview:
The purpose of this course is to teach database administrators how to determine and troubleshoot performance issues using MS SQL Server 2005. It includes establishing monitoring standards and baselines, determining performance thresholds, and focusing the investigation on specific issues.
Learning method:
Instructor led.
There is a heavy emphasis on individual exercises.
Who will benefit from this course?
This course is intended for DBA’s wanting to become skilled on SQL Server 2005, specifically to ensure maximum performance of the query engine and the database server in general.
Pre-requisites:
Before attending this course, you should have:
- working knowledge of SQL Server 2005 architecture including indexing, SQL execution plans, and SQL Server basic configuration
- basic monitoring and troubleshooting skills. For example, students should have used Sysmon and Perfmon on the job
- basic understanding of server architecture such as CPU and memory utilisation and disk input/output (I/O)
- basic knowledge of application architecture. You should know how applications can be designed in three layers, what applications can do, the interaction between applications and the database, and the interaction between the database and the platform or operating system
- an understanding of Transact-SQL syntax and programming logic
- basic knowledge of Microsoft Windows networking. You should understand how Domain Name Service (DNS) operates and how servers communicate between domains
- a familiarity with SQL Server 2005 features, tools, and technologies
What can you expect to gain from this course?
After completing this course, you will be able to:
- Identify specific parameters that indicate levels of performance of both the server and querying engine in particular
- set up monitoring and alerts
- identify and isolate bottlenecks and faults
- prevent runaway conditions and poor performance
- understand details of how MSSQL Server handles locking and any concurrency issues
Course Content:
Module 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
Module 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 Analyse Outputs
Module 3:Optimising the Query Performance Environment
- The Methodology of Optimising a Query Environment
- The Query Performance Troubleshooting Process
Module 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?
Module 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
Module 6: Troubleshooting SQL Server Data Concurrency Issues
- The Methodology of Troubleshooting Concurrency Issues
- What Are SQL Server Latches?
- Activity: Choosing a Blocking Monitoring Solution