Performance Optimisation - 2 day Master Class
Location: Microsoft Auditorium, EPDC2, Leopardstown
Date: July 5th & 6th 2010
Cost: €1,299
Delivered by internationally acclaimed SQL gurus, Paul S Randal & Kimberly L Tripp, this level 400 course is suited to people who have a number of years hands-on experience with the technologies and concepts or to those who have already completed a level 300 course in this area. It is a natural extension on the material covered in the SQL Immersion syllabus. The course will be delivered over the course of two intense days.
Register Now
There are many aspects to performance tuning – some should be done when the database is designed, prototyped and tested, while others must be implemented over time. However, data churn varies, user needs change and what users told you during the development often turns out to be untrue...; the end result is that workloads do change. The key to long-term success is being able to handle these changes. How do you create a scalable environment that can handle these changes? Some maintenance changes are more for analyzing and troubleshooting where your bottlenecks are, while others are just general best practices that you should make sure you have implemented.
In this course, Paul and Kimberly focus on best practices in scalable code, monitoring system and resource utilization, as well as governing it and finally will analyze the health of your indexes and overall indexing strategy.
Remember you risk the design work you have already invested in going to waste if you don’t better understand plan caching and stored procedure optimizations! This course will cover how optimization works for stored procedures and when it might help to force some level of recompilation or even force a plan.
When dealing with resource troubleshooting, Paul and Kimberly will introduce three core concepts in understanding how your SQL Server is performing – waits, queues and locks – and they will explain how to investigate performance issues related to them. Following on from this two new features in SQL Server 2008 that allow resources to be constrained to protect critical workloads and perform very in-depth troubleshooting are introduced.
And finally, are your indexing strategies working? In this section you will examine whether or not your existing indexes are being used, and if so, how? The course will examine index health and maintenance as well as the tools that exist to help you create additional indexes.
Part 1 - Optimizing Procedural Code
- Module 1: Batches & Plan Cache
- Statement execution and plan caching
- sp_executesql and dynamic string execution
- Module 2: Optimizing Procedural Code
- Understanding stored procedures
- Understanding recompilation
- Creating an optimal plan
- Forcing plans & plan guides
Part 2 - Resource Monitoring & Troubleshooting
- Module 1: Waits & Queues
- What are waits and queues?
- What should you be looking for?
- Module 2: Locking / blocking
- Locking overview
- Examples of blocking scenarios
- Trouble shooting blocking
- Blocking avoidance strategies
- Module 3: Resource governor
- Module 4: Extended Events
- Module 5: Performance Data Collection
Part 3 - Are your indexing strategies working?
- Module 1: index cleanup
- index usage and consolidation
- Module 2: Index health
- What is fragmentation
- Detecting fragmentation
- Removing fragmentation
- Module 3: Missing indexes
Register Now