If you manage and maintain SQL server databases, this course will help you gain the knowledge and skills to optimize and tune the performance of databases.  You'll learn how to write queries against data to ensure optimal execution performance of workloads. 

 

No prior knowledge needed.

Course length: 5 days, Delivery: Online or in-class. 
Please call (475) 328-2227 with questions.

SQL Performance Tuning

1.    Course Overview

  • Introduction

  • Course Materials

  • Facilities

  • Prerequisites

  • What We'll Be Discussing


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 Initialization

  • How SQL Stores Data

  • How SQL Locates Data


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


4.    SQL Performance Tools 

  • The Resource Governor

  • Activity Monitor

  • Live Query Statistics

  • Monitoring SQL with Transact-SQL

  • Dynamic Management Objects and Performance 


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

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

  • 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


7.    Understanding Statistics 

  • Statistics

  • Cardinality Estimator

  • Incremental Statistics

  • Computed Columns Statistics

  • Filtered Statistics

  • Maintenance


8.    In-Memory Database

  • Architecture

  • Tables and Indexes

  • Natively Compiled Stored Procedures

  • Restrictions

  • Analyze Migrate Report Tool

  • In-Memory Data Management Views (DMV)


9.    SQL Profiler and SQL Trace

  • SQL Trace 

  • SQL Trace Architecture 

  • SQL Server Profiler 


10.    Query Issues and Hints

  • Query Hints 

  • Plan Guides 

  • Plan Freezing 

  • Join Orders 

Upcoming Classes

Course Overview

SQLCodingCamp.com

© 2018 SQLCodingCamp is an offering of Signzilla Enterprises

Email: sales@signzillatraining.com

Phone: (475) 328-2227

.........