SQL Server 2014 Performance Tuning and Optimization Courseware (55144BC)

This five-day course introduces a detailed study of the SQL Server 2014 platform, where students explore a wide range of technical aspects. It begins with an overview module, providing insight into course materials, facilities, prerequisites, and the discussion topics planned for the course. It aims to equip students with a full understanding of the course's intent and familiarize them with the virtual machine environment.

The course proceeds with the SQL 2014 Architecture module, emphasizing the new Cardinality Estimator and memory-optimized tables. This module offers a clear view of how SQL stores and locates data, explaining performance for developers, startup parameters, startup stored procedures, database structures, and Instant File Initialization.

Further, the Database Engine module dissects the SQL Server Database Engine into its two major components, the storage engine, and the relational engine. Students gain a comprehensive understanding of the engine's workings, temporary table internals, concurrency, transactions, isolation levels, SQL Server locking architecture, SQL and Storage Area Networks (SAN), SQL on virtual machines, the SQLIO Utility, and partitioned tables and indexes.

In the SQL Performance Tools module, students explore robust monitoring tools offered by SQL. They gain knowledge about the Resource Governor, Activity Monitor, Live Query Statistics, how to monitor SQL with Transact-SQL, and Dynamic Management Objects (DMOs) for performance tuning.

Query Optimizing and Operators module focuses on the importance of query optimization in database applications. It covers topics such as the 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.

Understanding Indexes is another key module. Students get a comprehensive understanding of creating, utilizing, managing, and monitoring indexes. The lessons cover a wide array of topics including index types, metadata, Data Management Views for indexing, Database Engine Tuning Advisor, Index Data Management Objects, SQL Server Fragmentation, index storage strategies, indexed views, monitoring indexes, and index best practices.

The Understanding Statistics module underlines the importance of statistics in SQL Server Performance Tuning. It explores statistics objects, the Cardinality Estimator, incremental statistics, computed columns statistics, filtered statistics, and statistics maintenance.

The In-Memory Database module introduces the new In-Memory OLTP engine, a significant feature in SQL Server 2014. Lessons cover the architecture, tables and indexes, natively compiled stored procedures, restrictions, the Analyze Migrate Report tool, and in-memory Data Management Views (DMV).

The course also covers SQL Profiler and SQL Trace. Students learn about the SQL Trace architecture, SQL Server Profiler, and gain hands-on experience with the SQL Trace Default template, SQL Profiler Tuning template, creating a template for slow-running queries, identifying long-running queries, and using Profiler to detect deadlocks and blocked processes.

Finally, the course concludes with the Query Issues and Hints module, which explores query issues, how to identify them, and how to resolve them. Students learn about query hints, plan guides, plan freezing, and join orders. The course equips students with a comprehensive understanding of SQL Server 2014, preparing them for in-depth work in the field.