Investigation and fix: A SQL Server stored procedure slowdown

What happened?

At work this past week, I was alerted to a problem where a background job which processes incoming messages from a particular queue had slowed down dramatically. As a result, a significant delay had developed in providing certain event notifications to our customers.  

Our monitoring tooling showed that the culprit was a particular SQL Server stored procedure -- one that gets invoked as a part of processing each message from the queue. Execution time of that sproc had suddenly increased from near-instant to an average of 4 seconds, with spikes up to around 20 seconds. (The queue processing job is multi-threaded, so we were still processing about one message per second, but that was no longer keeping up with the incoming volume of messages during the busy hours of each day.)

No code deployments or changes to the environment had been made around the time of the initial slowdown; this problem had started occurring without an obvious root cause. 

Our investigation

The stored procedure in question comprises about a dozen individual SQL statements and queries. It wasn’t immediately clear which individual statement in the sproc was responsible for the slowness.

After a few initial investigative paths didn’t yield results, I decided to run a short trace on the live production database. The trace revealed that one particular SELECT query was consistently taking a long time to run during each run of the stored procedure.

Interestingly: When I ran that same SELECT query SQL manually, it completed near-instantly. However, when I ran some minor variations on that SELECT query, they did take a long time to execute.

Execution plans and indexes

SQL Server internally generates an “execution plan” for each query that it runs, whereby it decides how it will go about performing the operation that the query's SQL calls for. The SQL Server Management Studio tool is able to display these execution plans in a nice graphical format.

When the problematic SELECT SQL was run manually, the execution plan showed that it was – correctly – leveraging an existing index on the target table. That index had been originally added to support this specific query. It includes fields needed by the query’s lookup criteria.

Looking back at the results of the trace, the problematics SELECT queries being run during execution of the stored procedure were being run using a different execution plan – not leveraging the custom-designed index at all! A full table scan was being run by SQL Server to gather the query results. As that  table currently sits at a total row count of over 150 million, those queries were taking a while to run!

The specific SQL being run (simplified a bit here for readability) was:

SELECT TOP 1 ID
FROM myTable t
WHERE t.CreationDate > DATEADD(day, -30, GetDate())
  AND (t.Email = @Email OR t.PhoneNumber = @PhoneNumber)
  AND t.CompanyID = @CompanyID)
ORDER BY t.ID ASC

The custom index includes the following fields:

•     CompanyID
•     Email
•     CreationDate
•     PhoneNumber
  

Can you spot the problem?

…

Take a look at the ID field. That field isn’t part of the index; but it is being used in the SQL’s ORDER BY clause! And that clause, as it turns out, is used by the database as one of the factors in how it seeks for the single (TOP 1) row to return.

Through its own inscrutable internal algorithms, prior to April, SQL Server had been using an execution plan that did leverage the custom index for this query, despite the presence of the ID field in the ORDER BY clause. After April, some tipping point in the database’s characteristics was evidently hit that made SQL Server decide to no longer use that index in its execution plan during runs of the stored procedure.

The fix was to adjust the SELECT query to ORDER BY CreationDate, rather than by ID. This had the same logical effect with regard to selection of the returned row; and it got SQL Server to once again use our custom index while running the stored procedure, resulting in the resumption of speedy performance in processing of messages from the queue.

How did we verify that the fix worked?

• We observed that new records continued to be inserted correctly (and now, a lot more quickly!) into the database table.
• Execution times for the stored procedure went way down. We could see this both from the database itself, and from the our queue's web interface – the green line here is the rate of messages being processed off the queue, with the fix having been applied just after the 10:20 mark:

So what are the lessons here?

• Having the ability to determine which specific SQL statement was the root cause of the downstream slowness was key to being able to devise a fix. (The fact that our monitoring instrumentation only reported to the granularity of which stored procedure was running slowly -- but not which individual SQL statement within the sproc was slow -- made this trickier.)
• The availability of multiple weeks of monitoring data from our instrumentation did make it quick and obvious to confirm that there had been a dramatic change in performance from one of our stored procedures, which was the cause of the problem.
• When a particular SELECT statement is slow, ensuring that a properly-matching database index is in place – and that index is being applied to live queries in the expected manner – is a very good thing to check!

Tip: How to easily reorder bullet points in Google Docs

There's an easier way to reorder bullet points in Google Docs than cutting and pasting!

1. Position the insertion point over the bullet point line you want to move. 
2. Press Ctrl+Shift+⬆ (up arrow) to move the line up, or Ctrl+Shift+⬇ (down arrow) to move the line down.

I’ve found that this works a lot more nicely than cutting the item to be moved, and then pasting the item in the new location in the list (since often, line breaks need to be fixed up after the new item is pasted in).

If you select multiple items in the bulleted list first, then the aforementioned keyboard shortcuts will move them all together, as a group. 

This tip works for reordering or moving regular paragraphs, too, in addition to bullet points.

