Toll Free - 877-442-3915

Account | Self-Paced Login

Account | Self-Paced Login | 877-442-3915

SQL Server 2014 Performance Tuning and Optimization Course

SQL Server 2014 Performance Tuning and Optimization Training Course


Course Description:  The SQL Server 2014 Performance Tuning and Optimization - 55144 course is a 3-day course that is intended for database professionals looking to learn practical performance tuning and optimization techniques for SQL Server.

Students will learn all about SQL Server architecture, indexing and statistics strategies, how to optimize transaction log operations, tempdb and data file configuration, transactions and isolation levels, and locking and blocking. Students will alos learn how to create baselines and benchmark SQL Server performance, how to analyze workload and figure out where performance problems are, and how to fix them. Coverage of in-memory tables and stored procedures is also included.

Topics covered in the course include:

  • Understanding and utilizing the new cardinality estimator
  • Understanding and utilizing memory-optimized tables
  • Understanding performance
  • Understanding and utilizing startup stored procedures
  • Understanding database structures
  • Understanding and utilizing Instant File Initialization
  • Understanding how SQL stored data
  • Understanding how SQL locates data
  • Understanding temporary table internals
  • Understanding and utilizing the new cardinality estimator
  • Understanding and utilizing memory-optimized tables
  • Understanding performance
  • Understanding and utilizing startup stored procedures
  • Understanding database structures
  • Understanding and utilizing Instant File Initialization
  • Understanding how SQL stored data
  • Understanding how SQL locates data
  • Understanding temporary table internals
  • Understanding and utilizing table valued parameters
  • Understanding concurrency
  • Understanding and utilizing transactions
  • Understanding isolation levels
  • Understanding and utilizing SQL Server locking architecture
  • Understanding SQL and Storage Area Networks (SAN)
  • Understanding and utilizing SQL on virtual machines
  • Understanding SQLIO Utility and its uses
  • Understanding and utilizing partitioned tables and indexes
  • Understanding and utilizing the Resource Governor
  • Understanding and utilizing Activity Monitor
  • Understanding Live Query Statistics
  • Understanding how to monitor SQL using Transact-SQL
  • Understanding and utilizing performance DMVs
  • Understanding indexes and their importance
  • Understanding index types
  • Creating and altering indexes
  • Understanding and utilizing index metadata
  • Understanding and utilizing Database Tuning Advisor
  • Understanding and utilizing Index Data Management Objects
  • Understanding fragmentation of indexes and utilize page splits
  • Understanding index storage strategies
  • Understanding and utilizing indexed views
  • Understanding monitoring indexes
  • Understanding index best practices
  • Understanding and utilizing statistics objects
  • Understanding and utilizing histogram
  • Understanding and utilizing new vs old cardinality estimator
  • Understanding and utilizing incremental statistics
  • Understanding and utilizing computed columns
  • Understanding and utilizing filtered statistics
  • Understanding and utilizing ascending keys
  • Understanding and utilizing statistics maintenance plan
  • Understanding architecture
  • Understanding and utilizing tables and indexes
  • Understanding and utilizing the hash index
  • Understanding and utilizing natively compiled stored procedures
  • Understanding restrictions
  • Understanding and utilizing the Analyze Migrate Report tool
  • Understanding and utilizing in-memory data management views
  • Understanding and utilizing the SQL Trace Default template
  • Understanding and utilizing the SQL Profiler Tuning template
  • Creating and utilizing a template for slow running queries
  • Understanding and identify long running queries
  • Using Profiler to detect deadlocks
  • Using Profiler to detect a blocked process
  • Understanding and utilizing query hints
  • Creating and testing a plan guide
  • Allowing SQL Profiler to help create a plan
  • Understanding and utilizing plan freezing

Target Student:This course is designed for students that administer and maintain SQL Server databases, and are responsible for optimal performance of SQL Server instances that they manage.

Students should possess the following experience before attending the course:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality
  • Working knowledge of database administration and maintenance
  • Working knowledge of Transact-SQL

Training Dates
(Click on the course name below to view course details and full list of class dates)

SQL Server 2014 Performance Tuning and Optimization
Locations: Available Nationwide and Online
Class Date(s): 7/1/2019 - 7/3/2019
Course Fee : $1795


SQL Server 2014 Performance Tuning and Optimization
Locations: Available Nationwide and Online
Class Date(s): 7/31/2019 - 8/2/2019
Course Fee : $1795


SQL Server 2014 Performance Tuning and Optimization
Locations: Available Nationwide and Online
Class Date(s): 8/26/2019 - 8/28/2019
Course Fee : $1795


