Posted on

Perks for My SQLPASS Summit Precon: Prevent ‘Fake News’ in Your Data! Isolation Levels Demystified

I’m proud, giddy, and just plain overly excited to be giving a pre conference session at the SQL PASS Summit in Seattle, Washington on November 5, 2018.

This is going to be a fun, demo-packed day which will teach you skills you can use throughout your career as a developer, database administrator, or data professional.

Why attend?

While features and tricks change from year to year (or even month to month), understanding isolation levels is critical for your long term future.

The Session: Prevent ‘Fake News’ in Your Data! Isolation Levels Demystified

Are your users seeing bad data? If you’re using the default isolation level of read committed in SQL Server, chances are that sometimes your users get incorrect results. If NOLOCK hints lurk in your code, the odds are even higher that sometimes your customers see information that just isn’t right.

In this day-long, demo packed session, you’ll learn why a single statement may read rows twice, miss rows entirely, or return combinations of data that never existed in the database — and why that’s not a bug. You’ll learn what “read phenomena” are, which isolation levels are vulnerable to them, and the performance trade-offs which come from raising your isolation level to protect your users from bad data.

You’ll see how isolation levels work with newer technologies such as columnstore indexes, In-Memory OLTP, and Always On Availability Groups. You’ll discover why version-based isolation levels can be awesome, and what you need to look out for with these isolation levels to avoid race conditions that produce– you guessed it– incorrect results.

At the end of the day, we’ll pull together all this information into a guide. You’ll leave the seminar with the tools and knowledge to choose the right isolation levels for new and existing applications based on business and performance requirements.

What Does it Cost?

A single pre-conference session is $499. You can also bundle pre-conference sessions with registration at the full Summit.

The Perks

Attendees at my pre-conference session will not only get all the demos we show live, and a download of the PDF of slides, but also….

Perk: Watch It Again Later

Access to a recorded online session of my 8 hour seminar, “Conquer Blocking & Isolation Levels” for a full year.

Want to revisit a topic and get the nitty gritty details? You’ll be able to watch anytime, online.

Note: the live pre-conference sessions at PASS aren’t recorded for purchase anymore– however, you can buy recordings of “regular” sessions during the week from PASS in different formats.

Perk: A Month of SQLChallenges!

For four weeks following the conference, I’ll be hosting SQLChallenges on blocking and isolation level problems.

  • Attendees will get access to the problems and a chance to apply the skills they’ve learned.
  • I’ll hold a live, online session going over sample solutions for the problem. Attend, share your solution in Slack if you’d like, and ask questions.
  • Can’t make the SQLChallenge live? The recording will be posted and you’ll get online access for a year. All SQLChallenges support Q&A in the course pages.

Perk: Private Follow-Up Quizzes

Want even more learning? Following the SQLChallenges, attendees will get access to a series of four weekly quizzes about blocking and isolation levels to confirm your knowledge.

Combining this will the month of SQLChallenges will fully establish your new knowledge in place, getting you the most value for your dollar.

Register here

I hope to see you at the PASS Summit! Sign up here to join my pre-conference session.

Postscript: SQL Symposium subscribers get access to all these perks as well. As sustaining members during the first year of SQLWorkbooks, SQL Symposium members get it all 🙂 

Posted on

Two Free Sessions Next Week: Fighting Bad Data and Deadlocks

I am excited and honored to be giving two free online sessions next week. Both events are sponsored by the fine folks over at Quest Software.

Why is That Data Wrong? How Choosing the Wrong Isolation Level Causes Bad Results

Tuesday, June 12, Noon Pacific
24 Hours of PASS, Sponsored by Quest Software

If you haven’t thought much about isolation levels in SQL Server, chances are your applications can return inconsistent data to your users: data that looks completely wrong. If your user re-runs their report or reloads their screen, the data may look right the second time… but after this happens, your customer feels that they can’t trust your data.

In this session, you will learn why we have “isolation levels,” and how the read committed isolation level works by default in SQL Server. You’ll see how easy it is to make a query return inconsistent results using these default settings, and why this is allowed to happen. We’ll dig into an example where blocking causes a query to return “impossible” query results. You’ll leave the session with a fresh understanding of why choosing the right isolation level is critical to the success of your applications.

Register for this free event here

Check out more awesome sessions at the 24 Hours of PASS

DBA vs Deadlock: How to Out-Index a Deadly Blocking Scenario

Thursday, June 14, 8:30 AM Pacific
Sponsored by Quest Software

Deadlocks strike fear into the hearts of even seasoned DBAs — but they don’t have to!

In this session, you’ll get the code to cause a sample deadlock in SQL Server. You’ll see how to interpret the deadlock graph to find out where the conflict lies, and how to design an index to make the deadlock disappear. You’ll leave the session with the steps you need to confidently tackle future deadlocks.

