Duration
5 days
Introduction
- Course Materials
- Facilities
Prerequisites
- What We'll Be Discussing
- 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: SQL 2014 Architecture
- Configuring Compatibility Level
- Startup Parameter
- Startup Stored Procedure
- Instant File Allocation
- 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: The Database Engine
- Table-Valued Parameter
- Transactions
- Vertically Partitioned Table Using Code
- Partitioning with File Groups
- Partitioning Wizard
- SQL Performance Tools
- The 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
- 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: Query Optimizing and Troubleshooting
- 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
- 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
- Fragmentation of Indexes
- Patterns
- Index Storage Strategies
- Indexed Views
- Monitoring Indexes
- Index Dynamic Management Objects (DMOs)
- Best Practices
- Lab: Understanding Indexes
- Create Indexes
- Index Metadata
- Database Tuning Advisor
- Missing Index DMOs
- Data Compression
- Indexed Views
- DMO Index Stats
- 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
- In-Memory Databases
- Architecture
- Tables and Indexes
- Natively Compiled Stored Procedures
- Restrictions
- Analyze Migrate Report Tool
- In-Memory Data Management Views (DMV)
- Lab: In-Memory Databases
- Creating an In-Memory Enabled Database with Code and SSMS
- Creating a Memory-Optimized Table
- View Hash Index
- Natively Compiled Stored Procedure
- AMR Tool
- In-Memory DMVs
Programme scope, delivery arrangements and suitability are confirmed during enquiry.