ADVANCED SQL SERVER PERFORMANCE MONITORING & TUNING HANDS-ON WORKSHOP |
|
Instructor Ramesh Meyyappan with more than 20 years of SQL Server experience including working as a Program Manager in the SQL Server Development Team at Microsoft Corporation in Redmond, U.S.A. He is specialized in SQL Server Performance Monitoring, Tuning & Troubleshooting. More about Ramesh Charges Please find the info on the respective registration pages, accessible via www.sqlworkshops.com/schedule The maximum number of attendees per workshop is 12, due to its hands-on character. Location For upcoming workshops please check the Schedule. This workshop can also be organized onsite at your company. Please send us your requirements (number of people to be trained and where you are located) and we will send you a proposal. Workshop content can be custom tailored to your needs. Preview Watch Ramesh explain SQL Server internals at www.sqlvideo.com (Youtube videos) or on Microsoft Channel 9. |
 |
Agenda |
|
SQL Server 2016 and 2017 specific topics are currently being revised and shall be added. Analyzing Current Activity and Performance Bottlenecks of SQL Server in a Production Environment: This module introduces a comprehensive approach for analyzing current performance bottlenecks of SQL Server in a production environment. Monitoring and Tuning CPU Utilization: SQL Server architecture relating to CPU usage is explained. Efficient methods for monitoring and tuning CPU bottlenecks are described. Identifying optimal number of CPUs and configuration parameters for maximizing CPU utilization is explained. Best practice guidelines on using Resource Governor for allocating CPU resource are covered. Monitoring and Tuning Disk I/O and Database Files: SQL Server architecture relating to Disk I/O and Database File usage are explained. Various methods including Wait Stats and Extended Events for monitoring Disk I/O and Database File usage are described out of which few important methods with reference data are provided for optimal monitoring. Best practice guidelines on implementing Storage subsystems with DAS, SAN and SSDs are explained. Configuring and maintaining Database Files for user database and tempdb with Index Rebuild and Reorganization, Data Compression, Database Snapshots, and Bulk Inserts for optimal performance are covered. Best practice guidelines on using Resource Governor for IO throttling and Delayed Transactions are covered. Monitoring and Tuning Memory Utilization: SQL Server architecture relating to Memory allocation and usage is explained. Efficient methods for monitoring and tuning memory usage are described. Tempdb usage due to Memory pressure and suboptimal execution plans are explained along with effective workarounds to address these performance issues. Best practice guidelines on using Buffer Pool Extensions and Resource Governor for optimizing Memory usage are covered. Monitoring and Tuning Query Optimization and Query Execution: SQL Server architecture relating to Query Optimization and Query Execution is explained. Various methods for monitoring and tuning Plan Caching and Recompilation using Parameterization and Plan Guides are described. Understanding Cardinality Estimation and analyzing Execution Plans including joins and parallel plans and isolating performance issues are described. Monitoring and tuning Query Execution with optimal Indexes, Statistics, Query Hints, Computed Columns, Indexed Views, Filtered Indexes, Filtered Statistics, Indexed Views, Partitioned Tables and Partitioned Views are covered. Best practice guidelines on using Temporary Tables, Table Variables, Table Value Parameters, Stored Procedures, User Defined Functions and in general writing SQL Server Query Optimizer friendly queries are covered. Monitoring and Tuning Locks, Deadlocks and other Concurrency issues: SQL Server architecture relating to resource Locking is explained. Various methods including Extended Events for monitoring and tuning Locking, Blocking and Deadlocks are described. Best practice guidelines on using Snapshot Isolation level, Lock Priority, disabling Table Lock Escalation, and Locking Hints and query rewrite procedures to reduce Locking and Deadlocks issues are covered. Monitoring and Tuning In-Memory OLTP and Clustered Columnstore Indexes: In-Memory OLTP architecture relating to Memory-optimized tables, Hash and Nonclustered indexes, Natively Compiled Stored Procedures are covered. Best practice guidelines on using In-Memory OLTP are covered. Columnstore Index architecture relating to Trickle and Bulk Insert, Delta Stores, Delete Bitmaps, Archival Compression, Batch & Row Mode Processing and Segment Elimination are explained. Best practice guidelines on using Clustered Columnstore Indexes are discussed. Monitoring and Tuning AlwaysOn Availability Groups, Database Mirroring, Backup Compression and Database Encryption: Monitoring and tuning the performance impact of Availability Replicas and Database Mirroring is explained. Best practice guidelines on using Backup Compression and Database Encryption from performance perspective are covered. Useful Performance data to collect on a regular interval and on ad-hoc basis are discussed. Using Extended Events and Management Data Warehouse for performance data collection is explained. Ways to further deepen the SQL Server performance monitoring and tuning knowledge are discussed. |