The Complete SQL Server Optimization and Performance Tuning Course
(SQLPTO, Live Instructor-Led Training, 4 days)


Dates and Pricing


Jan 16 to Jan 19, 2018$2,500/person Feb 13 to Feb 16, 2018$2,500/person Mar 13 to Mar 16, 2018$2,500/person

Outline

SQL Server Storage Architecture
Understanding Pages and Extents
How SQL Server Allocates Pages and Extents
Exploring Data and Index Placement with Filegroups
About Data Types, Row Size and Page Usage
Determining the Space Requirements of a Table
Working with Standard Data Types
Working with Text Data: Unicode and Non-Unicode
Working with Image Data
About Fragmentation: Data and Indexes
Using TSQL to Display Table and Index Space Usage
Using TSQL to Display Table and Index Fragmentation
Tuning SQL Statements
Understanding Query Parsing and Compilation
Using and Understanding Statistics Time and Statistics IO
Displaying and Understanding Graphical Execution Plans
How SQL Server uses Table Scans
How SQL Server Processes Range and Point Queries
Understanding SQL Server Join Strategies
Using Execution Plans to Write Good SQL Statements
Using the Query Optimizer
Implementing the Query Optimizer’s Recommendations
Indexes and SQL
About Indexes: Clustered and Non-Clustered
Understanding Index Architecture and Storage
The Essential Role of Covering Indexes
Understanding the Role of Fill Factor and Pad Index
About Indexes and Data
About Indexes and Insert/Update Operations
About Indexes and Delete Operations
About Indexes and Views
About Indexes and Indexed Views
Implementing Sparse Indexes
Creating Indexes: Guidelines and Usage
Matching Indexes with Queries: Point and Range Queries
Creating Indexes to Support Point Queries
Creating Indexes to Support Range Queries
Creating Indexes to Support Join Operations
Creating Indexes to Support Ordering Operations
Creating Indexes to Support Aggregate Calculations
Maintaining Indexes
How SQL Server uses Statistics
How to Update Statistics
Displaying Fragmentation Statistics
Rebuilding Indexes
Altering Indexes
Automated Index Maintenance
SQL Server Memory Management
Understanding the SQL Server Memory Architecture
Understanding the Buffer Pool and the Buffer Manager
The Importance of the Buffer Cache Hit Ratio
Understanding Log Operations
Working with and Tuning for Checkpoints and the Lazy Writer
Working with Log Writer
Managing Transactions
About Lock Granularity: Table, Page, Extent and Row
SQL Server Lock Types and their Compatibility: Shared, Exclusive and More
Understanding Lock Types in the Context of Transaction Isolation
Common Locking Conditions: Deadlocks, Livelocks and More
Detecting and Avoiding Deadlocks
Detecting and Avoiding Livelocks
Common Mistakes and How to Avoid Them
When Nothing Else Works: De-normalization and Other Strategies
Comparing OLTP and OLAP Databases
Separating Query Data from Transactional Data
The Need for De-Normalization
Implementing Redundant Data: Triggers and Functions
Implementing Surrogate Keys
Implementing Partitioned Tables
Implementing Partitioned Views