Course Outline
SQL Server is Microsoft's flagship database product and is a strong challenger to Oracle's long held dominant position as market leader. The constant demand for more flexible, deeper, and generally larger databases means that performance becomes a serious issue when your business depends upon instant access to that data.
Course Content
This course has been written to cover all the main aspects of tuning the performance of SQL Server. It builds upon several sections of the programming course and the adminisration course, focusing in on those aspects of SQL Server that potentially cause performance bottlenecks. 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.
This course has been developed for real-world, commercial scenarios by our expert instructors. See below for detailed syllabus. If you have a technical question, please email sales@jbinternational.co.uk
What you will learn
By the end of the course delegates will gain:
1. A theoretical overview to potential performance problems.
2. A 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 optimiser behaviour.
4. Solid advice on tips and techniques to overcome these problems, keep them at bay and stop them happening in the first place
SQL Server Optimisation / Performance Tuning - Advanced 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
