DPS 2020 Training Class – SQL Server Performance Tuning – by Amit Bansal

Pre-Con Title

SQL Server Performance Tuning – Fast Track

Track(s)

Data Administration

Tags

#Performance #SQLServer #Tuning #Troubleshooting #Query Tuning

Level

Intermediate

Trainer/Speaker

Amit R S Bansal

Abstract

One fine day you realize that you have to start tuning SQL Server for better performance. The need might have arisen from a recent performance issue, or the barrage of support tickets that are filling your inbox, or maybe your boss assigned this to you after realizing that SQL Server performance troubleshooting is not his cup of tea. Irrespective of the reason, the challenge is upon you to identify, diagnose and fix a variety of performance issue that is plaguing your SQL Server environment.

Performance troubleshooting can be categorized as proactive, real-time & reactive. If a well-defined methodology is followed during the proactive phase, you will have to fire-fight less during the real-time troubleshooting phase and you will have a lot of meaningful performance data to work within the reactive phase, which will further help you in preventing performance cases.

Join this demo-filled, full-day class, where you will learn the three approaches of SQL Server performance troubleshooting & tuning. You will learn the practical way, exactly like how you would troubleshoot in the real world with lots of code and scripts. Amongst many takeaways, here are some interesting ones:

• Develop a performance troubleshooting methodology for the real-world
• Learn how to benchmark and baseline (and also take away a framework, ready-to-use)
• Analyzing and troubleshooting common wait types
• Proactive, reactive and real-time CPU troubleshooting
• Troubleshooting IO performance bottlenecks
• Tracking SQL Server memory consumption and fixing OOM errors
• Dealing with Tempdb madness
• Top 6 query tuning techniques that will solve 75% of performance issues (real-world query tuning)
• Top 3 indexing strategies that will fix 60% indexing woes
• Get rid of deadlocks (yes, you hear it right)
• SQL Server 2019 performance enhancements
• In total, there will be five modules comprising of more than 55 demos, all from the real-world. Yes, a lot of content and the class will be fast-paced. The pre-con will focus on recent versions of SQL Server. All demos will be shown on SQL Server 2019. You will walk away with all the demo scripts, most of them being production-ready. You will also get PDF version of the content.

Modules/Topics

Module 1: SQL Server Wait Types & Statistics

1. Query Lifecycle
2. Connections -> Sessions -> Requests -> Tasks -> Workers -> Threads
3. Common Wait Types in SQL Server, identification & diagnosis

Module 2: The Holy Trinity – CPU, Memory, IO

1. Identifying, troubleshooting & tuning workload patterns that cause excessive CPU
2. Identifying, troubleshooting & tuning workload patterns that cause excessive Memory Usage
3. Identifying, troubleshooting & tuning workload patterns that cause excessive IO

Module 3: Query Execution & Query Plan Analysis

1. Statistics & Cardinality Estimation
2. Reading & Analysing Execution Plans
3. Important Iterators

Module 4: Query Tuning

1. Rewriting Transact-SQL
2. Optimizing Stored Procedures
3. Solving Parameter Sniffing Issues
4. Parametrization Best Practices
5. Tips with Procedural Code

Module 5: Index Tuning

1. Demystifying Common Myths with Indexes
2. Indexing Strategies
3. Index Fragmentation
4. Real-World Index Tuning Examples

Module 6: Dealing with Deadlocks

1. SQL Server Concurrency, Isolation Levels, Locking Granularity & Hierarchy
2. Common Deadlock Types
3. Deadlock Best Practices
4. Practical Deadlock Troubleshooting & Diagnosis

Attendee Pre-requisite (if any)

Pre-requisite: You need to have at least two years of genuine SQL Server experience to gain maximum from this pre-con.

Is this class being recorded?

Yes. This class will be recorded and the registered attendee will get 12 months streaming access to the recorded class. The recordings will be available within 30 days of class completion.

Additional Comments from Instructor/Speaker

No special instructions/comments from the Speaker

Additional Comments from the Organizers

DPS 2020 Pre-Con price increases on the 1st of every month. This month’s pricing is the lowest. Book Now and save.

Pre-Con Trainings are instructor-led, demo-based, 8 hours (4 hrs each day).

Due virtual mode delivery, Pre-Cons are not hands-on. The 8-hour delivery is designed for maximum coverage and demos by the instructor. However, delegates will get the pre-con material (PDFs, demos, scripts, etc, as shared by instructor) to practice at their convenience.

More Questions?

Visit FAQ page.

BooK Now

Book Now