55144 SQL Server Performance Tuning and Optimization

Displaying courses for United States [Change]

Overview

The course offers a comprehensive coverage of SQL Server architecture, indexing and statistics strategies, optimize transaction log operations, tempdb and data file configuration, transactions and isolation levels, and locking and blocking.

Objectives

After completing this course, students will be able to:
  • Understand and utilize the new cardinality estimator
  • Understand and utilize memory-optimized tables
  • Understand and utilize startup stored procedures
  • Understand and utilize Instant File Initialization
  • Understand how SQL stored data
  • Understand how SQL locates data
  • Understand temporary table internals
  • Understand and utilize the new cardinality estimator
  • Understand and utilize memory-optimized tables
  • Understand performance
  • Understand and utilize startup stored procedures
  • Understand database structures
  • Understand and utilize Instant File Initialization
  • Understand how SQL stored data
  • Understand how SQL locates data
  • Understand temporary table internals
  • Understand and utilize table valued parameters
  • Understand concurrency
  • Understand and utilize transactions
  • Understand isolation levels
  • Understand and utilize SQL Server locking architecture
  • Understand SQL and Storage Area Networks (SAN)
  • Understand and utilize SQL on virtual machines
  • Understand SQLIO Utility and its uses
  • Understand and utilize partitioned tables and indexes
  • Understand and utilize the Resource Governor
  • Understand and utilize Activity Monitor
  • Understand Live Query Statistics
  • Understand how to monitor SQL using Transact-SQL
  • Understand and utilize performance DMVs
  • Understand indexes and their importance
  • Understand index types
  • Create and alter indexes
  • Understand and utilize index metadata
  • Understand and utilize Database Tuning Advisor
  • Understand and utilize Index Data Management Objects
  • Understand fragmentation of indexes and utilize page splits
  • Understand index storage strategies
  • Understand and utilize indexed views
  • Understand monitoring indexes
  • Understand index best practices
  • Understand and utilize statistics objects
  • Understand and utilize histogram
  • Understand and utilize new vs old cardinality estimator
  • Understand and utilize incremental statistics
  • Understand and utilize computed columns
  • Understand & utilize filtered statistics
  • Understand & utilize ascending keys
  • Understand & utilize statistics maintenance plan
  • Understand architecture
  • Understand & utilize tables and indexes
  • Understand & utilize the hash index
  • Understand & utilize natively compiled stored procedures
  • Understand restrictions
  • Understand & utilize the Analyze Migrat

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.

Syllabus

Course Overview

  • Introduction
  • Course Materials
  • Facilities
  • Prerequisites
  • What We'll Be Discussing
  • Lab: Course Overview

SQL 2014 Architecture

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

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: The Database Engine

SQL Performance Tools

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

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: Query Optimizing and Operators

Understanding Indexes

  • Introduction to Indexes
  • Index Types by Storage
  • Index Types by Column Designation
  • Creating and Altering Indexes
  • Metadata
  • Data Management Views for Indexing
  • Database Engine Tuning Advisor
  • Index Data Management Objects
  • SQL Server Fragmentation
  • Patterns
  • Index Storage Strategies
  • Indexed Views
  • Monitoring Indexes
  • Index Dynamic Management Objects (DMOs)
  • Best Practices
  • Lab: Understanding Indexes

Understanding Statistics

  • Statistics
  • Cardinality Estimator
  • Incremental Statistics
  • Computed Columns Statistics
  • Filtered Statistics
  • Maintenance
  • Lab: Understanding Statistics

In-Memory Database

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

SQL Profiler and SQL Trace

  • SQL Trace
  • SQL Trace Architecture
  • SQL Server Profiler
  • Lab: SQL Profiler and SQL Trace

Query Issues and Hints

  • Query Hints
  • Plan Guides
  • Plan Freezing
  • Join Orders
  • Lab: Query Issues and Hints

Training provider

Teaching mode:
Classroom - Instructor Led
Online - Instructor Led
Duration: 5 days
Gooroo has partnered with the global leaders in IT training to give you access to quality training, personalised to you, targeted at increasing your job opportunities and salary.

Our pricing

We do not display pricing as Gooroo members qualify for special discounts not available elsewhere. You must enquire through Gooroo to get this benefit.

New courses are happening all the time

Our partner's expert training consultant will provide you with the times and all the details you need. Enquire today.

Top skills covered in this course

Microsoft SQL Server
United States
This skill has an average salary of
US$94,968
and is mentioned in
2.39%
of job ads in this area.
Virtual machine
United States
This skill has an average salary of
US$94,329
and is mentioned in
0.31%
of job ads in this area.
Database
United States
This skill has an average salary of
US$81,092
and is mentioned in
13.62%
of job ads in this area.
SQL
United States
This skill has an average salary of
US$92,693
and is mentioned in
7.64%
of job ads in this area.