Exploring SQL Server 2016 (Part 2 of 5): Performance Enhancements

Enhancements in performance are an important reason to upgrade to SQL Server 2016. Learn about changes in manageability features and execution, as well as query plans with live query statistics before taking a look at Powershell and integration with automation, and discovering three new groups of commandlets. Continue your exploration with a focus on in-memory table improvements, columns store index changes, and the new temporal tables, then learn about some additional improvements including enhancements of optimizer, cloud first development, and testing in the cloud. This course includes the following lessons:

Lesson 1:

• T-SQL Enhancements
• Helpful T-SQL Enhancements
• Demo: T-SQL DBA Enhancements
• Demo: T-SQL Developer Enhancements
• Use With Caution
• System Metadata
• Demo: System Functions
• Temporal Tables
• Demo: Temporal Tables
• R Language
• Demo: R Language.

Lesson 2:

• Manageability Enhancements
• What is Query Store
• Configuration
• Demo: The Query Store
• Demo: Running Queries for the Store
• Query Store Dashboards
• Top 25 Resource Consumers
• Demo: Regressed and Tracked Dashboards
• Demo: Resource Consumption Dashboards.

Lesson 3:

• Live Query Statistics
• Demo: Live Query Statistics
• Dynamic Management Objects (DMOs)
• Demo: DMOs
• PowerShell Additions
• Demo: PowerShell Additions
• PowerShell Additions (Continued)
• TempDB.

Lesson 4:

• Overview
• OLTP Workload Patterns
• Migration Considerations
• Demo: In Memory Technology
• Tables
• Restrictions
• More Restrictions Removed
• Demo: On-Disk vs In-Memory Tables
• Natively Compiled SP
• Demo: Natively Compiled SP
• Wizard
• Demo: Using the Wizard
• Performance.

Lesson 5:

• Overview
• Introduction
• History
• Demo: Column Store Index Features
• Demo: Statistics IO and Batch Mode Processing
• Storage and Retrieval
• Clustered Column Store Index (CCI)
• Demo: Clustered Column Store
• Non-Clustered Column Store Index (NCCI)
• Performance and Maintenance
• Demo: Clustered vs Non-Clustered Metadata.

Lesson 6:

• Overview
• Backups
• JSON Support
• Demo: JSON Functions
• Statistics
• Resource Governor
• Optimizer.