New Pluralsight course: SQL Server: Using SentryOne Plan Explorer

Jonathan’s latest Pluralsight course has been published: SQL Server: Using SentryOne Plan Explorer. It’s almost 3 hours long, and from the course description:

Query plan analysis is both a science and an art, and the best tool for the job is SentryOne Plan Explorer. In this course, SQL Server: Using SentryOne Plan Explorer, you will learn how to make the most of the tool to make query plan analysis and performance tuning much easier and more intuitive than using SQL Server Management Studio. First, you will gain an understanding of how Plan Explorer compares to Management Studio, demonstrating how Plan Explorer solves the problems and shortcomings that Management Studio has. Next, you will discover how to use the the Plan Explorer user interface, including how the various information displays can help with performance analysis and tuning, and the various methods for getting query plans into the tool. Finally, you will explore how to use the more advanced features of the tool like index and statistics analysis, and profiling query performance using live query statistics. When you are finished with this course, you will have the skills and knowledge to start using the powerful SentryOne Plan Explorer tool to greatly improve your query plan analysis and performance tuning capabilities.

It’s a complete update for his 2013 course, and covers the very latest release of SentryOne’s fantastic (and free!) tool.

The modules are:

  • Introduction
  • Comparing Plan Explorer to SSMS
  • Using the Plan Explorer UI
  • Getting Execution Plans to Plan Explorer
  • Using Index and Statistics Analysis
  • Profiling Query Performance

Check it out here.

We now have more than 180 hours of SQLskills online training available (see all our courses here), all for as little as $29/month through Pluralsight (including more than 6,000 other developer and IT training courses). That’s unbeatable value that you can’t afford to ignore.


The Curious Case of… very long failover times with a large ad hoc workload

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

Erin worked with a client recently who was experiencing very long failover times with a large ad hoc workload and needed to understand why. By very long, we’re talking more than 30 minutes for an availability group failover, and they’d tested failovers under load before and never had it take more than a few minutes. They wondered if their workload had changed or there was some configuration setting that was off because they’d also recently upgraded.

Obviously Erin didn’t want them to do another failover and take more downtime and luckily they have a process in place to capture wait statistics all the time so Erin could see what waits happened while the failover was in progress. One stood out to her right away: QDS_LOADDB.

The QDS_LOADDB wait happens when a database is bring brought online, Query Store is enabled, and the Query Store data is being loaded from disk. No queries can execute until that data has finished loading. In this case, the client had enabled Query Store after upgrading and with their large workload of ad hoc queries, there was 100GB of Query Store data to load. That was the cause of the long failover time and they didn’t realize that Query Store has this behavior.

Luckily there is a workaround other than disabling Query Store. You can enable documented trace flag 7752 which makes the Query Store data load asynchronous with the database startup. This means that queries can run before the data load completes, but Query Store won’t capture any information about them (Query Store is essentially read-only until the data load completes). Erin discusses this and other Query Store trace flags in her post here, and in general about Query Store settings and data size in this post.

Bottom line: make sure you understand how a feature changes behavior before enabling it, and HA/DR testing should be performed regularly to catch unexpected behaviors such as this.

New live online training class in October: Fixing Slow Queries, Inefficient Code, and Caching/Statistics Problems

Continuing our series of live, online classes, Erin, Jonathan, and Kimberly will be delivering their new IEQUERY: Immersion Event on Fixing Slow Queries, Inefficient Code, and Caching/Statistics Problems in October! The class will be delivered live via WebEx on October 23-25 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the attendees will have lifetime access to the recordings following the end of the class.

Rather than have people try to watch a full day of training at their computer for one of more days, the class will run from 10am to 3pm PST each day, with two 90-minute teaching sessions, each followed by Q&A, and a lunch break. We chose to do this, and to spread the class over a few days, so the times work pretty well for those in the Americas, Africa, and Europe. We also realize that this is complex content, so want to give attendees time to digest each day’s material, plus extensive Q&A.

Here are some select quotes from prior 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.”
  • “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.”

The class is split into three parts, with each part taught by a different instructor:

  • Part 1/Day 1: Capturing Query Information and Analyzing Plans (presented by Erin Stellato)
    • Baselining options and considerations
    • Sources of query performance data (e.g. DMVs, Extended Events or Trace)
    • Capturing and comparing execution plans
    • Finding essential information in a plan
    • Misleading information in a plan
    • Common operators
    • Operators and memory use
    • Predicates and filters
    • Parallelism in plans
  • Part 2/Day 2: Removing Anti-Patterns in Transact-SQL (presented by Jonathan Kehayias)
    • Set based concepts for developers
    • Design considerations that affect performance
    • Reducing/eliminating row-by-row processing
      • CURSORs and WHILE Loops, scalar UDFs, TVFs
    • 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)
    • 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

The price of the class is US$699 (or US$599 for prior attendees of any SQL Server class).

You can get all the details here.

We decided to start teaching some live, online classes as we recognize that not everyone can travel to our in-person classes, or take that time away from work or family, or simply have travel budget as well as training budget. People also have different ways they learn, some preferring in-person training, some preferring recorded, online training, and some preferring live, online training.

We’ll be doing more of these so stay tuned for updates (and discounts through the newsletter).

We hope you can join us!