Loading...

M55144 – SQL Server Performance Tuning and Optimization

Home / M55144 – SQL Server Performance Tuning and Optimization

Course Outline:

1. Course Overview

2. SQL 2014 Architecture

  • Cardinality Estimator
  • Memory-Optimized Tables
  • Understand Performance for Developers
  • Startup Parameters and Startup Stored Procedures
  • Database Structures
  • Instant File Allocation
  • How SQL Stores and Locates Data

3. The Database Engine

  • Temporary Tables Internals
  • Concurrency, Transactions, and Isolation Levels
  • SQL Server Locking Architecture
  • SQL and Storage Area Networks (SAN)
  • SQL on VMs and SQLIO Utility
  • Partitioned Tables and Indexes

4. SQL Performance Tools

  • Resource Governor
  • Activity Monitor
  • Live Query Statistics
  • Monitoring SQL with Transact-SQL
  • DMOs and Performance Tuning

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 and Troubleshooting Queries

6. Understanding Indexes

  • Index Types by Storage and Column Designation
  • Creating and Altering Indexes
  • Metadata
  • DMVs for Indexing, Database Engine Tuning Advisor, Index DMOs
  • Fragmentation and Patterns
  • Index Storage Strategies, Indexed Views, and Monitoring Indexes

7. Understanding Statistics

  • Cardinality Estimator
  • Incremental, Computed Columns, and Filtered Statistics
  • Maintenance

8. In-Memory Databases

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

Course Audience & Prerequisites:

Audience

  • SQL Server database administrators
  • Individuals responsible for writing queries against data that require optimal execution performance of workloads
  • Individuals who manage and maintain optimal performance of SQL Server instances
  • Application developers who deliver content from SQL Server databases

Prerequisites

Course Offerings:

Instructor-Led In Classroom

Newcomp can directly deliver courses through the use of in-class training facilities. Currently,  in-class courses are offered in Markham, Ottawa, Vancouver, Halifax, and Edmonton. Please note that classes can be added to new areas based on demand.

  • Course Outline
  • Course Audience & Prerequisites
  • Course Offerings
  • Related Courses

1. Course Overview

2. SQL 2014 Architecture

  • Cardinality Estimator
  • Memory-Optimized Tables
  • Understand Performance for Developers
  • Startup Parameters and Startup Stored Procedures
  • Database Structures
  • Instant File Allocation
  • How SQL Stores and Locates Data

3. The Database Engine

  • Temporary Tables Internals
  • Concurrency, Transactions, and Isolation Levels
  • SQL Server Locking Architecture
  • SQL and Storage Area Networks (SAN)
  • SQL on VMs and SQLIO Utility
  • Partitioned Tables and Indexes

4. SQL Performance Tools

  • Resource Governor
  • Activity Monitor
  • Live Query Statistics
  • Monitoring SQL with Transact-SQL
  • DMOs and Performance Tuning

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 and Troubleshooting Queries

6. Understanding Indexes

  • Index Types by Storage and Column Designation
  • Creating and Altering Indexes
  • Metadata
  • DMVs for Indexing, Database Engine Tuning Advisor, Index DMOs
  • Fragmentation and Patterns
  • Index Storage Strategies, Indexed Views, and Monitoring Indexes

7. Understanding Statistics

  • Cardinality Estimator
  • Incremental, Computed Columns, and Filtered Statistics
  • Maintenance

8. In-Memory Databases

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

Audience

  • SQL Server database administrators
  • Individuals responsible for writing queries against data that require optimal execution performance of workloads
  • Individuals who manage and maintain optimal performance of SQL Server instances
  • Application developers who deliver content from SQL Server databases

Prerequisites

Instructor-Led In Classroom

Newcomp can directly deliver courses through the use of in-class training facilities. Currently,  in-class courses are offered in Markham, Ottawa, Vancouver, Halifax, and Edmonton. Please note that classes can be added to new areas based on demand.