SQL Server Performance Tuning and Optimization Training Course
(SQL Server 2005 & 2000)

Overview:
This course has been written to cover all the main aspects of tuning the performance of SQL Server (applies to versions 2000, 7 and 6.5). It builds upon several sections of the programming course and the administration course, focusing in on those aspects of SQL Server that potentially cause performance bottlenecks. If you are having optimization problems with your database, or trouble populating your tables efficiently, we can help you learn to optimize your database using various diagnostic tests and tweaks.

Available as a public or closed course at our London training centre, or can be tailored to your team's requirements and delivered onsite as a bespoke, customised training course.

Please click here for a full list of our latest SQL Server 2005 training courses

SQL Server Performance Tuning Training Course Summary

What You Will Learn
By the end of the course delegates will gain:
1. A theoretical overview to potential performance problems.
2. A practical, hands-on guide to the diagnostic tools available within the core product to identify specific performance-related problems.
3. A thorough grounding in the transact-SQL commands that help you analyse the status of tables, and what the optimiser is doing
4. Solid advice on tips and techniques to overcome these problems, keep them at bay and stop them happening in the first place

Course Content
Course content has been developed for real-world, commercial scenarios by our expert instructors. See below for detailed syllabus, or if you have a technical query email .

Duration
2 Days

Target Audience
Developers and administrators familiar with SQL Server needing to know about troubleshooting techniques and tips to improve database performance.

Pre Requisite Skills
Delegates should have a good background knowledge of SQL Server.

Associated courses

SQL Server 2005 training - coming soon!

SQL Server 2000 Applications Development training
SQL Server 2000 Database Administration
SQL Server Performance Tuning
SQL Server OLAP, DTS, Data Warehouses & Cubes / Analysis Services Training
SQL Design and Information Retrieval

SQL Server Performance Tuning Training Course Outline

Database Performance – What’s ‘Good’ and why?

Relational Database Overview

The Query optimiser, compilation and caching.

Locking and Logging and how updates happen.

How backups work, how DTS packages work.

Optimal Data Storage

Using File groups to Position Objects

The affect of normalisation and de-normalisation.

Analysis Services – a reporting solution in waiting?

Diagnostic Tools

Task Manager & Performance Monitor – General counters to look out for, tips and techniques.

Current Activity Window & SQL Profiler – Zooming in on problem queries

Query Analyser – Decrypting the showplan and trace output. Other set commands.

Transact SQL diagnostic commands. Various DBCC commands, some undocumented.

Index Optimisation

Index selection – How the optimiser decides.

Statistics – Auto and Manual.

Clustered versus Non-Clustered – Pros and Cons

The Fillfactor factor

Index optimiser hints

Index Covering

Index tuning wizard, when to use it.

Index Maintenance. Dangers and pitfalls.

Table defrag with clustered indexes. When and when not to do it.

Indexed Views – Advanced index builds.

Locking Optimisation

The City Analogy & Keeping the traffic flowing

Transaction isolation levels and locking hints

Deadlock resolution

Table restructuring & de-normalisation, replication and data warehousing solutions

Efficient Programming in TSQL

SARGS and the WHERE clause.

Covered indexes from the other side.

Stored procedures : Problems solved and created

Trigger problems and solutions

Cursors and Temporary tables

Tips and tricks.

Efficient Client Programming

Most of this course focuses on the server side application. However there is also a number of potential problems that may occur from the client side too. This section makes no assumption on which client you will be using, except that it will use ActiveX Data objects.

Default recordset options.

Send SQL versus Stored Procedures

Efficient use of recordsets and connections.

Timeout management and giving the user the perception of better performance