New live online training class on using Query Store in May!

Continuing our series of live, online classes, Erin will be delivering her new IEQS: Immersion Event on Solving Common Performance Problems with Query Store in May! The class will be delivered live via WebEx on May 22-23 (roughly 12-13 hours of content including Q&As; about the same as two full workshop days!) and the recording will be available to attendees for six months 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 in-person classes:

  • “Extremely pleased with the course. FAR exceeded my expectations.”
  • “Well worth the time and expense to attend. Would highly recommend this to others.”
  • “I though this was extremely worthwhile. I have been a DBA for a few years and this was a great refresher for a lot of things I don’t do very often but should.”
  • “Found class extremely helpful and plan to take the next one.”
  • “Great course – very informative – very great instructors – I am sure to be back!”
  • “I was familiar with all of the topics in this course, but barely understood most. I feel I will be able to begin to do all of the things I need to take simpler tasks from our DBAs to allow them to focus on the more advanced tasks. That was exactly my goal for taking this class.”
  • “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.”

The modules covered will be:

  • Query Store Fundamentals
  • Understanding the Query Store Data
  • Workload Characteristics and Query Store Performance
  • Finding Performance Issues
  • Forcing Plans
  • Automatic Tuning
  • Other Uses of Query Store
  • Visualizing Query Store Data

The price of the class is US$795 (or US$695 for prior live, online attendees) and you can get all the details here.

The class was also announced in our newsletter today, with a US$100 discount for those people who received that newsletter, valid for two weeks. All future live, online classes will always feature a two-week discount for newsletter subscribers.

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 in 2018, on a variety of topics, so stay tuned for updates (and discounts through the newsletter).

We hope you can join us!

New Pluralsight course: Analyzing Query Performance for Developers

Erin’s latest Pluralsight course has been published – SQL Server: Analyzing Query Performance for Developers. It’s four hours long, and from the course description:

Developers are sometimes charged with fixing performance issues, and they have no idea where to start. They’ve never seen a query plan, or when they look at one they’re not sure what it *really* means. The developers know how long it takes for a query to execute, but they don’t know how to see how much IO, CPU, or memory it uses. And sometimes they just create an index and that seems to fix the problem (even though they have no idea why!). In this course, SQL Server: Analyzing Query Performance for Developers, you’ll learn about all the data that SQL Server generates when a query executes – it’s more than just the query plan – and you’ll learn where it exists and how to find it. First, you’ll discover what the data represents and how to read a query plan. Next, you’ll explore some of the query plan operators you’ll see most often. Finally, you’ll learn what information is included in the plan, which may not be immediately obvious, but can be extremely valuable when troubleshooting. When you’re finished this course, you’ll have the skills and knowledge to start examining and understanding query plans and making query changes for better performance!

The modules are:

  • Introduction
  • Finding Information About Queries
  • Understanding Query Performance Metrics
  • Reading Query Plans
  • Operators in a Query Plan
  • Important Information in a plan

Check it out here.

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

Enjoy!

SQLskills SQL101: Query plans based on what’s in memory

As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.

One of the topics that I discuss in class today is why the query optimizer doesn’t know (or care) what’s in the buffer pool. (The query optimizer is the part of the query processor that’s responsible for compiling an efficient query plan, and the buffer pool is the cache of database data file pages that are in memory.)

Let’s investigate…

Scenario

Here’s a scenario:

  • Table T has two nonclustered indexes, A and B, that both cover query Q (a simple SELECT query)
  • Query Q will require a complete index scan of either index
  • Index A has 10,000 pages at its leaf level
  • Index B has 50,000 pages at its leaf level

Which index will the query optimizer use when compiling the query plan?

Cost-based…

SQL Server uses a cost-based optimizer, which uses various metrics and statistics to determine the most efficient query plan for the query (given the time limits imposed on its search of the space of all possible query plans). The ‘cost’ in ‘cost-based’ means that it considers the CPU cost and I/O cost of the various operators in the query plan, with the I/O cost essentially being relative to the number of physical reads required. And it assumes that nothing is in memory.

In the scenario above, the optimizer will choose a query plan using index A, as the most efficient plan will be the one involving the fewest phsyical reads and with such a large difference between the page counts of indexes A and B, index A will be chosen for sure.

Hypothetical memory-based…

Now let’s allow a hypothetical optimizer to base its plan choice on what’s in the buffer pool.

If index A is mostly not in the buffer pool and index B is mostly in the buffer pool, it would be more efficient to compile the query plan to use index B, for a query running at that instant. Even though index B is larger, and would need more CPU cycles to scan through, physical reads are waaaay more expensive (in terms of elapsed time) than CPU cycles so a more efficient query plan is the one that minimizes the number of physical reads.

This argument only holds, and a ‘use index B’ query plan is only more efficient than a ‘use index A’ query plan, if index B remains mostly in memory, and index A remains mostly not in memory. As soon as the relative proportions of indexes A and B that are in memory become such that the ‘use index A’ query plan would be more efficient, the ‘use index B’ query plan is the wrong choice.

The situations when the compiled ‘use index B’ plan is less efficient than the cost-based ‘use index A’ plan are (generalizing):

  • Indexes A and B are both memory resident: the compiled plan will use roughly 5 times more CPU than the optimal plan, as there are 5 times more pages to scan.
  • Neither index is memory resident: the compiled plan will do 5 times the number of physical reads AND use roughly 5 times more CPU.
  • Index A is memory resident and index B isn’t: all physical reads performed by the plan are extraneous, AND it will use roughly 5 times more CPU.

This means that the ‘use index B’ plan is really only the optimal plan at the time the query was compiled.

So although a hypothetical optimizer could make use of buffer pool contents knowledge to compile a query that is the most efficient at a single instant, it would be a very dangerous way to drive plan compilation because of the potential volatility of the buffer pool contents, making the future efficiency of the cached compiled plan highly unreliable.

And I also haven’t mentioned the extra cost of maintaining buffer pool contents knowledge in real time, and then potentially having to recompile queries that are now deemed to be inefficient because buffer pool contents have changed.

Summary

Although it doesn’t always get it right, the optimizer strives to produce the most efficient plan, assuming nothing is in the buffer pool. Understanding how the query optimizer comes to plan choice decisions is extremely useful for understanding query plans themselves and relating them to the code driving the plan.

Hope you found this helpful!