Register for this free online session here

See you next week!


Posted on

Why Make a SQLChallenge on Tuning Functions? Why Not Say, ‘Stop Using Functions’?

I’ve published a new SQLChallenge for subscribers: Fix My Function.

This 200-Level challenge features a terrible, awful, very-bad function, which you are dared to tune.

The rules of the challenge include that you can rewrite the function and rewrite the query, but you still need to keep the function logic in a function.

What’s wrong with user defined functions?

I’m going to quote from a recent Microsoft paper of note a few times in this post: Froid: Optimization of Imperative Programs in a Relational Database. The paper is by Karthik Ramachandra, Kwanghyun Park, K. Venkatesh Emani, Alan Halverson, Cesar Galindo-Legaria and Conor Cunningham.

The introduction to the paper explains why user defined functions are both loved and hated.

UDFs are loved because they have loads of advantages

The paper lists these advantages, which are pretty persuasive. Functions can be very powerful because…

(a) They are an elegant way to achieve modularity and code reuse across SQL queries, (b) some computations (such as complex business rules and ML algorithms) are easier to express in imperative form, (c) they allow users to express intent using a mix of simple SQL and imperative code, as opposed to complex SQL queries, thereby improving readability and maintainability.

Expressing and coding business rules clearly, in a way that can be easily reused and maintained: that’s not a small benefit! That’s why functions are very common in database code, even though….

UDFs are hated because they often have big performance problems

I’m not going to be able to say this better than the paper. It explains:

It is a known fact amongst practitioners that UDFs are “evil” when it comes to performance considerations.

This is an academic paper, so there are citations regarding the evil of functions! That makes me laugh so hard.

If you’re wondering who was cited on the evil of functions, the articles are  by Simon Sabin and Arvind Shyamsundar.

Functions look like they’re going to shed some of their ‘evil’

The paper on Froid goes on to describe very cool new optimization techniques to help keep all those benefits of functions without killing performance. These optimizations solve a lot of the pains.

Pains like: user defined scalar-functions (of the non-CLR variety) prevent parallelism in the queries that call them.

The features described in the paper transform the logic in the function and bring them into the query plan itself, allowing parallelism and other optimizations. If you don’t want to read the whole paper, the abstract is pretty clear about this.

It’s still well worth knowing how to tune functions

There are reasons to still learn your way around tuning a function in SQL Server, and not just wait for v-Next (the version after SQL Server 2017).

  1. We don’t know exactly which functions can be optimized by these new function Froid-ing features
  2. We don’t know what Froid will cost / which editions will get it
  3. When there’s a simpler way to write or tune something, it’s often better!

Those reasons are why I wrote  Fix My Function. (Besides it being a lot of fun)

This SQLChallenge is a 200 level problem, but the videos documenting sample solutions get into the 300-level area by showing how to use estimated and actual plans to understand what functions are doing, and how to use SQL Server’s DMVs to measure function performance in different ways.

If you’ve already subscribed, you already have access to this course: dive right in.

If you’re ready to join, sign up here!

Posted on

New Course: Why Table Partitioning Does Not Speed Up Query Performance – With One Exception

Table Partitioning is Tricky Business

Learn why SQL Server’s table partitioning feature won’t make your queries against disk-based rowstore indexes faster– and may even make them slower.

Table partitioning can absolutely be worth implementing, but it may be for different reasons than you think!

In this course, you will learn:

  • What partition elimination really does
  • Why table partitioning makes some queries trickier to optimize
  • Where table partitioning shines – and in which case it can make queries faster

The videos in this session include selected questions and answers with a live audience. In these videos, I use diagrams to visually explain the concepts involved.

Like to get your hands on code to learn?

Yeah, me too. Course downloads include scripts that quickly create, populate, and explore the example table we walk through in the course, so you can prove each concept explained in the course.

If you’ve already subscribed, you already have access to this course: dive right in.

If you’re ready to join, sign up here!

Posted on

Why I Love the ‘Automatic Plan Correction’ Auto-Tuning Feature

I’m thrilled to have just finished and published a new course on Automatic Tuning in SQL Server. This feature is available in SQL Server 2017+ and Azure SQL Database.

I am WAY more excited about this feature than I thought I would be

Cynical me assumed…

  • This feature won’t be that cool
  • It only works sometimes — when you’ve actually had decent performance in the past!
  • It’ll probably be really clumsy

I shouldn’t be so cynical, because after working with this even for a short time, I started to see the magic.

I’m glad I was wrong

Yes, the feature only works sometimes. However, that’s because it’s NOT really clumsy. It’s quite thoughtful!

