SQL Server 2014 Performance Tuning And Optimisation 55144BC Training - United Kingdom

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