Overview

This 1/300 level, training course will give anyone that works with SQL Server a better understanding of how to analyze and troubleshoot poor query performance. The first problem people tend to have is understanding where to find performance data and how to evaluate a query’s plan. To understand this better, Erin will spend the first day showing how to capture query information, and then how to evaluate, understand, and analyze the many aspects of a plan’s performance. But, what is the real problem? Is it caching? Is it inefficient code? Is it statistics? On the second day, Jonathan will focus on common but ineffective query patterns that lead to performance problems as well as better solutions to handling them. And finally, on the last day, Kimberly will show you how to tell if your query problems are because of caching problems or because of statistics.

With techniques to determine your query’s actual problem and the many possible solutions for solving them, if you work with SQL Server in almost any capacity but just don’t know how to dig into query performance – this class is for you! And, while there are still other potential problems that can occur, dedicating only 3 half-days of your time for 9 hours of lecture and up to 4.5 hours of open Q&A, this is an amazing ROI while you can still get work done during your day! Spend some time with us so that we can help you tackle many of the most common and problematic scenarios that often plague SQL Server systems.

Instructors: Erin Stellato, Jonathan Kehayias, Kimberly L. Tripp

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Ready to buy? Please see our Immersion Event Offerings for options, bundles, pricing. and more.

Select Quotes From Past Attendees of Erin’s/Jon’s/Kimberly’s Online Classes

• “Extremely pleased with the course. FAR exceeded my expectations.”
• “Well worth the time and expense to attend. Would highly recommend this to others.”
• “Great course – very informative – very great instructors – I am sure to be back!”
• “Great course. Good new info for me, plus refresher on other info. Thanks!”
• “Both Erin and Jon have a vast knowledge of not only SQL Server & tools, but also effective presentation.”
• “Thanks for taking the time to better my knowledge of SQL and allow me to better my career.”
• “Kimberly is incredibly knowledgeable and was able to adapt the techniques to all the different scenarios presented to her.”
• “Great course. I could hear clearly, the content was relevant to current day problems, and provided clear instruction.”
• “SLIGHTLY prefer the classroom option, but attendance to a location can be very hard to manage and therefore WebEx is a VERY, VERY good alternative. I didn’t think it would be, but I’m definitely sold.” – Kevin Urquhart, UK
• “Loved the online aspect. It felt like I was there with the question ability and having the questions just answered right there. I felt I had a voice and could ask anything and the ability to watch it later made it totally worth the registration.”
• “I really enjoyed the ability to ask questions as the course went along so that I didn’t forget what I wanted to ask while you were teaching. This allowed for questions to come through and class to continue until a good stopping point to answer the questions. Plus having the questions written from other attendees was nice for future reference instead of trying to remember from an in-person class discussion.”
• “Kimberly, I have to tell you that you are hands down the finest educator I have ever met. Your dynamic personality, combined with your depth of knowledge, and passion to fully understand the subject matter are unmatched, in my experience. I feel truly privileged to have had the opportunity to learn from you again and again. You really DO make learning fun. Thank you!” – Mike Petri, USA

Curriculum

Part 1/Day 1: Capturing Query Information and Analyzing Plans (presented by Erin Stellato)

Tuning queries starts with a baseline. You have to measure query performance before you begin in order to quantify change. Then, you have to decide where to focus your efforts, and that often starts by analyzing the query plan. Topics covered include:

• Baselining options and considerations
• Sources of query performance data (e.g. DMVs, Extended Events or Trace)
• Capturing and comparing execution plans
  • Live Query Statistics
• Finding essential information in a plan
• Misleading information in a plan
• Common operators
  • Data Access
  • Joins
• Operators and memory use
• Predicates and filters
• Parallelism in plans

Part 2/Day 2: Removing Anti-Patterns in Transact-SQL (presented by Jonathan Kehayias)

Hardware and configuration best practices only go so far for solving database performance problems and identifying where design and code problems exists often only comes from experience.  Understanding how SQL Server processes queries is an important part of performance tuning and recognizing the anti-patterns for performance in code and how to replace those with other constructs is a fundamental part of building scalable solutions on SQL Server. Topics covered include:

• Set based concepts for developers
• Design considerations that affect performance
  • Normalization and data-types
• Reducing/eliminating row-by-row processing
  • CURSORs and WHILE Loops
  • Scalar User Defined Functions
  • Table Valued Functions
  • Using FOR XML and a table of numbers
  • Making row based processing scale when required
• Understanding Sargability and eliminating index scans in code
• Profiling during development and testing properly

Part 3/Day 3: How to Differentiate Caching / Statistics problems and SOLVE THEM! (presented by Kimberly L. Tripp)

On our final day, we’ll bring together all of the pieces of poor query performance. Erin’s covered how to see and evaluate the many aspects of a query’s performance and shown you what to look for while Jonathan has shown you ways to improve performance by re-writing poorly designed code. It’s definitely a good idea to consider moving away from some of these inefficient code patterns. However, there are still other frustrating query-performance inconsistencies around your statement execution method, parameterization, and even time (e.g. fragmentation, statistics out of date, table growth). In this final part I’ll show you how to determine if you have a caching problem or a statistics problem and then depending on which one it is, techniques to solve your problems!

Troubleshooting Statement Execution and Caching

• Different ways to execute statements
• Some statements can be cached for reuse
• Statement auto-parameterization
• Dynamic string execution
• sp_executesql
• Stored procedures
• Literals, variables, and parameters
• The life of a plan in cache
• Plan cache limits
• Bringing it all together

Troubleshooting Plan Problems Related to Statistics (not Caching)

• Statement selectivity
• What kinds of statistics exist
• How does SQL Server use statistics
• Creating additional statistics
• Updating statistics

Ready to buy? Please see our Immersion Event Offerings for options, bundles, pricing. and more.

Questions?

If you have any questions not answered by our Immersion Events F.A.Q., please contact us.