Automatic Plan Correction is smart enough to test and verify its changes. It’s smart enough to back off what it’s done when you add an index, or when your data distribution changes, and then test and verify again.

But this is OK, because it’s not trying to permanently fix things. It’s trying to make things better for a little while until YOU can step in and figure out a permanent fix!

Automatic Tuning makes it much easier to find bad parameter sniffing

One of the trickiest performance problems that developers and DBAs face is figuring out “why was this thing slow two hours ago, but it’s fast now?”

Many times, the answer to this is “bad parameter sniffing”: we had an execution plan in cache that was slow when the query was executed with a variety of different parameter values.

This is hard to identify because often a different plan is currently in cache by the time we look at it. And even if we have fancy monitoring tools, it can be tricky to compare the average costs of the plans and determine which plan may be better.

The Automatic Plan Correction feature is looking exactly for things like that.

And that is a hard enough problem to detect that I see why this is an Enterprise Only feature. I get it.

Best of all, you can try this out in a safe way

You can either let Automatic Plan Correction test out changes for you, or just look at its recommendations.

The recommendations contain pretty rich detail about the problem it observed, the regressed plan, and the plan that looks like it would be better. You can take that information and use it with Query Store to devise your own fix safely.

Pretty cool.

This course is for subscribers. If you haven’t joined, you can watch the first two video lessons for free

The first two lessons in the course cover:

No signup or login is required to check out those lessons. Click the links and preview away.

Subscribers also get…

Demo scripts, a PDF of the slides, and a quiz, plus modules on:

  • The Queries With High Variation Query Store Report (4 minutes)
  • How to Tell if a Query Plan Has Been ‘Corrected’ (6 minutes)
  • Expiring an Automatic Correction With Update Statistics (10 minutes)
  • Automatic Plan Correction Can Re-Correct! Plus: Our Correction Survives a Plan Cache Flush (10 minutes)
  • Why Automatic Plan Correction Skips Queries Using Recompile (3 minutes)
  • Demo: Recompile, Automatic Tuning, and Manual Plan Forcing in Query Store (14 minutes)
  • Observations, Starter Notes, and Final Q&A (5 minutes)

I also talk in the course about things you can do to manually find these problem plans in Query Store if you don’t have Enterprise Edition.

Interested in subscribing?

I’d love to have you join. Sign up here.


Posted on

New Course Completion Certificates

Learning isn’t always easy. And we often forget to take a moment to congratulate ourselves for completing tasks when it comes to learning.

But finishing up a course shouldn’t go un-marked. A little celebration is in order!

In this spirit, I revamped Course Completion Certificates

I wish that I could appear with cupcakes and a set of balloons every time you finished an online course. That’s not in this year’s budget.

The least I could do is give you fun certificates.

Each course now has a custom certificate featuring art from the course, and this will be the case for all future courses as well.

I like the certificates so much that even SQL Challenges and Recorded Webcasts will have certificates, when they are published! Every kind of learning should have a fun reward.

When do I see the certificate?

Once you’ve completed all lessons in a course and passed the quiz, the certificate appears on the course page and in your list of courses.

To complete a video lesson, you simply press the “Complete Lesson” button at the bottom of the page. This also marks the lesson with a green checkbox in the Course Progress navigation menu, so it’s easy to keep track of where you are in the course.

Course surveys are always optional– this doesn’t mean you have to fill one out, if you’re not inclined to give feedback. You can just hit “complete lesson” without filling out the survey.

But you do have to pass the Quiz 🙂

The "view certificate" button appears in blue at the top of the course page after completion
The “view certificate” button appears in blue at the top of the course page after completion

Are these certificates related to Microsoft certifications?

Nope. There’s no connection between these certificates and Microsoft certifications.

My courses here on SQLWorkbooks are designed to build practical, real-world skills for configuring and tuning SQL Server, writing reliable code, and getting the most out of SQL Server’s features. I base the courses on my experiences as a DBA, a consultant, and the questions that I get from people using SQL Server — not based on Microsoft’s current certifications.

This also means that “guess the new Enterprise feature as the correct answer” is unlikely to work on my quizzes 😉

Do I get a certificate for a free course?

Yes! Currently I have one free course on offer. Over time I plan to increase that number, and free courses will all have completion certificates as well.

What if I completed a course in the past?

These new certificates are available retroactively for courses you’ve completed in the past, as long as all lessons are complete and you passed the quiz. If you’re having problems seeing a certificate or have any questions, just let me know the details (please include your login id, the course in question, and a screenshot if there’s an error) and I’ll help out! Everything looked good when I tested it, but those are famous last words.

What do you mean by ‘trials’ on the certificate?

I work in technology. I know how many times you’ve been interrupted while trying to absorb information. Learning doesn’t come without all sorts of trials — which is all the more reason to celebrate yourself a bit when you finish a course.