SQL Server 2014 Performance Tuning and Optimization
Locations: Available Nationwide and Online
Class Date(s): 9/25/2019 - 9/27/2019
Course Fee : $1795


SQL Server 2014 Performance Tuning and Optimization
Locations: Available Nationwide and Online
Class Date(s): 10/21/2019 - 10/23/2019
Course Fee : $1795


SQL Server 2014 Performance Tuning and Optimization
Locations: Available Nationwide and Online
Class Date(s): 11/20/2019 - 11/22/2019
Course Fee : $1795


Course Syllabus

Section 1: Course Overview
This section discusses how the class will be structured and introduces course materials and additional administrative information.

Topics :
Introduction
Course Materials
Facilities
Prerequisites
What We'll Be Discussing

Lab :
Course Overview
None
Successfully log into their virtual machine.
Have a full understanding of what the course intends to cover.

Section 2: SQL 2014 Architecture
In this section, we will discuss the new Cardinality Estimator, explore memory-optimized tables, and explain how SQL both stores and locates data.

Topics :
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
Configuring Compatibility Level
Startup Parameter
Startup Stored Procedure
Instant File Initialization
Understanding and utilizing the new Cardinality Estimator.
Understanding and utilizing memory-optimized tables.
Understanding performance.
Understanding and utilizing startup stored procedures.
Understanding database structures.
Understanding and utilizing Instant File Initialization.
Understandinghow SQL stored data.
Understandinghow SQL locates data.

Section 3: The Database Engine
The SQL Server Database Engine is split into two major components, which are the storage engine and the relational engine. The relational engine is also called the query processor which is a more descriptive term for what it actually does. In this section, we explore how the engines works, and cover concepts that will help you to Understandingtheir behavior.

Topics :
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
Table-Valued Parameter
Transactions
Vertically Partitioned Table Using Code
Partitioning with Filegroups
Partitioning Wizard
Understanding temporary table internals.
Understanding and utilizing table valued parameters.
Understanding concurrency.
Understanding and utilizing transactions.
Understanding isolation levels.
Understanding and utilizing SQL Server locking architecture.
Understanding SQL and Storage Area Networks (SAN).
Understanding and utilizing SQL on virtual machines.
Understanding SQLIO Utility and its uses.
Understanding and utilizing partitioned tables and indexes.

Section 4: SQL Performance Tools
SQL provides a number of robust monitoring tools and in this section we explore the tools available and explain what they are designed to do.

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

Lab :
SQL Performance Tools
Resource Governor
Activity Monitor
sp_who2
Performance DMVs
Understanding and utilizing the Resource Governor.
Understanding and utilizing Activity Monitor.
Understanding Live Query Statistics.
Understanding how to monitor SQL using Transact-SQL.
Understanding and utilizing performance DMVs.

Section 5: Query Optimizing and Operators
SQL Server query optimization remains a very important part of today’s database applications. In this section we discuss the tuning process, options for troubleshooting, execution plans, Performance Monitor, and much more.

Topics :
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
Performance Monitor (perfmon)
Estimated vs Actual Plans and XML Plans
Viewing a Non-Trivial Execution Plan
SET STATISTICS TIME and SET STATISTICS IO
Data Access Operators
DMVs
View Information About Extended Events
Wizard Templates
Creating a Session Without a Wizard
Configuring MDW and Viewing Reports
Understanding the tuning process.
Understanding and utilizing Performance Monitor tool.
Understanding SQL query processing steps.
Understanding and utilizing execution plans.
Understanding and utilizing SET STATISTICS TIME and SET STATISTICS IO.
Understanding and utilizing Data Access Operators.
Understanding and utilizing DMVs.
Understanding and utilizing Extended Events.
Understanding and utilizing sessions.
Understanding how to troubleshooting queries.
Understanding how to configure a Management Data Warehouse and view reports.

Section 6: Understanding Indexes
The entire concept of indexing is speedy retrieval. In this section we explain everything you need to know about creating, utilizing, managing, and monitoring indexes.

Topics :
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
Creating Indexes
Index Metadata
Database Tuning Advisor
Missing Index DMOs
Fragmentation and Page Splits
Data Compression
Indexed Views
Doubleclick to insert next Lab
Understanding indexes and their importance.
Understanding index types.
Creating and altering indexes.
Understanding and utilizing index metadata.
Understanding and utilizing Database Tuning Advisor.
Understanding and utilizing Index Data Management Objects.
Understanding fragmentation of indexes and utilize page splits.
Understanding index storage strategies.
Understanding and utilizing indexed views.
Understanding monitoring indexes.
Understanding index best practices.

