SQL Server 2014 Performance Tuning and Optimisation 55144BC Training 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 Freezing
- Join Orders
Lab: Query Issues and Hints
- Create and Test a Plan Guide
- Allow SQL Profiler to Help Create a Plan
- Plan Freezing