Download PDF version
Objective
This workshop covers all the advanced concepts of performance tuning. Performance Tuning is double sided sword – the features exist from early versions but not explored properly and newly introduced features. This workshop is created taking best of the both of the worlds and mixing it with real world scenarios. Attendees will learn lots of insights of performance tuning and will gain knowledge of new advanced tricks used by expert production engineers. Attendees will be able to go back and right away apply the newly learned knowledge in production environment.
Description
This 3 day workshop covers best of the best concepts and practices from popular courses offered by Solid Quality Mentors. Instead of learning theory only, the workshop focuses on providing real world experience by using demos and scenarios derived from customer engagements. The workshop is uniquely structured. Sessions are discussion based and designed to be an interactive between instructor and participants for an optimal learning experience.
The workshop is intended to be immersion-based where participants will have plenty of opportunities to get deeply involved in the concepts presented by the instructor. The best participants will receive special educational material from the instructor as further follow up.
Each learning session is followed up with Hands-On Lab (HOL). Each Hands-On Lab is carefully crafted to give maximum exposure to the subject discussed in session. After doing Hands-On Lab the learning concept will be strengthen and understood. This way attendee can be productive and ready to excel the tasks at work based on the learning experience from the workshop.
The last hour of the workshop is kept reserve to solve attendees’ real world problems and pressing issues at work. We will sit together and try to resolve them. If the subject is interesting to other attendees then we will also discuss generically. We will get our hands dirty with code and will do our best to reach to the solution.
Target audience
This seminar is intended for following audiences:
• SQL Developers – Professionals who are responsible for writing optimal SQL Query
• SQL DBA – Professionals who are responsible for performance of the server
• Programmers – Any language programmer who deals with SQL Server
Agenda
Day 1: Performance Tuning T-SQL with Solid Fundamentals
Do you know? – 10 Questions in 10 Minutes
This session is very unique and serves as an ice breaker between instructor and attendees. This unique session contains 10 different performance related questions. Attendees are will participate and discuss the questions and its answers. These 10 minutes also serves as the introduction to the class as well gives idea to attendees what they are learning in next 3 days.
Demo: Each Question will be explained using sample T-SQL Script
The Unknown Tricks of Subqueries – An Advanced Fundamentals
The age old topic of Subquery is in fact never old. Subqueries are most confusing to people who claim to know them well. This session talks about unknown tricks and tips that only advanced users would know. Learning them is like doing 6 digit multiplications without using paper or calculators. You will know significance of the Join vs Subquery, IN vs Except etc. This session provides advance trick where sub queries improves the performance and gives learning experience with execution plan.
LAB: Writing Subqueries to improve the performance
External and Internal of Joins – Inner, Outer, Cross vs Loop, Merge, Hash
The SQL Server Engine looks at the Join very differently from how we look at it. We think that there are Cross Join, Inner Join and Outer Join. SQL Server Engine thinks there are Loop Join, Merge Join and Hash Join. Learn where and how the bridge is completed. This session will also answer many real world questions related to joins. The importance of the order of joins in query as well how to performance tune joins will be covered in depth.
LAB: Optimizing Join Techniques and Join Hints
Tips and Tricks of Performance Tuning
This session will cover Tips and Tricks which are so far not covered in any session. This session will explain the enhancement in TOP clause, APPLY clause as well few tips related to writing proper T-SQL. This session will cover the advanced topics of Common Table Expression (CTE) and will help attendees to learn the real trick to take performance advantage of new features.
LAB: Writing TOP query for paging
LAB: Writing Recursive Common Table Expression
Day 2: Performance Tuning T-SQL with Solid Fundamentals
Logical Query Processing – Learning Proper Flow of Code
No Performance tuning course is complete without this session. Attending this session will help you answer the core questions for query optimization.
Which Index reduces IO cost? Why?
Join is better or Subquery for performance? Why?
How to remove most expensive ‘Sort’ operator in any query?
What is the performance effect of ‘Having’ clause?
Index Internals – Deep Look at Inside Story
Internals of the index is often ignored or considered as less interesting story. The reality is that without understanding the internals it is not possible to understand how indexes are in fact working. In this session we will take deep look at the constructions of the index, storage, usage as well few other index related internals. We will also look at the dynamic management views (DMV) related to index internals.
LAB: Writing query to understand Index B-Tree Levels and understanding
Index Externals – Clustered, Nonclustered, Included, Filtered and All
Indexes are very crucial element of any schema. They can increase the performance as well reduces the performance as well. Too many or too little is the common dilemma of developer and DBA for ages. The solution of this dilemma is extremely simple, once the technique of Index creation is mastered. This session will go deep into the index creation concept and will give solid understanding of the creating proper indexes.
LAB: Creating Indexes which improves
Question and Answers & Additional Lab Time
Here is your chance to ask your questions to the instructor which are so far not covered in any of the sessions. You can ask as many as questions as you like as well bring your real world problems. The instructor will guide you towards attaining resolutions to your problems.
Day 3: Performance Tuning T-SQL with Solid Advanced Technique
Flow of Query Execution – Internals of Engine
Do you know exactly how the query is processed inside SQL Server Engine?
This unique session will discuss the very same thing of how SQL Server Engine compiles the plan and decides the best optimal plan for SQL Server Engine. We will discuss how and changing the schema and recompilations of the T-SQL code makes deep impact on the cached plans. The importance of memory, CPU and IO in terms of the performance will be explained in this session.
We will go over few internals tables and structure where plan is stored.
LAB: Observe T-SQL Internals running T-SQL, manipulate the plan cache.
Understanding Execution Plans – A walk through
Have you ever got confused when looking at Execution Plan?
This session has no theory and only demonstrates. We will go over so many different examples and will try to understand how and why certain kind of executions plan is created. How to read the information in Execution plan and what is the significance of the same. How you can force a query to go into parallelism and how you can change the query execution plan using fixed plan guide. All of this and many more detailed technology subject we will discuss in this session.
LAB: Change Query Plan using various T-SQL Query Hints
Fast, Faster and The Fastest! – Turbo Fast Queries Techniques
This is the grand finale of two days. We will take a query which is already running in zero (0) millisecond and optimize it further by three levels. Each level will be more difficult than others and the end result will be stunning. This unique session will teach you interesting lessons at each level of optimization and will run the query faster than its original seep of zero (0) millisecond.
LAB: Attendees will enhance the solution explained by instructor
Your Problems and Our Solution
This is the final session. During this session instructor will address all real world problem of attendees and will propose solutions of the same. If you have any performance issue in your production server, this is must attend session to get resolutions.