55144 SQL Server 2014 Performance Tuning and Optimization
CLASS DATE(s):
8/10/2020 - 8/14/2020
10/12/2020 - 10/16/2020

COURSE LENGTH: 5 Days

COURSE COST: $2495.00

COURSE TIMES: 9:00am - 4:30pm

Printable version of this course
print
Register for this course
register

COURSE OVERVIEW

This 5-day course is designed to give the right amount of internals knowledge combined with a wealth of practical tuning and optimization techniques that students can put into production. The course offers comprehensive coverage of SQL Server architecture, indexing and statistics strategies, optimizing transaction log operations, temp db and data file configuration, transactions and isolation levels, and locking and blocking. The course also teaches how to create baselines to benchmark SQL Server performance, how to analyze workload, and how to find and fix performance problems. Also included is an introduction to, and coverage of, two exciting new technologies: in-memory tables and stored procedures.

AUDIENCE AND PREREQUISITES

The primary audience for this course is individuals who administer and maintain SQL Server databases, and are responsible for optimal performance of SQL Server instances that they manage. These individuals also write queries against data and need to ensure optimal execution performance of the workloads.

The secondary audiences for this course are individuals who develop applications that deliver content from SQL Server databases.

Before attending this course, students should have:
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
- Working knowledge of database administration and maintenance.
- Working knowledge of Transact-SQL.

  

*Course Cost listed does not include the cost of courseware or lunch, required in your registration. Please refer to your Enrollment Sales Order or Invoice for the additional cost. If you have any questions, please contact us (learn@vtec.org or 207-775-0244). Course subject to minimum enrollment. Subject to minimum enrollment. Course may run as a Live DIstance Learning (LDL) session if minimum enrollment is not met.

COURSE TOPICS:


Module 1: Course Overview
Introduction
Course Materials
Facilities
Prerequisites
What We'll Be Discussing

Module 2: SQL 2014 Architecture
The New Cardinality Estimator
Memory-Optimized Tables
Understanding Performance for Developers
Understanding Startup Parameters
Startup Stored Procedures
Database Structures
Instant File Allocation
How SQL Stores Data
How SQL Locates Data
Lab 1: SQL 2014 Architecture

Module 3: The Database Engine
Four Important Concepts
Temporary Tables Internals
Concurrency
Transactions
Isolation Levels
SQL Server Locking Architecture
SQL and Storage Area Networks (SAN)
SQL on VMs
SQLIO Utility
Partitioned Tables and Indexes
Lab 1: The Database Engine

Module 4: SQL Performance Tools
The Resource Governor
Activity Monitor
Live Query Statistics
Monitoring SQL with Transact-SQL
Dynamic Management Objects (DMOs) and Performance Tuning
Lab 1: SQL Performance Tools

Module 5: Query Optimizing and Operators
Tuning Process
Performance Monitor Tool
SQL Query Processing Steps
Understanding Execution Plans
SET STATISTICS TIME and SET STATISTICS_IO
Data Access Operators
Troubleshooting Queries
Lab 1: Query Optimizing and Operators

Module 6: Understanding Indexes
Introduction to Indexes
Index Types by Storage
Index Types by Column Designation
Creating and Altering Indexes
Metadata
Data Management Views for Indexing
Index Data Management Objects
Fragmentation of Indexes
Patterns
Index Storage Strategies
Indexed Views
Monitoring Indexes

.
Index Dynamic Management Objects (DMOs)
Best Practices
Lab 1: Understanding Indexes

Module 7: Understanding Statistics
Statistics
Cardinality Estimator
Incremental Statistics
Computed Columns Statistics
Filtered Statistics
Maintenance
Lab 1: Understanding Statistics

Module 8: In-Memory Database
Architecture
Tables and Indexes
Natively Compiled Stored Procedures
Restrictions
Analyze Migrate Report Tool
In-Memory Data Management Views (DMV)
Lab 1: In-Memory Database