55144 SQL Server 2014 Performance Tuning and Optimization
8/10/2020 - 8/14/202010/12/2020 - 10/16/2020COURSE LENGTH:
9:00am - 4:30pm
This 5-day course is designed to give the right amount of internals knowledge combined with a wealth of practical tuning and optimization techniques that students can put into production. The course offers comprehensive coverage of SQL Server architecture, indexing and statistics strategies, optimizing transaction log operations, temp db and data file configuration, transactions and isolation levels, and locking and blocking. The course also teaches how to create baselines to benchmark SQL Server performance, how to analyze workload, and how to find and fix performance problems. Also included is an introduction to, and coverage of, two exciting new technologies: in-memory tables and stored procedures.
AUDIENCE AND PREREQUISITES
The primary audience for this course is individuals who administer and maintain SQL Server databases, and are responsible for optimal performance of SQL Server instances that they manage. These individuals also write queries against data and need to ensure optimal execution performance of the workloads.
The secondary audiences for this course are individuals who develop applications that deliver content from SQL Server databases.
Before attending this course, students should have:
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
- Working knowledge of database administration and maintenance.
- Working knowledge of Transact-SQL.
*Course Cost listed does not include the cost of courseware or lunch, required in your registration. Please refer to your Enrollment Sales Order or Invoice for the additional cost. If you have any questions, please contact us (firstname.lastname@example.org or 207-775-0244). Course subject to minimum enrollment. Subject to minimum enrollment. Course may run as a Live DIstance Learning (LDL) session if minimum enrollment is not met.
Module 1: Course Overview Introduction Course Materials Facilities Prerequisites What We'll Be DiscussingModule 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 Allocation How SQL Stores Data How SQL Locates Data Lab 1: SQL 2014 ArchitectureModule 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 Lab 1: The Database EngineModule 4: SQL Performance Tools The Resource Governor Activity Monitor Live Query Statistics Monitoring SQL with Transact-SQL Dynamic Management Objects (DMOs) and Performance Tuning Lab 1: SQL Performance ToolsModule 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 Lab 1: Query Optimizing and OperatorsModule 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 Index Data Management Objects Fragmentation of Indexes Patterns Index Storage Strategies Indexed Views Monitoring Indexes. Index Dynamic Management Objects (DMOs) Best Practices Lab 1: Understanding IndexesModule 7: Understanding Statistics Statistics Cardinality Estimator Incremental Statistics Computed Columns Statistics Filtered Statistics Maintenance Lab 1: Understanding StatisticsModule 8: In-Memory Database Architecture Tables and Indexes Natively Compiled Stored Procedures Restrictions Analyze Migrate Report Tool In-Memory Data Management Views (DMV) Lab 1: In-Memory Database