Course Code: 2368

55144 SQL Server 2014 Performance Tuning and Optimization

Class Dates:
1/20/2025
3/17/2025
Length:
5 Days
Cost:
$2495.00
Class Time:
Technology:
Database
Delivery:
Instructor-Led Training, Virtual Instructor-Led Training

Overview

  • 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
  • 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.

Prerequisites

  • 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 Disclaimer

*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 Details

  • 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