Microsoft Premier Workshop: SQL Server 2014: Performance Tuning and Optimization


SQL Server 2014: Performance Tuning and Optimization is a four day course that provides an in-depth study into aspects of SQL Server that affect application performance.
Students will learn about the internals of the SQL Server engine such as the memory manager and task scheduler, table and index structures, locking and concurrency, query optimization and programming efficiency. Through lecture, demo and hands-on labs, students will discover the new performance-related features of SQL Server 2014. They will understand Dynamic Management Views (DMVs) and Extended Events, optimize queries, examine memory management and process scheduling, troubleshoot locking and blocking, and improve programming efficiency.

Technical Highlights

After attending this workshop, students will be able to:
•Analyze performance bottlenecks
•Design an effective index strategy
•Diagnose and avoid blocking and deadlocks
•Analyze execution plans
•Develop efficient queries and stored procedures


Module 1: Architecture
•Windows Memory management
•SQL Server Memory management
•SQL Server Disk I/O
•Wait statistics
•SQL Server Process Scheduling

Module 2: SQL Data Structures
•File, Page and Extent Architecture
•SQL Server Object Structures, Data Access and Index Architecture
•FILESTREAM and FileTable

Module 3: SQL Server Tools
•SQL Server Management Studio Performance Tools
•Extended Events
•Performance Tools
•Other Performance Tools
•Tuning Methods

Module 4: SQL Server 2014 Table and Index Structure
•XML Index
•Developing an Indexing Strategy
•Optimizing and Maintaining Indexes

Module 5: In Memory OLTP
•In-Memory OLTP

Module 6: SQL Server 2014 Locking and Concurrency
•Locking Concepts
•Isolation Levels
•Blocking and Deadlocks
•Latches and Spinlocks

Module 7: SQL Server 2014 Programming Optimization
•Query Processing
•Understanding Execution Plans
•Query Tuning
Module 8: T-SQL and Programming
•Caching Mechanisms
•Performance Considerations
•Coding Best Practices

Target Audience
Job titles such as: Database Administrators, Database Developers, Database Support Engineers, Database Architects, ISV developers which at least 2-3 years’ experience with SQL Server (any version)

Level 300
(Levels: 100= Strategic / 200= technical Overview / 300= technical Expert level)

Please send your registration mail to your Microsoft Technical Account Manager (TAM) or visit us on Microsoft Premier Education.