Microsoft SQL Server 2000/2005: Performance & Tuning

Course Overview

The objective of this course is to provide senior database analysts, developers or database administrators with a good understanding of SQL Server Architecture and Query performance so that applications and their supporting environment can be tuned for optimal performance.

The course will provide delegates with the knowledge required to size, plan, monitor and tune a SQL Server application for either a OLTP or data warehousing environment. Everything from Operating System & SQL Server service tuning through to query and index tuning will be covered. It is a hands on course with performance and analysis tools being put to use throughout.

Microsoft is an increasing popular career choice. This Microsoft training course will help accelerate your job prospects.

Related Courses

• Microsoft SQL Server: Developing SQL Queries for Microsoft SQL Server
• Microsoft SQL Server: Developing High-Performance Microsoft SQL Server Databases
• Microsoft SQL Server 2000/2005: Introduction for Beginners

Audience

• Senior Database Analysts
• Developers
• Database Administrators

Prerequisites

Delegates should have extensive experience with SQL Server 7 or later and should have a knowledge level equivalent to that provided by the PTR Advanced SQL, Stored Procedures and Triggers For SQL Server course. Knowledge of basic SQL Server 2000 Administration would be beneficial.

Course Outline

Performance and Tuning

• Sizing and Planning
• Monitoring Performance
• Analyzing Performance Data
• Tuning and Optimising Performance

SQL Server Internals

• Windows and SQL Server Architecture
• Process and Thread management
• Memory Use and Management
• I/O Management
• Networking

SQL Server Architecture

• Data Storage
• Transaction Logging
• Locking
• Indexing
• OLTP databases
• Data Warehouses

Planning A SQL Server Database Application

• Capacity Planning
• Sizing Databases
• Sizing Servers

System Performance and Tuning

• Hardware Resource Tuning
• I/O Performance
• Database Layout
• SQL Server Configuration Options

Monitoring Tools

• Performance Monitor
• SQL Profiler
• DBCC

Optimal Database Design

• Database Design
• Table Design
• Indexes
• OLTP databases
• Data Warehouses

Index Optimisation

• Index Types
• Covering Indexes
• Index Strategies

Locking

• Lock Types
• Locking and Granularity
• Blocking
• Locking and Transactions
• Transaction Isolation Levels
• Transaction Design
• Analysing Locks

Query Optimisation

• Query Optimiser
• Recompilation
• Query Cache
• Query Types
• Range
• Ordering
• Grouping
• Table joins and Join Algorithms

Analyzing Queries

• Query Analyser and Execution Plans
• SHOWPLAN
• Graphical
• Index Statistics
• Index Tuning Wizard

Tuning SQL Statements

• Comparing Queries
• Avoiding Subqueries
• Table Hints

Database Application Tuning

• Using Stored Procedures and User Defined Functions
• Views
• Partitioning Data
• Derived Data
• Minimising Redundant Data With Triggers
• Optimising Transactions
• Optimising Cursors

Tuning SQL Server Services and Tasks

• Replication Services
• DTS Packages
• Backup and Recovery