Posted on

Celebration Sale! 50% Off Annual Subscriptions until Feb 23


Celebration-Sale-Annual-Video-LeagueLast week I went on an Excellent WordPress Adventure: I’ve got a whole new homepage, annual and monthly subscriptions available in the shop, plus lots of new features.

I’m so proud of how it turned out! Some of my goals were to integrate lots of the artwork that I’ve done over the past year or so into the website cleanly, to improve navigation, and to summarize pricing clearly, and I think I did pretty well.

To celebrate, I’m running a big sale on annual subscriptions

From now until February 23, subscriptions to the Annual Video League are 50% off!

Annual subscriptions won’t get cheaper than this in the future, so if you are interested, now is a good time to buy.

Here’s how the pricing works:

SQL Learning League™
Monthly SQLChallenges and solutions - live webcasts and video courses
Exclusive technical webcasts for subscribers - live and recorded
All online video courses
Quizzes and certificates
Captions, transcripts, adjustable video speed
Immediate access to new courses
SQL Symposium™
All online SQL Seminars with recordings
Monthly SQLChallenges and solutions - live webcasts and video courses
Exclusive technical webcasts for subscribers - live and recorded
All online video courses
Quizzes and certificates
Captions, transcripts, adjustable video speed
Immediate access to new courses
Indexing Live SQL Seminar
Single online SQL Seminar with recording
Live in-class SQLChallenges and solutions in the seminar
Seminar recording for one year
Course certificate

The different ways to buy are designed around different needs:

  • Is there just one course you want? You can buy access to just that course in a two year subscription.
  • Want to jump in and finish several courses in a month or two? The monthly subscription– Monthly Video Crew— is your best value.
  • Want to learn from any course or webcast, without having to finish up quickly? The annual subscription — Annual Video League — saves you a bunch of money off the Monthly Video Crew, and has extra perks in addition.

It is very important to me that users be able to easily control their subscriptions, online, without having to email or talk to anyone, and I’m happy to say the site works that way! Immediately after your purchase goes through, you can manage your subscription online.

Here’s a few highlights of changes I’ve made around the site…

Helpful dino counts the new courses

Annual subscribers will get to watch webcasts after the fact

If it isn’t possible or convenient for you to attend my free webcasts, you will be able to watch them afterward as an Annual Video League member. The published webcasts will be split into lessons and have script downloads, much like a regular course.

If you’ve got questions or comments, lesson pages let you ask or participate at the bottom of each page, too.

After much experimentation, I’ve just recently nailed down a process of recording live webcasts where the audio and video aren’t potato 🥔 quality on the recording, so this applies to webcasts from February 2018 and forward. (If there’s a past webcast you’d really like to have a recording of, hit me up with a request! I might re-run it.)

New courses sidebar widget

Over time, I’ve gotten feedback from many students saying that they prefer a shorter course format.

Folks seem to like courses to be in the 45 minute to 1 hour range, with relatively short lessons. This makes a lot of sense to me! I’m going to keep the few longer format courses I already have, but new courses will follow the shorter format. When a topic needs a longer time, I will break it up into ~50 minute courses and list them as a series.

This, plus publishing webcast courses, means that the number of courses I have will expand fairly rapidly, so it’s nice to have an easy way to see the most recent courses and recorded webcasts (for annual subscribers) that have gone live without heading over to the blog.

I’ve set up the sidebar on several common pages to show this list to make finding new content easier.

Handy dandy ‘Course Catalog’ page

I wanted an easy way to summarize courses by topic – both for shoppers, as well as enrolled students. The Course Catalog page is a single page that lists all the courses by topic, with links so you can jump around the page.

To get to each course, you just click on the course picture or a link. If you’re an enrolled student, you can take the course. If you’re a shopper, you can check out preview lessons.

I’ve found that as I work on the site and make little tweaks to courses, I use this page a ton! I think it will be very useful for students, too.

New ‘Course Catalog’ page and menu

Every course has captions and transcripts

All courses can now have captions enabled in the video browser, plus a written transcript below the lesson.

It took some time to get through the backlog for this. The company I hire to do captions isn’t staffed by SQL Server experts, so sometimes I end up with a caption where I’m talking about a ‘gross store index’ instead of a ‘rowstore index’ 😂. I did my best to catch errors and get the best quality possible, but see if you can find any funny easter eggs still in there.

There’s even more new stuff…

But I’m going to save ’em for short posts with more details on them.

Thanks to all of you who have supported me in building this site!

To everyone who has tried out courses, left me reviews, given me feedback, and taken surveys — thank you so much for helping me launch this site and get things off the ground. Your comments and help have really made a difference and helped me get to the point where I am today, and I am so grateful to you.