Section 7: Understanding Statistics
Statistics are considered one of the most important aspects of SQL Server Performance Tuning. In this section we discuss statistics and how to utilize them.

Topics :
Statistics
Cardinality Estimator
Incremental Statistics
Computed Columns Statistics
Filtered Statistics
Maintenance

Lab :
Understanding Statistics
Statistics Objects
Histogram
New vs Old Cardinality Estimator
Incremental Statistics
Computed Columns
Filtered Statistics
Ascending Keys
Statistics Maintenance Plan
Understanding and utilizing statistics objects.
Understanding and utilizing the histogram.
Understanding and utilizing new vs old cardinality estimator.
Understanding and utilizing incremental statistics.
Understanding and utilizing computed columns.
Understanding and utilizing filtered statistics.
Understanding and utilizing ascending keys.
Understanding and utilizing statistics maintenance plan.

Section 8: In-Memory Database
The most important new feature in SQL Server 2014 is the new In-Memory OLTP engine. In this section we will cover the architecture, restrictions, and other exciting features.

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

Lab :
In-Memory Database
Creating an In-Memory Enabled Database with Code and SSMS
Creating a Memory-Optimized Table
View Hash Index
Natively Compiled Stored Procedure
AMR Tool
In-Memory DMVs
Understanding architecture.
Understanding and utilizing tables and indexes.
Understanding and utilizing the hash index.
Understanding and utilizing natively compiled stored procedures.
Understanding restrictions.
Understanding and utilizing the Analyze Migrate Report tool.
Understanding and utilizing in-memory data management views.

Section 9: SQL Profiler and SQL Trace
The SQL Server 2014 Profiler is essentially the same as the SQL Server 2012 Profiler. This is not surprising because many new features that were added in SQL Server 2005 and SQL Server 2008 addressed gaps identified in previous versions. In this section, we cover both SQL Profiler and SQL Trace.

Topics :
SQL Trace
SQL Trace Architecture
SQL Server Profiler

Lab :
SQL Profiler and SQL Trace
SQL Trace Default Template
SQL Profiler Tuning Template
Creating a Template for Slow Running Queries
Identifying Long Running Queries
Using Profiler to Detect Deadlocks
Use Profiler to Detect a Blocked Process
Understanding and utilizing the SQL Trace Default template.
Understanding and utilizing the SQL Profiler Tuning template.
Creating and utilizing a template for slow running queries.
Understanding and identify long running queries.

Section 10: Query Issues and Hints
In this section, we will discuss query issues, how to identify them, and how to resolve them. The course will also cover query hints, plan guides, and explore plan freezing.

Topics :
Query Hints
Plan Guides
Plan Freezing
Join Orders

Lab :
Query Issues and Hints
Creating and Test a Plan Guide
Allow SQL Profiler to Help Creating a Plan
Plan Freezing
Understanding and utilizing query hints.
Creating and test a plan guide.
Allow SQL Profiler to help Creating a plan.
Understanding and utilizing plan freezing.

Available Locations

Alabama
Birmingham
Birmingham South
Huntsville
Mobile
Montgomery

Arizona
Mesa
Phoenix
Scottsdale
Tucson

Arkansas
Bentonville
Fayetteville
Little Rock
Little Rock west

California
Bakersfield
El Segundo
Elk Grove
Fresno
Glendale
Irvine
Los Angeles
Los Angeles West
Mission Valley
Orange
Palo Alto
Petaluma
Pleasanton
Redwood City
Riverside
Sacramento
San Diego
San Diego downtown
San Francisco
San Jose
Walnut Creek
Woodland Hills

Colorado
Boulder
Centennial
Colorado Springs
Denver
Fort Collins
Grand Junction
Lakewood
Loveland

Connecticut
Cheshire
Danbury
East Granby
Hartford
Rocky Hill
Shelton
Stamford

Delaware
Wilmington DE

Florida
Boca Raton
Brandon
Clearwater
Daytona Beach
Fort Myers
Fort Walton Beach
Ft Lauderdale
Jacksonville
Kissimmee
Lake Mary
Lakeland
Land O Lakes
Melbourne
Miami
Miami Doral
Miramar Beach
Naples
Orange Park
Orlando
Orlando Downtown
Orlando Northeast
Palm Beach Gardens
Pensacola
Plantation
Sarasota
St Petersburg
Tallahassee
Tampa
Vero Beach

