SQL Server 2014 Performance Tuning and Optimization

Duration

Course Code

5 days

55144BC

About the Course

The SQL Server 2014 Performance Tuning and Optimization 55144BC Course provides an in-depth understanding of performance tuning and optimization techniques crucial for managing SQL Server environments efficiently. This course is designed to equip participants with the skills necessary to enhance database performance, ensuring robust and reliable database systems.

Proficiency in SQL Server performance tuning is essential for Database Administrators, Developers, and IT Professionals who manage SQL Server databases. Mastering these skills is vital for optimizing query performance, reducing server load, and ensuring high availability and scalability of database applications. Professionals in these roles should aim to develop expertise in SQL Server performance tuning to maintain efficient database systems.

This comprehensive 4-day training course will empower delegates with the knowledge and practical skills needed to identify and resolve performance issues in SQL Server 2014. Through hands-on exercises and expert-led discussions, participants will learn to apply best practices and advanced techniques to optimize SQL Server performance, leading to improved system efficiency and productivity.

Course Objectives

To understand the fundamentals of SQL Server performance tuning and optimization.
To identify and analyse performance bottlenecks in SQL Server 2014.
To implement indexing strategies to enhance query performance.
To utilise SQL Server Profiler and Extended Events for monitoring.
To apply best practices for SQL Server configuration and maintenance.
To optimize SQL Server memory and CPU usage.

After completing this course, delegates will be able to effectively tune and optimize SQL Server 2014 for enhanced performance and efficiency, ensuring high availability and scalability of database applications, leading to improved overall system performance and reliability.

Target Audience

The SQL Server 2014 Performance Tuning and Optimisation 55144BC Course is intended for database professionals and IT personnel responsible for managing and maintaining SQL Server 2014 databases.

This course is particularly suitable for:

Database Administrators
SQL Server Developers
Systems Administrators
Database Analysts
IT Managers
Database Architects
Application Developers

Course Outline

Module 1: SQL 2014 Architecture

New Cardinality Estimator
Memory-Optimised Tables
Understanding Performance for Developers
Understanding Start-up Parameters
Start-up Stored Procedures
Database Structures
Instant File Initialisation
How SQL Stores Data?
How SQL Locates Data?

Lab: SQL 2014 Architecture

Configuring Compatibility Level
Start-up Parameter
Start-up Stored Procedure
Instant File Initialisation

Module 2: 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: Database Engine

Table-Valued Parameter
Transactions
Vertically Partitioned Table Using Code
Partitioning with Filegroups
Partitioning Wizard

Module 3: SQL Performance Tools

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

Module 4: Query Optimising 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 Optimising 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

Module 5: 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)

Lab: Understanding Indexes

Create Indexes
Index Metadata
Database Tuning Advisor
Missing Index DMOs
Fragmentation and Page Splits
Data Compression
Indexed Views
DMO Index Stats

Module 6: Understanding Statistics

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

Module 7: In-Memory Database

Architecture
Tables and Indexes
Natively Compiled Stored Procedures
Restrictions
Analyse 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-Optimised Table
View Hash Index
Natively Compiled Stored Procedure
AMR Tool
In-Memory DMVs

Module 8: SQL Profiler and SQL Trace

SQL Trace
SQL Trace Architecture
SQL Server Profiler

Lab: SQL Profiler and SQL Trace

SQL Trace Default Template
SQL Profiler Tuning Template
Create a Template for Slow Running Queries
Identifying Long Running Queries
Using Profiler to Detect Deadlocks
Use Profiler to Detect a Blocked Process

Module 9: Query Issues and Hints

Query Hints
Plan Guides
Plan To Freeze
Join Orders

Lab: Query Issues and Hints

Create and Test a Plan Guide
Allow SQL Profiler to Help Create a Plan
Plan To Freeze