Want to do the same thing in Microsoft Word? Check out my (much!) earlier post, Tip: How to easily reorder bullet points in MS Word, from 2009!

My Game of the Year Awards: 2021

Dating back to my days as a kid, one of my favorite hobbies is computer and console gaming. Following the Gamers With Jobs rules -- only games that I played for the first time in 2021 are eligible to make the list -- here are my top 10 favorite new-to-me games of 2021 (along with the platform on which I played each game), in descending order:

1. Metroid Dread (Nintendo Switch)

After nearly 20 years, we finally got a new 2D Metroid sequel. And it does so many things right! Gameplay that deeply respects prior games in the series, yet smartly blazes new territory. Beautiful and finely-detailed graphics. Good map/level design. Tough but fair boss battles. It's probably impossible for the greatness of Super Metroid (SNES) to ever be matched again, but Dread is an excellent modern take on the formula.

2. Bravely Default 2 (Nintendo Switch)

Bravely Default 2 takes the raw materials of JRPGs going all the way back to the first Final Fantasy games, iterates and folds them back on themselves many times, and ends up with this pretty nice piece of gaming origami. The most important thing I look for in a JRPG is combat that is fun, well-balanced, and (ideally) rewards smarts/creativity on the part of the player, and Bravely Default 2 delivers.

3. Final Fantasy Pixel Remaster (iPhone)

Speaking of the first Final Fantasy games: It was a treat this year having in my pocket this update to the very first Final Fantasy featuring a wonderful soundtrack, sharp graphics, and modernized and rebalanced gameplay -- while still remaining faithful to the original. I don't often blog about individual games, but I did do a compare-and-contrast between Final Fantasy Pixel Remaster and the NES original.

4. Monster Train (Windows)

With my primary computer these days being a Mac, I don't do a lot of gaming on Windows, but I made an exception for Monster Train. It's a fun and smart iteration on the Slay the Spire deck-builder-battler genre. (I was initially put off somewhat by the protagonists being the forces of Hell, but it's fine; in Monster Train, the angels are a bunch of jerks, and your team is fighting for self-preservation.)

5. Xenoblade Chronicles (Nintendo Switch)

I'm still midway through this expansive classic, and it might end up higher on a hypothetical 2021-2022 combined list, but for now, here we are! I had originally passed on this game, but the combination of my teenage son recommending it to me as having "single-player MMO" combat, plus wanting to learn more about Shulk from Smash Ultimate convinced me to give Xenoblade Chronicles a fair shake, and I'm glad I did. And I really like the soundtrack!

6. Etrian Odyssey 5 (Nintendo 3DS)

I dusted off my 3DS again during a period where the aforementioned teenage son was monopolizing the household Switch, and discovered that he'd purchased a digital copy of Etrian Odyssey 5 (2017) at some point. I'm only midway through, but the tough combat and old-school DIY mapping using the 3DS's stylus are just as engaging as I remember from earlier series entries.

7. New Pokemon Snap (Nintendo Switch)

I had low expectations for New Pokemon Snap, but they were wildly exceeded when I had a couple of weeks to give it a try on a check-out from my local library. I think the thing I like most is the fact that when you manage to get a great photograph, it's 100% due to your own timing and skill -- not due in part to some huge passive buff from a piece of equipped gear, or a lucky roll on some behind-the-scenes RNG. I'm planning to purchase a copy of New Pokemon Snap for myself when it goes on sale.

8. Steamworld Quest (iPhone)

This deck-builder-battler that's not a roguelike, but is instead a party-based RPG, played really well on the iPhone platform, even though it was originally designed for larger screens. The unique battle system kept me engaged all the way through to the end of the game.

9. Roguebook (Mac)

Another fun riff on the deck-builder-battler-roguelike genre, adding a map exploration mechanic that ends up working a bit like FTL, where you need to do all you can in a given area to power yourself up, before taking on the area boss and advancing. A few remaining rough edges in both minor UI bugs and gameplay balance issues don't hold Roguebook back from being fun to play.

10. Sorcery! 4-game series (Mac)

Also available on iPhone, this isn't just a remaster of the original 1980s gamebook series, but a full remake, taking advantage of the digital platform. A combat system that incorporates a bit of skill, and isn't simply dice-rolling -- and smartly, gives the player the option to immediately replay battles that didn't go so well -- adds to the fun.

Honorable mention

Best new DLC: Fighters Pass 2 for Super Smash Bros Ultimate (Switch). I find it crazy how well this mashup of 82 or so distinct playable characters from dozens of different series actually works in practice. My favorite gaming experience of 2021, eclipsing any of the new games mentioned above, is playing 2v2 local-vs-online matches with the teenage son. (The characters I mainly play are Samus and Ludwig von Koopa!)

Kotlin: My first impressions, via days 1-3 of Advent of Code 2021

While working on Ruby solutions to this year's Advent of Code programming challenges, my attention was caught by one of the sponsored messages running in the site's sidebar, from JetBrains:

Get ready to jingle with Advent of Code in Kotlin! Have fun, learn new things, and win prizes. Believe in magic with Kotlin. Happy holidays! https://jb.gg/AoC

That link redirected to a post on the JetBrains / Kotlin blog inviting developers to try out the Kotlin language via the Advent of Code (AoC) problems, and providing a nice Kotlin AoC GitHub template as a starting point.

I'd never worked with Kotlin before, but I have used AoC to try out new programming languages before. Serendipitously, I had some time available today, and so I decided to give it a go!

In the time I had, I wound up solving the first 3 days of AoC 2021 in Kotlin.  Here's a quick write-up of my very first impressions of Kotlin!

What I liked!

Good out of the box support in the IntelliJ IDE (which I downloaded for the first time today), including suggestions for more idiomatic Kotlin syntax. (Which makes sense, as JetBrains provides both the IntelliJ IDE and the Kotlin language itself!)  I was saved from some instances of typical "trying to write language X as if it were language Y" newbie mistakes by warnings/suggestions in the IDE.

IntelliJ displays, inline in loop declarations, whether the lower and upper bounds are inclusive of exclusive. This made it easy for me to understand whether, in a Kotlin until loop over an array, I needed to use myArray.length or myArray.length - 1 as the upper bound of the loop. (The former!)

I found the data class syntax to be a nice succinct and readable way for a method to return some related values. In a method in one of my Ruby AoC solutions where I was returning two values just wrapped together in an array, it was easy in the Kotlin equivalent to declare a one-liner data class, and then have my method return an instance of that class, to make what was being returned much more obvious to readers.

If ... else in Kotlin is an expression, not a statement. While looking up an equivalent for the typical ? : ternary expression syntax, I actually enjoyed discovering that ? : is actually not supported in Kotlin, and an inline if ... else should be used instead.

Support for ".."-syntax ranges like in Ruby (e.g. (5..15) to represent the set of integers 5 through 15, inclusive) was fun to see in a C-like language.

The variable-declaration keywords val and var, similar to let and const in JavaScript ES6, to respectively declare immutable and immutable variables.

The ability to pass a function "pointer" as a parameter to a function -- something both the Ruby and Kotlin variations of my Day 3 solution actually made use of.

 

What I maybe didn't like so much

I actually wasn't aware of this ahead of time, but Kotlin is built on top of Java. (Or perhaps it's more accurate to say that Kotlin, like Java, is a language that runs on the JVM?) The first time I became aware of this was when my program threw an exception... and there was a mix of Java and Kotlin code in the call stack. I have no idea to what extent aspects of Java poke their heads up while working in Kotlin... but it's something that I'd want to understand more about before committing to using Kotlin for a real project, versus other available languages where that kind of thing is much more of a non-issue.

