OEC501 Tuning and Optimizing Queries Using Microsoft SQL Server 2012

Unit 1: Measuring Database Performance
Overview: Best Practices for Measuring Performance
Overview: Key Measures for Query Performance

Lesson 1: Options for Auditing Data Access in SQL
Lesson 2: Implementing SQL Server Audit
Lesson 3: Managing SQL Server Audit
Lab: Auditing SQL Server Environments

Unit 2: Optimizing Physical Database Design
Data Model Optimization
Importance of Schema Design in Optimization
Normalization
Responsible Denormalization
Generalization
Infrastructure Optimization
Planning Files and Filegroups
Planning a Partitioning Data
Planning Compression

Unit 3: Optimizing Queries for Performance
Reading SQL Server 2012 Execution Plans
Lesson 1: Execution Plan Core Concepts
Lesson 2: Common Execution Plan Elements
Lesson 3: Working with Execution Plans
Lab: Reading SQL Server Execution Plans

Improving Query Performance
Lesson 1: Factors in Query Performance
Lesson 2: Displaying Query Performance Data
Lab: Improving Query Performance

Unit 4: Refactoring Cursors into Queries and T-SQL Enhanements
Cursor Usage and Performance
Strategies for Refactoring Cursors
Lesson 1: Transact-SQL Enhancements
Lesson 2: New and Enhanced Transact-SQL Functions

Lab: Developing SQL Server 2012 Databases

Unit 5: Optimizing an Indexing Strategy
Improving Performance through Nonclustered Indexes
Lesson 1: Designing Effective Nonclustered Indexes
Lesson 2: Implementing Nonclustered Indexes
Lesson 3: Tracing and Tuning Queries
Lab: Improving Performance through NC Indexes

Performance and Tuning Enhancements
Lesson 1: Performance Optimization Tools
Lesson 2: Enhancements to Indexing
Lab: Using Columnstore Index


Unit 6: Tracing Access to SQL Server 2012
Lesson 1: Capturing Activity using SQL Server Profiler
Lesson 2: Improving Performance with the Database Engine Tuning Advisor
Lesson 3: Working with Tracing Options
Lab: Tracing Access to SQL Server 2012


Unit 7: Managing Concurrency
Lesson 1: Introduction to Transactions
Lesson 2: Introduction to Locks
Lesson 3: Management of Locking
Lesson 4: Transaction Isolation Levels
Lab: Creating Highly Concurrent SQL Server Applications

Dotazione:
Ciascun partecipante avrà a disposizione una postazione allestita con i sistemi operativi ed il software necessario alle esercitazioni.