Audience
This course is intended for developers and administrators who have performance problems with SQL Server and need to take correct measures to speed it up rapidly.
Prerequisites
A solid foundation in SQL Server administration and writing T-SQL queries, or in developing applications for SQL Server is necessary. Ideally delegates will have several years of experience in these areas.
Duration
3 days. Hands on.
This course is available on site only. Please call for details.
Course Objectives
During this course the delegates will learn which factors affect the performance of SQL Server, as well as how to quickly find the main sources of problems, how to fix them quickly, and how to speed up their SQL Servers rapidly.
Part of the course is also devoted to the major mistakes that occur when inexperienced persons try to tune performance, as well as procedures how to avoid making such mistakes and correct them.
Upon successful completion of this course, students will be able to:
- Find the main performance problems with their queries and servers
- Use indexes properly and tune their performance
- Use advanced index performance optimizations
- Use full-text indexes and other types of indexes
- Tune query performance by tools provided by SQL Server
- Transfer data and data changes rapidly
- Automate regular performance tuning tasks
- Monitor performance of SQL Servers
- Identify and resolve common performance problems at server and query level
Course Content
SQL Server Query Processing Architecture
Query processing architecture
Execution plans
Compilation and recycling of execution plans
SQL Server caches, and their impact on overall performance
Performance factors affecting databases from the OS level - CPU, RAM, networks, disk drives and arrays, and their correct configuration
Indexes In-Depth
How SQL Server (doesn't) use indexes
Clustered vs. Nonclustered indexes
Internal structure of indexes
Index statistics, internal structure and its use
Covering vs. Composite indexes
Index bitmaps
Quick identification of missing and redundant indexes
Consolidation of indexes
Maintenance of indexes
Filtered Indexes
Indexed (materialized) views, indexing aggregates
Indexing functions and expressions
Little known ways of using indexes to optimize queries
Full-text indexes
XML indexes and spatial (geographic) indexes
Recommendations from the practice
Tuning Query Performance
JOIN Optimization
MERGE operator - fast data synchronization
Optimization of working with binary columns through FILESTREAM
Optimizing I/O through the compression of tables and indexes
Cursors and stored procedures – refactoring to relational queries, and vice versa, optimization of execution
Replacing SQL analyses by OLAP analyses - Introduction to Analysis Services
Rapid Data Transfer and Archiving
Bulk load
Partition switching, express data archiving
Change Data Capture (CDC) and Change Tracking
Introduction to fast ETL tools - SQL Server Integration Services
Automated Tuning of SQL Server Performance
SQL Profiler
Database Engine Tuning Advisor
SQL Nexus and RML Utilities
Performance Monitoring
Activity Monitor
Performance Monitor
Data Collector and Utility Control Points
SQL Server Performance Dashboard Reports
Identification and Resolving of Problems
Express diagnostics via Dynamic Management Views
Identification of the most demanding queries - long-taking, and also short ones with high cumulative load
Locking and blocking basics
Resource Governor – regulating resources for applications using SQL Server
Plan Guides - enforcement of execution plans for queries
Correcting problems with tempdb