JetBrains' Kotlin template for AoC helpfully included a check call to test each problem's provided sample answer against the corresponding sample input. What wasn't so helpful was that a failure of the test just resulted in a generic java.lang.IllegalStateException : Check failed -- with no accompanying mention of the check's expected or actual values. I had to manually add a print statement to find out what the failure was. Maybe I was just missing something?

I ran into some trouble using the built-in pow to raise 2 to the power of a particular variable:

• It took me a little while to figure out why I was getting an Unresolved reference: pow.  I ended up googling for that exact phrase, while led me to discover that an import kotlin.math.* is needed. Not surprising in retrospect, but it would have been nice to a a prompt in Kotlin's error message about an import/reference possibly being needed. (I believe C# does this, probably among others.)
• The pow method (as of the current version, 1.6) can't be used on integers. After a little more confusion, I figured out that I had to use the literal 2.0 (instead of just 2), and then manually convert the result back to an Int after getting my result. Why make developers do that?
  

 

Verdict

Verdict? It's much too early for a verdict! I only worked with Kotlin for a few hours, on some toy problems, on my own! 

It was fun, though, to get to play with a new language! Kudos to the Kotlin team at JetBrains for their combination of the Advent of Code sponsored message, their blog post, and the Kotlin Advent of Code GitHub template, which taken together, were enough to get me to take a look at Kotlin!

And if you're a developer but have never taken a look at Advent of Code before, I strongly recommend checking it out! The event is extremely well crafted and executed each year. It's worth your time!

Git tip: Visual indicator of the 50-character git commit summary message limit

Although it's somewhat too short for my taste, I generally comply with the conventional wisdom of having the summary line of git commit messages be no more than 50 characters.

Even using an editor that shows the current position of the insertion point (such as vim with set ruler enabled), it's not readily discernible how much of the available 50 characters remains, relative to text that has been keyed in so far.

My solution to this has been to set up a ".gitmessage" commit message template that includes a comment showing the 50-character limit:

 
 
#                                                  |<-- 50 char summary line limit

As long as my summary message ends before that vertical pipe character, I'm good!