Georgia
Alpharetta
Atlanta Buckhead
Augusta
Columbus GA
Duluth
Kennesaw
Macon
Savannah

Idaho
Boise
Meridian

Illinois
Chicago
Gurnee
Joliet
Naperville
Northbrook
Oakbrook Terrace
Peoria
Rockford
Schaumburg

Indiana
Carmel
Evansville
Fort Wayne
Indianapolis
Indianapolis downtown
South Bend

Iowa
Cedar Rapids
Des Moines
Quad Cities

Kansas
Lenexa
Topeka
Wichita

Kentucky
Erlanger
Lexington
Louisville

Louisiana
Baton Rouge
Lafayette
New Orleans

Maine
Portland ME

Maryland
Annapolis
Baltimore
Bethesda
Columbia MD
Ellicott City
Frederick
Germantown
Greenbelt
Owings Mills
Rockville
Towson

Massachusetts
Boston
Braintree
Danvers
Mansfield
Newton
North Andover
Norwell
Springfield MA
Westborough
Woburn
Worcester

Michigan
Ann Arbor
Franklin MI
Grand Rapids
Kalamazoo
Lansing
Livonia
Novi
Traverse City
Troy

Minnesota
Bloomington
Maple Grove
Minneapolis
Minneapolis downtown
Rochester MN
St Cloud
Woodbury

Mississippi
Gulfport
Jackson

Missouri
Columbia MO
Kansas City
Lees Summit
O Fallon
Springfield
St Louis
St Louis downtown

Montana
Great Falls

Nebraska
Lincoln
Omaha

Nevada
Henderson
Las Vegas
Reno

New Hampshire
Bedford
Concord
Londonderry
Nashua
Portsmouth

New Jersey
Bridgewater
East Brunswick
East Rutherford
Hamilton
Jersey City
Mahwah
Morristown
Mt Laurel
Paramus
Princeton
Red Bank

New Mexico
Albuquerque

New York
Albany
Bohemia
Brooklyn
Buffalo
Harrison
Melville
Mt Kisco
New York City - Grand Central Station
New York City - Lower Manhattan
New York City - Penn Station
Poughkeepsie
Queens
Rochester
Syracuse
Uniondale

North Carolina
Asheville
Chapel Hill
Charlotte
Charlotte North
Charlotte Uptown
Fayetteville
Greensboro
Hickory
Morrisville
Raleigh
Wilmington

North Dakota
Fargo
Grand Forks

Ohio
Akron
Beachwood
Blue Ash
Cincinnati
Cleveland
Columbus
Columbus Downtown
Dayton
Dublin
Independence OH
Norwood
Toledo
Westlake
Youngstown

Oklahoma
Edmond
Oklahoma City
Tulsa

Oregon
Beaverton
Eugene
Medford
Portland
Salem
Tualatin

Pennsylvania
Allentown
Conshohocken
Erie
Harrisburg
Huntingdon Valley
Lancaster
Malvern
Philadelphia
Pittsburgh
Pittsburgh downtown
State College
University Center
Wexford
York

Rhode Island
Providence
Warwick

South Carolina
Cayce
Greenville
Mount Pleasant

South Dakota
Sioux Falls

Tennessee
Chattanooga
Franklin
Knoxville
Memphis
Nashville

Texas
Addison
Amarillo
Austin
Austin downtown
Bryan
Corpus Christi
Dallas
El Paso
Fort Worth
Grapevine
Houston
Houston North
Irving
Katy
Keller
League City
McAllen
Midland
Plano
Plano Frisco
San Antonio
San Antonio downtown
Sugar Land
The Woodlands
Waco

Utah
Provo
Salt Lake City

Vermont
Shelburne

Virginia
Alexandria
Arlington VA
Charlottesville
Fairfax
Fredericksburg
Glen Allen
Lynchburg
Manassas
McLean
Newport News
Norfolk
Reston
Richmond
Roanoke

Washington
Bellevue
Bellingham
Bothell
Kennewick
Mukilteo
Olympia
Puyallup
Seattle
Spokane
Tacoma

Washington DC
Washington DC
Washington DC - Union Station

West Virginia
Martinsburg

Wisconsin
Brookfield
Glendale WI
Green Bay
Madison
Madison east
Milwaukee

Wyoming
Casper
Cheyenne


Online Self-Paced Training Value Package Only $149

"The Instructor made class truly interesting and put everything into real world terms."
- Meredith T(Jersey City, NJ)


Business Computer Skills BBB Profile