How to Think Like the SQL Server Engine: Building Wider Indexes to Deal with Bad T-SQL

In our last episode, we were running into problems with these two queries:

When SQL Server saw the function in the second query’s WHERE clause, it punted out with a 1-row estimate, which caused us problems. SQL Server did an index seek + key lookup where it wasn’t appropriate.

Well, if we’re not allowed to change the query, we could improve the situation by building a wider covering index. Right now we have this index:

But let’s say we add either of these indexes:

The resulting leaf pages of the index will look like this:

Now, look, dear reader: I know you. You and I go way back. I know how you think, and your first reaction is going to be to scroll down to the comment section and start banging away loudly on your keyboard, debating the difference between putting columns in the include versus putting them in the keys of the index. Hold that thought. We’re going to get to that in the next post in the series, I swear on my standing desk. For now, just roll with this as-is.

Add either one of those indexes – seriously, I don’t care which one you pick, but just only pick one – and the query plans now look identical for ANY date range, even for the entire table:

Ah, the magic of covering indexes. Make an index wide enough, and the queries will beat a path to your door. Now, SQL Server doesn’t have to worry about the tipping point because this index works perfectly no matter how many rows we’re bringing back.

So the plans are the same, right?

Well, no. Come closer, dear reader, and click on the Messages tab. SQL Server is hiding a dirty secret from you:

The bottom query actually read more pages even though it returned the same number of rows! Why is that? Well, hover your mouse over each index seek operator and look at the number of rows read. Here’s the top query:

In the top one, where our dates are clearly defined, SQL Server was able to seek directly into the index at the right point, read the 24,380 matching rows, and then bail out. In the bottom one, however:

\

SQL Server was like me at the Chinese buffet: it either didn’t know where to start, or it didn’t know when to finish, because it put way more rows on its plate than it really needed to. (For the record, even when crab rangoons are bad, they’re still good.) We’ll cover the reason for that (a residual predicate) later in this series.

For now, don’t sweat 60-70 logical reads between friends. (Also, are you going to finish those rangoons?) Don’t get hung up on this index seek not being as good as it could possibly be, because it’s still way the hell better than the 73,225 logical reads we were getting in the last post, and better than the 7,405 reads that a table scan would require.

60 reads isn’t a big deal,
but here are 3 things that are.

Here are three things you SHOULD sweat:

  1. The estimated number of rows (1) is still completely inaccurate, which will wreak havoc on further operations in your query, like if you join to additional tables
  2. Even when you’re looking at an actual plan, the cost percentages shown are still estimates – SQL Server never goes back and recalculates “actual” query costs
  3. The estimated query costs are pretty much irrelevant to reality:

SQL Server’s saying the top query is 97% of the cost of the batch, and the bottom query is 3%. That’s flat out not true: the bottom query does MORE reads than the top query! The whole reason the bottom query shows a lowly 3% cost is all back to SQL Server’s inaccurate estimate that only 1 row would return.

If the bottom query’s function really did only produce 1 row, AND if SQL Server was smart enough to only read the rows it really needed to read (which isn’t the case with this function), then sure, the cost of the bottom query would be cheaper. But it’s not – so the costs, much like my insistence that I can quit crab rangoons whenever I want, is a lie.

Now if you’ll excuse me, I have an errand to run. When I’m done with that, we’ll talk about whether columns should go in an index’s key or in the includes.

How to Think Like the SQL Server Engine: When Statistics Don’t Help

In our last episode, we saw how SQL Server estimates row count using statistics. Let’s write two slightly different versions of our query – this time, only looking for a single day’s worth of users – and see how its estimations go:

Both of those queries are theoretically identical in that they accomplish the same result by producing exactly the same rows – but their execution plans are different. On this one, you’ll probably want to click to zoom in, and play spot-the-differences:

The good news is that both plans are essentially doing an index seek + key lookup. The bottom one’s Constant Scan is where SQL Server is figuring out what the first date would be if it had to seek to a specific date/time to start the CAST process.

If you read a lot of blog posts – and you strike me as the kind of person who does – then you’ve probably read a post somewhere that said if you use functions in the WHERE clause, SQL Server won’t use an index, or that it won’t do an index seek. That’s not exactly true, as the bottom query plan shows – and it’s awesome! Hot diggety, I love SQL Server at moments like this.

The bad news is that they have very different row estimates. The top one estimates that 1,989 rows will match that date range, as shown by the “2443 of 1989” on the top Index Seek.

The bottom one has a very different row estimate: 1.

SQL Server brought back 2443 rows out of an expected 1.

And if you hover your mouse over the index seek to see more details, you can see that SQL Server knew doggone good and well what date it was looking for – and even that our CAST got converted (see what I did there.)

But the statistics didn’t help. SQL Server just couldn’t be bothered to guess how many rows were going to match the date of 2018-08-27.

In this case, the row estimate of 1 helped me out a lot because it encouraged SQL Server to lowball the row estimate, thereby encouraging an index seek + key lookup. But…what if that was a bad idea?

Let’s try another date.

This time, let’s aim for just 3 days later:

It just so happens 24,380 people logged in on that date, so now the top execution plan looks very different from the bottom one:

In this case, the 1 row lowball estimate backfired on me. The top query understood that over 24,000 rows would come back, so it did a clustered index scan. The bottom one’s 1-row estimate caused an index seek + key lookup – and as you’ll remember from your earlier lessons, dear reader, an index seek + key lookup is dramatically less effective as you add more rows, as statistics IO will show:

The top query did 7,405 reads (the entire table), whereas the bottom one did ALMOST TEN TIMES AS MANY.

The easier it is for SQL Server to read your query and quickly guess how many rows are going to come back, the faster of a query plan it’ll usually build. The more you layer in obfuscation – even the tiniest obfuscations that seem completely obvious to you – the less likely it is you’ll get a fast query plan.

Or, you’re gonna have to compensate by putting more work into your indexes – and we’ll do that next post, Building Wider Indexes to Deal with Bad T-SQL.

How to Think Like the SQL Server Engine: Using Statistics to Build Query Plans

In our last episode, SQL Server was picking between index seeks and table scans, dancing along the tipping point to figure out which one would be more efficient for a query.

One of my favorite things about SQL Server is the sheer number of things it has to consider when building a query plan. It has to think about:

  • What tables your query is trying to reference (which could be obfuscated by views, functions, and nesting)
  • How many rows will come back from the tables that you’re filtering
  • How many rows will then come back from other tables that you’re joining to
  • What indexes are available on all of these tables
  • Which indexes should be processed first
  • How those indexes should be accessed (seeks or scans)
  • How the data should be joined together between tables
  • At what point in the process we’re going to need to do sorts
  • How much memory each operator will need
  • Whether it makes sense to throw multiple CPU cores at each operator

And it has to do all of this without looking at the data in your tables. Looking inside the data pages themselves would be cheating, and SQL Server’s not allowed to do that before the query starts executing. (This starts to change a little in SQL Server 2017 & beyond.)

To make these decisions,
SQL Server uses statistics.

Every index has a matching statistic with the same name, and each statistic is a single 8KB page of metadata that describes the contents of your indexes. Stats have been around (and been mostly the same) for forever, so this is one of the places where SQL Server’s documentation really shines: Books Online has a ton of information about statistics. You honestly don’t need to know most of that – but if you wanna make a living performance tuning, there’s a ton of great stuff in there.

For now, let’s keep it simple and just read our stat’s contents with DBCC SHOW_STATISTICS:

I don’t expect you to know or use that command often – if ever! – but knowing just a little bit about what’s happening here will start to shape how you think about query execution.

The first result set:

  • Updated – the date/time this stat was last updated, either automatically or by your index maintenance jobs
  • Rows – the number of rows in the object at the time the stats were updated
  • Rows Sampled – just like a political pollster, as the population size grows, SQL Server resorts to sampling to get a rough idea of what the population looks like

The second result set shows the columns involved in the statistic – in our case, LastAccessDate, then Id. The first column is the main one that matters, though, because that’s where the histogram focuses, as the third result set shows.

Histograms sound complicated, but they’re really not so bad. I’ll walk you through the first two rows:

Each row is like a bucket.

The first row says, “The first LastAccessDate is August 1, 2008 at about midnight. There is exactly one row that equals this LastAccessDate.”

The second row says:

  • Range_Hi_Key = 2008-11-27 – which means this bucket consists of all of the users who have a LastAccessDate > the prior bucket (2008-08-01), up to and including 2008-11-27.
  • Eq_Rows = there is exactly 1 user with a LastAccessDate = 2008-11-27 09:02:52:947.
  • Range_Rows = there are 1,222 other rows in this bucket.
  • Distinct_Range_Rows = of those 1,222 rows, all of them have distinct LastAccessDates. That makes sense, since not everybody logs into StackOverflow.com at exactly the same time.
  • Avg_Range_Rows = for any given LastAccessDate in this range, it only has 1 matching row. So if you filtered for WHERE LastAccessDate = ‘2008-10-15’, for example, SQL Server will estimate that 1 row will match.

That’s where your Estimated Number of Rows comes from.

When our query does this:

SQL Server pops open the histogram and scrolls down to the bottom (literally, he uses one of those old scroll mouse wheels) to review what he knows about the data as of the last time this stat was updated:

He scrolls down to the end and says, “Well, you’re looking for 2018-09-02 04:00. I don’t really have a bucket dedicated to just that, but I know that:

  • Some of the rows in line 130 are going to match
  • All of the rows in lines 131 and 132 are going to match

He cues up some Violent Femmes, adds them up, and comes up with an estimated number of rows. To see it, hover your mouse over the Index Seek and look at the bottom right:

That’s a pretty doggone good estimate: he estimated 1,552 rows, and 1,576 rows actually came out. Good job, SQL Server. You get an A+ on your homework. Let’s pin this one on the fridge and take a look now at what your boy has done.

The easier of a time SQL Server has interpreting your query, the better job it can do. If your query tries to get all fancy and do things like functions or comparisons, then all bets are off, as we’ll see in the next episode.

SQL Server 2019 is out…now.

After just one release candidate, Microsoft has decided it’s ready to go, apparently! Well, kinda: the official build in the release notes is 15.0.2000.5, and there’s already a “servicing update” to 15.0.2070.41. I’m not sure I wanna know what’s going on there.

Anyhoo, SQL Server 2019 is available for download now.

This also means it’s time to pick winners for my SQL Server 2019 Release Date contest! First place goes to Aadhar Joshi, who correctly guessed 2019/11/04 first back in February, using the right date format and everything. Aadhar got Level 3 Bundle.

Runners-up with a Recorded Class Season Pass are Prather Surheley, Brent, Darren Scott, Pepe, Randy Hartwig, and Martin Henriksen, all of which also guessed the same date, but guessed after Aadhar.

My Annual Cyber Monday Sale is On Now!

Company News

The time is finally here, the moment you’ve been waiting for all year: your chance to save 73-82% on my training classes and online services:

Level 1: Fundamentals
$295
Level 3: Live Classes With Lab VM
$1,995

The fine, fine print

During the sale, we only accept credit card payments, not POs, checks, IOUs, or wires. This keeps our handling costs down and lets us do these deep discounts. If you can only pay via those other methods, you’re welcome to buy at our regular prices after the sales end.

Buying for someone else? The billing email address needs to be the email address of the person who will be getting the training. The buyer can use their own name/address/etc, but the email address absolutely has to be the person who’s gonna attend the classes. If you’re worried about getting the receipt, don’t worry – it’s shown to you during the checkout process, and you just need to print/PDF it then.

This is a subscription, but you can cancel whenever you like without talking to a human being. After logging in, go to My Subscriptions, and you can cancel your subscriptions, but keep using them up til the next billing cycle. Just click Cancel once, and it’ll show “Pending Cancelation” – that means you won’t be billed again. (If you click Cancel twice, you can outright cancel it period and it stops your access immediately. You probably don’t wanna do that.)

Still no sales to the EU due to the GDPR, sorry. I’d really hoped to get that in place for this year’s sale, but we couldn’t quite get there.

If you’re replacing an existing subscription, go into your subscriptions after you buy, and cancel the ones you no longer want. For example, if you had a previous Recorded Class Season Pass standalone subscription that would have renewed soon, you’ll want to cancel that (since you get the same stuff in these new bundles above.) With these deep, deep, deep discounts, we can’t tie these to your existing subscriptions – so no, if you have 3 months left on another subscription, that’s just going to get lost in the shuffle. (I lost sleep on that trying to get it to work, but it’s just too complex given that you can have just 1 of several products.)

How to register for live classes

Once your BrentOzar.com account has the Live Class Season Pass membership, you can register for the included classes for free. (If someone else bought the membership for you – like if the buyer’s email doesn’t match the student’s email – we have a little bit of manual work to do behind the scenes to set up your account.)

After you buy Level 2 or Level 3, and you want to register for upcoming live classes, go to the list of eligible classes, add the class/date you want to your cart, and check out at least 2 days before the class starts. If you see anything about putting in a credit card, STOP, and make sure you’re logged in with the student’s account. No money should be changing hands here. Well, unless you wanna tip us, but in that case we’d suggest tequila, or large t-shirts from your place of employment.

Cheating is a bad idea. If you try to register someone else, we will catch you when we audit the list the night before class, and we’ll have to send you a really sad email. (Trust me, I have to do this a few times a year. It’s stunning that people think they can still get away with that in the age of databases, but whatever.)

You can also start watching Instant Replay recordings right away by clicking Training at the top of the site. You’re absolutely welcome to start watching the videos before your class – it can help you get up to speed early, and help you re-absorb the material better during the live class, and ask better questions, too.

There are plenty of seats for Season Pass holders – don’t worry about registering ahead of time if you’re not sure if you’ll be in a class. The Season Pass gets you free entry into each class up to 2 times. (You can even attend a class more times than that, but then there’s a $100 fee to cover your VM.) Don’t panic and try to register for every class – there’s plenty of space for you, and once set, you don’t get a refund if you have to switch dates. (I have to rent your VMs in advance for dates, so if you don’t show up for a class, it doesn’t really matter – that money is gone.) When in doubt, wait it out – you can always register the week before a class. If it looks like a class is sold out, just shoot us an email and we’ll add a spot for you.

I would recommend doing the Mastering Indexes class first, then Mastering Query Tuning, then finally Mastering Server Tuning. That’s why I run the classes in that order, too – they alternate months 1-2-3, 1-2-3, 1-2-3. If you have to take ’em out of order, that’s okay – it’s just that in the Query Tuning class, I’m going to assume that you’ve already mastered the index tuning portion, so there will be some parts of the class that won’t make as much sense.

Got a question that isn’t answered here? Contact me. See you in class!

My annual Black Friday sale starts tomorrow. Here’s what you wanna know.

The time is almost here – the moment you’ve been waiting for all year. I’ve sharpened my pencils to take the prices as low as I can possibly take ’em:

Level 1: Fundamentals
$295
Level 3: Live Classes With Lab VM
$1,995

They all include a year of both SQL ConstantCare® and The Consultant Toolkit, two apps that make it easier for you to understand what’s happening on your SQL Server and help make it go faster. I did this because some companies will only pay for software – not training – and this way, you can say, “I’m buying software.”

So which one should you get?

If this is your first SQL Server training,
and you’ve never bought a class before, then start with Level 1: Fundamentals. You get access to dozens of hours of my recorded training classes including the Fundamentals of Index Tuning, Query Tuning, Database Administration, and more. If your company’s really cheap, you can even say, “I’m getting SQL ConstantCare and The Consultant Toolkit software so I can troubleshoot tough problems with the exact same tools Brent uses, and he’s throwing in training for free!”

If you’ve been to SQL Saturdays and conferences,
and you’re ready to start learning much tougher material, really mastering performance start to finish, step up to Level 3. This gets you all of my live online classes for a year straight, including my tough-as-nails Mastering Index Tuning, Mastering Query Tuning, and Mastering Server Tuning. These are the only classes in the industry where you’re challenged with real, live running workloads, and you have to turn around performance quickly to show what you’ve learned. For just $1,995, you get to take these classes repeatedly, all year long! It’s an amazing deal.

But if you really want to save a lot of money,
grab Level 2: Live Classes, No VM. You’ll have to build your own lab VM to follow along with the exercises, but you can save a ton of money doing it. You get absolutely the same quality experience, and the same running workloads – but it’s just your hardware instead of mine. This is a whole year of really in-depth training classes for less than what it’d cost for a conference hotel room, let alone the conference!

I worked hard to cut expenses as deeply as I could to make Level 2 possible. It’s one heck of a deal, and I’m excited to see if that helps y’all get it approved by managers.

Got questions? Hit the Black Friday page and I’ve written up the most common answers, or you can also leave a comment here too.

Registration opens tomorrow, and it’s open all November. The prices won’t get any lower during the course of the month -I wanted to give as much of a time window as I could for those of y’all who work for big, slow companies. And no, you can’t pay with an IOU – it’s credit cards only during these deep discounts, so get your move on!

Updated First Responder Kit and Consultant Toolkit for October 2019

This is a pretty doggone big release. There are a lot of improvements to sp_BlitzFirst, sp_BlitzCache, and sp_BlitzWho to prepare for a new release of the First Responder Kit Power BI Dashboard. If you’re using that today, I would strongly recommend updating to this new version of the FRK scripts – they fix a ton of bugs and start collecting data in a way that will make joins easier.

I wanted to ship the new Power BI Dashboard this month, but couldn’t quite get it working just the way I want to, so I’ll ship that one in next month’s release. Here’s a quick preview of the updated Query Details tab contents:

It has the new Power BI filters pane on the right – and yes, there’s a server filter, which means you’ll be able to centralize all of your data into a single repository, and navigate through it with easy filtering. On the left, I’ve got not only the cached plans from sp_BlitzCache, but also any live snapshots that were captured from sp_BlitzWho. Those show when the query is running, and its status along the way – really useful for those big long OPTION RECOMPILE queries that don’t show up in the plan cache, but you know they’re running and killing performance. Lots of good stuff coming there.

Breaking change: if you’re logging the data to tables, there’s a new JoinKey computed column added to all of the views, and to the BlitzCache, BlitzFirst, and BlitzWho tables to make Power BI Desktop joins easier. These columns will automatically be added by sp_BlitzFirst & friends when you upgrade, but if you’re doing any kind of ETL to put all of the Blitz% tables in one central location, you may need to add this column to your central location’s tables (and make sure you’re not doing SELECT * to ETL the data around.) For questions, feel free to hop into the #FirstResponderKit Slack channel. I’m out on vacation starting Sunday, but I’ll stick my head in there from time to time.

To get the new version:

Consultant Toolkit Changes

Updated the First Responder Kit with this month’s script updates, plus:

  • Fix: the Plans CPU ByHash tab wasn’t populating if the server didn’t have sp_BlitzCache installed.

sp_Blitz Changes

  • Fix: new @SkipBlockingChecks parameter defaults to 1. There’s a known bug in sys.dm_db_persisted_sku_features, which caused sp_Blitz to be blocked during columnstore index creation. I don’t expect Microsoft to fix that one quickly, so we just skip that check by default to be safe. (#2130)
  • Fix: if you stored your database files on a UNC path (\\) and wrote your backups on one too, sp_Blitz no longer reports that you’re storing backups in the same place as your databases. (#2141)
  • Fix: some heaps with 0 reads were reported as being actively used. (#2149, thanks Razvan Socol.)

sp_BlitzCache Changes

  • Improvement: @SortOrder = ‘query hash’ now takes a second sort order in a comma-delimited list, like @SortOrder = ‘query hash, reads’ to find the queries with multiple cached plans, sorted by most reads. (#2156)
  • Improvement: adds JoinKey computed column (ServerName + CheckDate) to output table to make Power BI Desktop joins easier. (#2162)
  • Fix: when called by sp_BlitzFirst, the CheckDates in the output table now match the exact same dates/times as sp_BlitzFirst’s output tables (even though it’s not exactly when sp_BlitzCache is running.) This enables joins in the Power BI Dashboard since we’re filtering by dates/times. This used to work back when we only called sp_BlitzCache with a single sort order, based on your top wait type, but once I switched to @SortOrder = ‘all’, it stopped working. (#2159)
  • Fix: when @SortOrder = ‘all’, @MinutesBack was being ignored. This is especially important for folks who use the Power BI Dashboard because with the default Agent job step, this would have resulted in way too much stuff showing up in the plan cache reports, instead of only showing things that executed in the last 15 minutes. (#2168, thanks Matt Rushton.)
  • Fix: in @SortOrder = ‘all’ output, the plans with the most spills were being incorrectly shown in the ‘memory grant’ category. They were still queries you wanted to fix, but the reason WHY they were a problem was wrong. (#2170, thanks Matt Rushton.)

sp_BlitzFirst Changes

  • Improvement: new @OutputTableNameBlitzWho logs sp_BlitzWho results to table. (#2159)
  • Improvement: new @BlitzCacheSkipAnalysis parameter defaults to 1 to replicate existing behavior, but if you want your sp_BlitzCache output tables to have the full query analysis, you can turn this off in your Agent jobs. I’m not a fan – this makes the Agent job take >30 seconds on even my laptop – but if you want it, there it is. (#2155, thanks Matt Rushton for the idea. The idea is good though!)
  • Improvement: BlitzFirst output table has 2 new columns: JoinKey computed column (ServerName + CheckDate) and QueryHash, plus added join keys to the output views to make Power BI Desktop joins easier. (#2162, #2164)
  • Improvement: added SQL Server 2017 XTP Perfmon counters. (Yes, they actually hard-code the version numbers into the Perfmon counter name, and 2019 uses the 2017 names.) (#2162)
  • Fix: if you passed on a @LogMessage with a @LogMessageCheckDate, we were, uh, ignoring it and just using the current time. (#2135, thanks Matt Rushton.)

sp_BlitzIndex Changes

  • Fix: reducing collation errors. (#2126, thanks chaowlert.)
  • Fix: more natural sorting for missing index recommendations, by impact. (#2143)

sp_BlitzLock Changes

  • Fix: because the system health XE session stores deadlock times in UTC, @StartDate used to assume you knew that. We now assume @StartDate is the server’s local time, and we also convert the outputted deadlock dates from UTC to the server’s local time too. (#2147, thanks DigitalOhm for the idea.)

sp_BlitzQueryStore Changes

  • Fixes: wasn’t running on Azure SQL DB, now does. (#2124 and #2136, thanks Paul McHenry.) Remember, though – our support policy on Azure SQL DB is pretty fast and loose since the DMVs change up there without warning.

sp_BlitzWho Changes

  • Improvement: added a @CheckDateOverride parameter so this can be called by sp_BlitzFirst and write the exact same CheckDates in the output table. This enables joins in the Power BI Dashboard, which requires joins on exact field contents. (#2159)
  • Improvement: adds JoinKey computed column (ServerName + CheckDate) to output table to make Power BI Desktop joins easier. (#2162)

sp_DatabaseRestore Changes

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. If you need a free invite, hit SQLslack.com. Be patient – it’s staffed with volunteers who have day jobs.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

How to Think Like the SQL Server Engine: What’s the Tipping Point?

In our last episode, I’d expanded our query to include DisplayName and Age – two columns that weren’t in our nonclustered index:

So as a result, I was getting key lookups in the execution plan:

And I spent a lot of time talking about the overhead that each key lookup incurs. Astute readers among the audience (as in, other people, not you) probably noticed the 2018-09-02 04:00 date and thought it was rather odd that I’d switch dates while telling this story.

Let’s query for just one hour earlier.

We were only getting 1,576 rows for Sept 2 4AM and after (because the data export I’m using was done on Sept 2, 2018) – let’s try 3AM:

And SQL Server ignores my index:

Even though only 2,256 rows are being returned, SQL Server would rather just scan the whole table than deal with the index seek + key lookup combo. There are 299,398 rows in the table – and even though we’re querying LESS THAN ONE PERCENT OF THE TABLE, the index gets ignored.

Is that the right decision?
To find out, let’s force the index.

I’ll run two queries back to back – first, letting SQL Server decide which index to use, and then second, forcing the nonclustered index by using a query hint:

Then check the Messages tab for our logical reads:

When we do a table scan, that’s 7,405 reads.

When we use the index, it’s 6,927 reads – assuming that we’re only getting 2,256 rows.

It’s tempting to say SQL Server was wrong, but here’s the thing: the table scan is a predictable 7,405 reads no matter how many rows are brought back from the query. It’s a safe decision when SQL Server just can’t be sure exactly how many rows are going to come back. If I query for just one more hour earlier, at 2AM:

Now that 2,917 rows match our filter, the clustered index scan pays off: it’s reading less rows than the combination of the index seek + key lookup. The key lookup costs more and more with every additional execution.

If we go back just one day, the difference becomes huge:

We’re only getting 16,325 rows – about 5% of the table – and yet if we use the index, we do so many logical reads that it’s like reading the entire table seven times over! That’s exactly the problem SQL Server is trying to prevent, dealing with key lookups that read more pages than there are in the table.

Our 2AM-4AM WHERE clause is hovering around the tipping point.

Even though we’re only bringing back less than 1% of the users, it’s already more efficient for SQL Server to scan the entire table rather than use our index.

In our examples, the 3AM query erred just a tiny, tiny bit on the conservative side, picking a clustered index scan when an index seek + key lookup would have been more efficient. In this example, who cares? It’s a difference of less than 500 page reads. I’m happy with that decision.

So how does SQL Server figure out which query plan – the index seek or the table scan – is more appropriate before actually executing the query? That’s where statistics come in, and we’ll talk about those next.

One solution is to widen the index.

When you see an index seek + key lookup in an execution plan, hover your mouse over the key lookup and look at the Output List:

Then start asking a few questions:

Can my query live with less rows? The whole problem with the tipping point is based on the number of rows we’re bringing back. If you can do pagination, do it. If you can restrict the number of rows outright or force users to be more selective with their searches, do that too. When you truly want to scale, everything is important.

Can my query live with less columns? Sometimes it’s a matter of folks doing a SELECT * out of laziness, but sometimes we really do need all of the columns. The more columns we need, the less concerned I am about the key lookups – because I simply can’t afford to maintain multiple copies of the full table, all sorted in different orders.

How large are the columns? If they’re big ol’ NVARCHAR(MAX), VARCHAR(MAX), JSON, XML, etc., then the discussion is over. I’m fine with the cost of the key lookup. I wouldn’t want to add them to indexes because they’ll simply take up too much space.

How frequently are the columns updated? SQL Server doesn’t have asynchronous indexes: every index has to be kept up to date at all times with the clustered index. If the Age column changes all the time, for example, then it may not make sense to duplicate it on multiple indexes since we’re going to have to grab locks on those indexes when we run updates. (Inserts & deletes don’t matter as much since we have to touch the indexes at that point anyway no matter how many columns they contain.)

If we do need those columns, should we add them to the nonclustered index? By adding them, we could get rid of the cost of the key lookup altogether.

There are no stone-carved right-and-wrong answers here, only a series of decisions and compromises to make in your quest for performance. When you first get started, you don’t really notice when SQL Server tips over from index seeks to table scans because your data is relatively small. As your data grows and your users want higher performance, that’s when this kind of thing starts to matter more.

I won’t dig too deeply into nonclustered index design in this blog post series, but if you’re interested in that kind of thing, check out my one-day Fundamentals of Index Tuning course.

Next up, let’s see how SQL Server figures out where the tipping point will be for a query.

How to Think Like the SQL Server Engine: What’s a Key Lookup?

In our last couple of queries, we’ve been using a simple query to find the Ids of everyone who accessed the system since mid-2014:

But Ids alone aren’t all that useful – so let’s add a few more columns to our query:

Now think about how you’re going to execute this query plan in plain English, as a human being. You have two copies of the table: the nonclustered index (black pages) with LastAccessDate, Id:

And the clustered index (white pages) with all of the columns in the table:

dbo.Users clustered index

One way is to use the nonclustered index first.

We could:

  • Grab the black pages, seek to 2014-07-01, and start making a list of the LastAccessDates and Ids we want
  • Grab the white pages, and for each Id we found in the black index, look up their Id in the white pages to get the extra columns (DisplayName, Age) that aren’t included in the white index

Here’s what that query plan looks like:

We read plans from right to left, but also top to bottom. The first thing SQL Server did was the Index Seek at the top right – that’s the seek on the black pages. SQL Server seeked to a specific date/time, read out the LastAccessDate & Id for each row, and that was the end of the Index Seek’s work. (He’s a totally separate standalone mini-program.)

Then, SQL Server took that list of Ids, and looked up each one of them on the white pages – that’s the Key Lookup operation. SQL Server used their clustering key – in this case, Id, because that’s what we built our clustered index on. (If you don’t build a clustered index, this same basic process still happens – but more on that in another episode.)

That’s why every nonclustered index includes the clustering keys.

When I first told you I created the black index, I said I did this:

But I didn’t have to. I could have just done this:

And we would have ended up with the same basic black pages. SQL Server adds the clustering keys into every nonclustered index because it has to be able to accomplish these key lookups. For every row it finds in a nonclustered index, it has to be able to track back to that one unique row in the clustered index.

I just told you I created the index on both columns in the beginning because I had to ease you into this. I didn’t wanna say anything, but you weren’t particularly with it that first morning you started reading this series.

Yes, I’m still holding things back from you right now even as I type this. No, I didn’t want to say that because it’s only going to make things awkward between us. Also, you’re not my only reader. You’re still the one I love the most, though. Now let’s just move on before you ask more questions.

Key Lookup operators obfuscate a lot of details.

I wish execution plans were three-dimensional: I wish operators popped off the page based on the number of times they were executed. You see that “Key Lookup” and you think it only happened once, but that’s not true at all. Hover your mouse over it, and you’ll see Number of Executions – that’s how many times it was actually performed:

Earlier, I wrote that when Microsoft paints stuff on a screen, they either put it in alphabetical order, or they load up the Data Cannon, fire it, and wherever the data goes, that’s where it ends up. Here, this tooltip was arranged by the Data Cannon, so we’re going to jump around a little.

  • Estimated Number of Executions is based on how many rows SQL Server thought was going to come out of the Index Seek. For each row we found, we’re gonna have to execute one Key Lookup.
  • Estimated Number of Rows – how many rows EACH key lookup will return.
  • Number of Executions – how many times we actually did it, based on how many rows actually came out of the index seek.
  • Actual Number of Rows – the total number of rows that came out of ALL key lookups (not each one.)

I know. Sometimes they say Actual, sometimes they don’t. Sometimes the numbers are per-execution, sometimes they’re total. If you’re looking for consistency and organization, you don’t use the Data Cannon. I’m sure they treat the internals of SQL Server with much more accuracy and care.

The cost of the key lookup is logical reads.

Each time we execute the key lookup – in this case, 1,576 times – we’re opening up the white pages and doing some logical reads. To see it, let’s run a no-lookup query (that only gets Id) and a multi-column query (that gets DisplayName and Age too) and compare their logical reads:

The top query only does 7 reads, and the bottom one does 4,842! Those 1,576 key lookups needed to do about 3 logical reads each. (Why not just 1 read each? Because they need to navigate a tree structure of pages to find exactly the right page that has the data they want – there’s a couple/few pages of overhead each time you need to seek in an index. We’ll talk about alternatives to that later too.)

The more rows your index seek returns, the more likely it is that you won’t get this execution plan at all. Notice that in this post, I didn’t use 2014-07-01 as my filter date – I used something much more recent. You’ll learn why in the next episode.

How to Think Like the SQL Server Engine: So Index Seeks are Great, Right?

In our last episode, we added a nonclustered index, and now even though this query has a filter and an ORDER BY, it hardly did any work at all – just a lightweight index seek:

Ah, index seeks – nothing better than that, right?

Well, not exactly. Let’s make one tweak to the WHERE clause and find all of the people who accessed Stack Overflow going all the way back to the days of programming your ox team:

We still get an index “seek”:

Yes, it’s a seek, even though it’s reading every single row in the index. You probably thought that would be a scan, but it’s not.

Index seek doesn’t mean lightweight.

Index seek only means SQL Server is going to jump to a specific point in the index and start reading.

It has absolutely nothing to do with how few or how many rows you’re going to read. You can do an index seek and read just one row, or read all of the rows in the table.

If you’ve been looking at an execution plan and going, “seek seek seek, all seeks here, guess it can’t get any better than that,” then you’ve been missing out on something pretty big.

Index scan doesn’t mean reading the entire table, either.

Let’s get any 10 users:

We get a clustered index scan – meaning, it’s reading the entire table, right?

Nope – SQL Server starts scanning the table, but it can bail as soon as it’s found enough rows to deliver your query results. Hover your mouse over the clustered index scan, and it says “Number of Rows Read: 10.” If your number of rows read equals the number of rows your query outputted, that’s great! I can’t possibly design a better index to support this query. A clustered index scan is absolutely perfect here.

Here’s what seeks and scans really mean:

  • Index seek: “I’m going to jump to a specific point and start reading.”
  • Index scan: “I’m going to start at either end of the table and start reading.”

Here’s what they don’t mean:

  • How many rows we’re going to read
  • Whether we’re seeking on all of the columns in the index, or just the first one (more on that later)
  • Whether the index is good or bad for this query

I can’t emphasize this enough: you can’t make index judgements based purely on seeks or scans. This is especially true when you start looking at index usage metrics from tools like sp_BlitzIndex: we’re simply reporting how the index is being accessed. You can’t say, “This index is getting seeks, so it’s perfect,” nor can you say, “This index is getting scans, so the keys must be in the wrong order.” You’ve gotta dig deeper than that to see how much data is moving around – and that just isn’t something the DMVs track. You’ve gotta look at query plans closely to decipher what’s going on.

Next, let’s add some columns to our query and force SQL Server to make a tough decision.

SQL Server 2019 Standard Edition Feature Limitations Are Out

The features list by edition is out, and there’s a disclaimer at the top:

This content is being updated for SQL Server 2019. The information in the article is not final.

Which means if you’re really angry, and your company buys a lot of licenses, now’s your time to raise holy hell with your Microsoft reps if there’s something in this doc that disappoints you.

The most important stuff:

  • Standard Edition is still capped at 128GB RAM.
  • Accelerated Database Recovery is in Standard Edition.
  • Automatic tuning, batch mode for row store, adaptive memory grants, adaptive joins, and memory-optimized TempDB are Enterprise Edition only.
  • Automatic inlining of scalar functions isn’t listed, but given the above, it’s probably Enterprise Edition only as well. Update: scalar UDF inlining is now listed, and it’s in Standard Edition! Woohoo!

Update: how I feel about the limitations

Looking back at this, I realize that I didn’t editorialize this at all – I didn’t say how I felt about it. I’m actually totally cool with it except for two things, and I’ll get to those in a second.

Microsoft’s gotta make money just like you do, and they need big differentiators between Standard Edition and Enterprise Edition. They need to have serious reasons as to why you’d spend the extra $5K per CPU core. I think automatic tuning, batch mode for row store tables, adaptive joins, and memory-optimized TempDB are all fair limitations. If you’re hitting the kinds of performance issues where you need those features in order to survive – not just that you want them, but that you have to have them – then you should probably pony up for Enterprise Edition.

The vast, vast majority of shops have survived just fine without those features for decades. Sure, you’ve hit performance limitations that have caused you to do some performance tuning, and those took you some time. Maybe even a lot of time. But that’s the value proposition of Enterprise: when you hit big performance issues, you could EITHER tune it manually yourself, or you could pay Microsoft to improve the performance for you. I think it’s a fair trade.

First, Basic Availability Groups are a joke. I know, they’re supposed to replace database mirroring, but the requirement of a separate Availability Group for every database, and a separate listener, and a separate IP address – those are just ridiculous. Let people put multiple databases in the same Basic Availability Group and fail them over together. (I’m not asking for multiple replicas or read replicas – I get that both of those are great differentiators for Enterprise. I know, some readers are gonna say they need multiple replicas for both high availability and disaster recovery, and I don’t think that’s unreasonable, but I’m not fighting that battle today.)

Second, there’s one area that’s unforgivable: memory grants. SQL Server itself makes horrible, horrible decisions around memory grants, especially around over-estimation. Standard Edition has the perfect storm of problems:

  1. It’s capped at 128GB RAM (especially around query workspace)
  2. You can’t use Resource Governor to cap query grants
  3. SQL Server can’t learn from its mistakes because Standard doesn’t get adaptive grants

I would totally understand if we had ANY way at the system level to fix SQL Server’s bad memory grant decisions, but the only option we have is by changing the queries themselves (by rewrites or by adding grant hints.) Microsoft should give us SOME way to fix these bad grants at the system level – and putting any one of the 3 above options in Standard would be fine.

Or, you know, they could fix the query optimizer to stop granting so many queries 25% of the buffer pool every time they run a query. But let’s be realistic here.

How to Think Like the SQL Server Engine: Adding a Nonclustered Index

When we left off in the last post, our users kept running this query, and they want it to be really fast:

Let’s pre-bake the data by creating a copy of the table sorted in a way that we can find the right rows faster:

This builds a separate copy of our table (also stored in 8KB pages) that looks like this:

This index slows down inserts and deletes.

The first thing to notice is that you now have two physical copies of the table. Every time you insert a new row, you have to add it in two places. Every time you delete a row, you have to delete it in two places. You’ve effectively doubled the amount of writes that your storage has to do. (Updates are a little trickier – more on that in another post.)

The second thing to notice is that the nonclustered index (black page) is denser: you can fit more users per 8KB page on that because we chose to store less fields. You can see that by running sp_BlitzIndex and looking at the top result set, which shows how big each index is:

The clustered index (marked CX PK) has ~300K rows and takes up 58.1MB space on disk because it contains all the columns of the table.

The nonclustered index has the same number of rows, but it only takes up 5.3MB space because we’re only storing LastAccessDate and Id on it. The more columns you add to an index – whether they’re in the key, or in the includes – they make the object larger on disk. (I’ll talk more about index design as this series continues.)

But this index pays off dramatically for selects.

Try our select query again – and here, I’m running two queries back to back. I’m running the first query with INDEX = 1 as a hint to show the cost of the clustered index scan. (Index #1 is your clustered index.)

Our new bottom plan is much simpler:

  • We get a seek, not a scan – because SQL Server is able to jump right to the rows where LastAccessDate > 2014/07/01. It doesn’t need to scan the whole object to find ’em.
  • We don’t need a sort – because as we read the data out, it’s already sorted by LastAccessDate.

The index seek’s cost is lower for two reasons: it doesn’t need the sort, and it reads less 8KB pages. To see how much less, let’s check out the messages tab:

The clustered index scan read 7,405 pages and took 110ms of CPU time to do the ORDER BY.

The new nonclustered index only read 335 pages – like 20X less! – and didn’t need CPU time for ORDER BY. Really good index designs like this are how you can quickly get 20X (or much higher) improvements in your queries without rewriting the queries. Do I wish every query was perfectly tuned? Sure – but even if it was, you’re still gonna need to give the engine a helping hand by organizing the data in more searchable ways.

This index is called a covering index.

The index perfectly covers this query, satisfying everything it needs in a quick, efficient way. The term “covering” isn’t a special kind of index that you create with different syntax – “covering” just refers to the combination of this index AND this query. If you change the query at all – like we’re going to do here shortly – then the index may no longer be covering, and we’ll have to do more work again.

So our plan has an index seek – that’s as good as a plan can get, right? No, as we’ll see in the next episode.

How to Think Like the SQL Server Engine: Running a Query Repeatedly

Earlier in this series, we ran a query with ORDER BY, and we realized that it was CPU-intensive work that tripled the cost of the query:

Now, let’s run that query a bunch of times. In SSMS, you can add numbers after the GO, and SSMS will run your query a number of times. I’ll run it 50 times:

I explained SET STATISTICS IO ON in the first post, but I’ve added a new option here: TIME. This adds more messages that show how much CPU time and elapsed time the query burned:

SQL Server executes the query over and over, reading the 7,405 pages each time, and doing the sort work each time.

When I was a developer, I used to be totally okay with constantly fetching data from the SQL Server. After all, I’d just run the query before, right? The data’s in cache, right? Surely SQL Server caches that sorted data so that it doesn’t have to redo all that work – especially when my queries had been doing a lot of joining, grouping, filtering, and sorting.

SQL Server caches raw data pages,
not query output.

It doesn’t matter if the data hasn’t changed. It doesn’t matter if you’re the only one in the database. It doesn’t even matter if the database is set to read-only.

SQL Server re-executes the query again from scratch.

And similarly, if 500 people are running the exact same query at the same exact time, SQL Server doesn’t execute it once and share the results across all of the sessions. Each query gets its own memory grant and does its own CPU work.

This is one of those areas where Oracle has us beat. Sometimes folks will ask me what my favorite database is, and I gotta confess that if money didn’t matter, I’d probably be really interested in Oracle. Check out their Result Cache feature: you can configure a percentage of memory to cache query results to be ready-to-go when apps keep rerunning the same query. However, at $47,500 per CPU core for Enterprise Edition licensing (check out the price sheets), I’m afraid I’m not going to be sampling any of that caviar anytime soon.

One way we can solve that problem is by caching the results in the application layer:

But another way – and the way I use most often – is to pre-bake the data in a way that makes queries run faster. That’s a nonclustered index, and I’ll cover those next.

How to Think Like the SQL Server Engine: The Perils of SELECT *

In our last post, we ran a query with an ORDER BY, but we only got one column in the SELECT:

The estimated cost was about $18 Query Bucks because SQL Server had to:

  • Scan the entire clustered index, yelling out the Id and LastAccessDate of each row
  • Sort that list by LastAccessDate

Now, let’s change just one thing about the query – what we’re selecting:

And run both of the queries back to back to get their actual execution plans:

The basic execution plan of both queries is pretty similar, aside from the fact that the bottom one went parallel. SQL Server realized that sorting all this data was going to be a heck of a lot more work, so it split the work across multiple CPU cores.

This query sucks in a few different ways.

SELECT * can read more data. I know what you’re thinking: both queries have to read all of the 8KB pages in the table, right? No – go back to the first post in the series when I introduced the Users table. I mentioned that the AboutMe field, a big ol’ NVARCHAR(MAX), might be so large that it ends up getting pushed off-row: stored in different 8KB pages. Our SELECT Id query didn’t need to read those extra pages – but when we SELECT *, we do.

SELECT * can sort more data. SQL Server can’t just sort the LastAccessDates – it sorts the whole rows around. That means it’s going to need more CPU time, more memory, and do more spills to disk if it gets those memory estimates wrong.

SELECT * can take more time to output. This was always the thing I focused on as a database administrator. I would tell my devs, “Don’t select fields you don’t need, because it takes longer to yell that data out over the network.” Today, that’s the least of my problems: I’m much, much more concerned about the CPU time and memory grants consumed by the sort operator.

Your first clue about just how bad the SELECT *’s sort operator sucks is the 97% cost of the sort – but it’s not 97% of the same query cost. The original query cost was $18 Query Bucks, but check out the cost of the SELECT * – IT’S ALMOST NINE HUNDRED QUERY BUCKS.

If query bucks were real dollars, I could have bought my first car with the cost of this SELECT *.

I hate SELECT *.

It isn’t about the star itself – it’s about lazily getting all of the columns, including ones you don’t need, and then forcing SQL Server to sort them.

And just because you didn’t put an ORDER BY in your query doesn’t mean you don’t have a sort, either – Erik wrote about sorts that get injected into your plan even if you didn’t ask for ’em.

For the next post in the series, we’ll go back to selecting just the Id. It’s not that I’m only going to allow my developers to just select Ids and nothing else – I understand that we gotta get data out of the database. However, when I’m dealing with query tuning, and I see a SELECT * (or a big ginormous list of columns), I’m going to start the tuning process by asking if we really need all of those fields. Even with Entity Framework, you can pick the columns you want. Does it take a little more work on your part? Sure – but you’re a lot cheaper than SQL Server’s CPU licensing or Azure SQL DB’s pricing, especially as your app starts to scale.

So next, let’s get just the Id, but run the query repeatedly.

[Video] What’s New in SQL Server 2019

We must be getting pretty close to the official release of Microsoft SQL Server 2019. There are less than 80 days left in the year. (Interestingly, it’s been almost 60 days since Release Candidate 1 shipped – that’s unusually long for a delay between builds. Hmm.)

With the clock ticking down, now’s a good time to take a 40-minute look at some of my favorite new features: deferred compilation for table variables, adaptive memory grants, adaptive joins, and air_quote_actual plans:

To learn more about this stuff:

How to Think Like the SQL Server Engine: Adding an ORDER BY

We started out the How to Think Like the Engine series with a simple query with a WHERE clause:

Now let’s add an ORDER BY:

Here’s the updated plan – note that the query cost has tripled to $17.72 Query Bucks. Let’s dig into why:

We read the plan from right to left, hovering our mouse over each operator. Each operator is kinda like a standalone program that has its own dedicated work to do, and produces a specific output.

The first thing that happens is the Clustered Index Scan.

At the top right, the clustered index scan is exactly the same as it was in the last query. Hover your mouse over that and there are a lot of juicy details that we didn’t really dig into before:

  • Predicate: the clustered index scan mini-program is only going to return rows where the LastAccessDate > ‘2014-07-01’. (Pretty nifty how it changed the date format and added the time, right?)
  • Estimated Number of Rows: 149,259
  • Estimated Number of Rows to be Read: 299,398 (we have to scan the whole table to find the people who match)
  • Output List: Id, LastAccessDate (because upstream of this mini-program, other mini-programs are going to need both Id and LastAccessDate. Specifically, the Sort operator – which happens next – is going to need to sort all these rows by LastAccessDate.)

The data flows out of this scan operator, and flows into the next one: Sort.

The second thing that happens is the Sort.

The sort’s input is the 148,328 rows of Id & LastAccessDate that came out of the Clustered Index Scan, and they’re not sorted in any kind of order – but our query asked for them to be ordered by LastAccessDate. That’s where the Sort’s work comes in. Hover your mouse over it to see what’s happening in that mini-program:

A few points of interest:

  • Order By (down at the bottom): the main goal of the sort
  • Estimated Number of Rows: 149,259
  • Estimated I/O Cost: $0.01 (because there’s not much I/O to do if you’re sorting 150K rows)
  • Estimated CPU Cost: $11.7752 Query Bucks (because sorting is mostly CPU work)

But note that those estimates above are all based on 149,259 rows. If way more rows came in (or less), then our actual work would have been way more (or less.) This is a good time to stop and mention that you don’t see Actual Cost numbers here in Query Bucks: SQL Server doesn’t go back and re-cost stuff after the work has been completed. Anytime you see a cost – even on an actual plan – it’s just an estimate that was done before the query started. Just like you, SQL Server doesn’t later confess just how over budget or late its work was.

So why did the query cost triple from $6 to $18?

It all comes back to the $11.7752 Query Buck cost of the Sort operator, which is brand new in this plan. Sorting data is hard work.

I jokingly say that SQL Server is the world’s second most expensive place to sort data – second only to Oracle. Next time someone complains about the $7K per core cost of SQL Server Enterprise Edition, remind them that Oracle is $47K per core. $47K. Hoo boy.

If you need to sort data, try doing it in the app tier instead. Developers are really good about scaling out application work, and their app servers don’t cost $7K per core. I’m all about going to bat for my developers to get them more & faster web servers because those are easier/cheaper than scaling out sorting in SQL Server. If the query doesn’t have a TOP, then it probably shouldn’t have an ORDER BY.

What’s that, you say? You’ve never heard Microsoft dispense that advice?

Microsoft, the company that charges $7K per core to do your sorting for you? They haven’t told you about that? Huh. Must have been too busy about telling you how you can now do R, Python, and Java inside the SQL Server, all at the low low price of $7K per core. Funny how that works.

And the more columns you add, the worse it gets. Next up, let’s try SELECT *.

Why Query Plans Can Look Different on Different Servers

In the first post in my How to Think Like the Engine series, I started by doing a pretty simple query:

But I noted that if you were following along on your computer, you might see a different execution plan. If I tweak just one thing about my SQL Server, I see a different plan:

Fully optimized plan

That’s the SAME query, hitting the SAME table with the SAME data, on the SAME server, with the exact same data – but suddenly now the query plan goes parallel (as indicated by the Parallelism icon), and I get a missing index request. What gives?

In this case, I changed Cost Threshold for Parallelism.

CTFP is a server-level setting that says, “If a query costs more than this, then consider parallelizing the query’s work across multiple cores.” Most SQL Server setup checklists will suggest that you raise it from the default of 5 up to something more like 40, 50, or 75. I explain the setting in more details over in my post, What is the CXPACKET Wait Type?

In the first screenshot – where the query goes single-threaded – I had my CTFP set at 50, which means that SQL Server wouldn’t consider parallelizing it if it had a lower cost. If I hover my mouse over the first query’s SELECT operator in the execution plan, I can see the Estimated Subtree Cost:

Estimated Subtree Cost

Estimated Subtree Cost is SQL Server’s guess of how much CPU and IO work will be required to execute the query. SQL Server doesn’t know how fast your CPUs are or how fast your storage is, and these numbers are hard-coded to kinda old hardware. We needed a way to communicate what these numbers mean, though, so Kendra Little came up with the term “Query Bucks.”

This query costs $5.79 Query Bucks, which is less than our $50 Cost Threshold For Parallelism, and it was a pretty simple query to optimize, so SQL Server bailed out early on building a query plan. If you right-click on the SELECT operator and click Properties, you can see that in the Optimization Level property being TRIVIAL.

SQL Server is in a race against the clock: the faster it can ship your query results, the happier you’ll be. There was no sense putting more time into building this query plan since it was seen as trivially simple – any additional time spent compiling the plan would be just wasted.

When I dropped Cost Threshold for Parallelism back down to the default of 5:

  • The query cost of $5.79 was suddenly higher than CTFP ($5)
  • SQL Server went deeper into query optimization (the Optimization Level switched to “FULL”)
  • Parallelism was injected into the plan
  • The missing index recommendation code kicked into action

And now, SQL Server decides to build a better query plan for my configuration settings – that part is really important to understand:

The cost is now HIGHER – $5.87 Query Bucks – but SQL Server believes it’ll complete more quickly by spreading the work across multiple threads. It might be wrong – but it would be wrong based on YOUR input, which is what drove the Cost Threshold for Parallelism setting. By itself, SQL Server just doesn’t know your workload well enough to decide which queries should go parallel and which ones should stay multi-threaded in order to balance load as efficiently as possible across multiple cores. It needs your tender loving hand on the CTFP dial.

Change anything about the server, and your plans can change.

Oh sure, there’s the obvious stuff:

  • Server-level configuration settings (like sp_configure options)
  • Hardware capacity (core count and memory availability change plans)
  • SQL Server major version (2019, 2017, 2016)
  • SQL Server patch level (yes, even Cumulative Updates change optimizer behavior)
  • Data quantity – how many rows you have in tables
  • Data distribution between different values

But that’s really only scratching the surface. SQL Server is incredibly complex, and has an amazing number of ways that you can influence query plan behavior without even meaning to. For more examples, check out 5 Ways to Change Execution Plans Without Tuning.

This has implications for how you do performance tuning.

Go easy on untested changes. Just because you believe that changing a setting is safe doesn’t mean it won’t have unexpected performance implications.

Try to keep production & development servers as identical as practically possible. The closer your servers match, the closer your query plans will match. If production is SQL Server 2017 with 48 cores and 2TB RAM, but development is SQL Server 2019 with 4 cores and 16GB RAM and a tiny subset of production data, you don’t stand a snowball’s chance in hell of getting execution plans that are even remotely similar. You’re going to have a much harder – not impossible, just harder – chance of getting identical query plans. But at the same time…

Most of the time, you don’t need exactly identical query plans. Generally, a crappy query is a crappy query regardless of the SQL Server version or settings. In this blog post series, for example, if you’re following along with my queries, you don’t need to get exactly the same query plan in order to get the point. Just focus on the basics: is the query getting better or worse?

I kinda feel like this is going into more technical detail than really necessary to introduce you to internals in the How to Think Like the Engine series, but I needed to get that out of the way for those of you who wanna follow along with the query plans on your own machines.

In the next post, we’ll get back to expanding our query with more work and seeing how the plans change.

How to Think Like the SQL Server Engine

You’re a developer or a DBA, and you’re comfortable writing queries to get the data you need. You’re much less comfortable trying to design the right indexes for your database server.

In the next several posts, you’ll learn:

  • The differences between clustered and nonclustered indexes
  • How (and when) to make a covering index
  • The basics of execution plans

If you prefer watching videos rather than reading, you can watch my How to Think Like the Engine training videos free too. I just decided to write these out as a series of blog posts because I get so many questions about how this stuff works – from folks who don’t like sitting around watching videos. Let’s get started!

I’m using the Users table in the Stack Overflow database.

StackOverflow.com open sources your question & answer data, and I import that into a few free SQL Server databases you can download. In this series, I’m going to use the small 10GB StackOverflow2010 database (1GB zip file) – if you want to follow along and get similar results & query plans, use that one.

The Users table holds exactly what you think it holds: a list of everybody who’s created an account at StackOverflow.com. The schema is pretty simple:

  • Id – an identity field, starts at 1 and goes up to a bajillion
  • DisplayName – not unique, just the name you go by, like “Brent Ozar” or “Alex”
  • LastAccessDate – the last time you opened a page at StackOverflow.com

In SQL Server, most objects are saved in 8KB pages, and each 8KB page is dedicated entirely to one object. (We’ll save the nuances of columnstore indexes, Hekaton, and other niche objects for other blog posts.) You can think of these 8KB pages just like printed-out pages of an Excel spreadsheet, like this:

dbo.Users clustered index

When I teach this class in person, I actually give out printed-out pieces of paper with this stuff. You can download that 5-page PDF and print it out to help follow along and visualize the data as we go through demos. Just stick with the white piece of paper at first.

That white grid above is the clustered index of the dbo.Users table. In the Stack Overflow databases that I publish, the Id column is set up as the clustered primary key of the table.

This clustered index on Id *is* the table.

Look down the left side of the sheet: the rows are sorted by Id, and the sheet has all of the columns in the table. The clustered index IS the table, and these 8KB pages are the same whether they’re on disk or in memory.

Each database’s data files (MDF/NDF) are just a string of 8KB pages from start to finish. Some of the pages are used by SQL Server to save metadata about the database, but most of ’em are your indexes.

You can see how many pages a table contains by running a query against it like this:

The first line there, SET STATISTICS IO ON, adds info in SQL Server Management Studio’s “Messages” tab that tell you the number of 8KB pages SQL Server had to read to execute your query:

The “logical reads 7405” means SQL Server read 7,405 8KB pages. Generally speaking, the less pages SQL Server has to read to execute your query, the faster your query will go.

7,405 pages is about 15 reams of paper.

You know those 500-page packs of paper that you put into the copier or the printer? (No? Do you remember copiers and printers? Honestly, me neither.) The Users table is one of the smallest tables in the Stack Overflow database export, but it’s still 15 of those packs.

As we work through demos in the upcoming posts, I want you to visualize a stack of 15 reams of paper over in the corner of your room. When I ask you to query the table, I want you to think about how you’d execute that as a human being facing data spread across 15 reams of paper. It’d be a hell of a lot of work, and you wouldn’t be so eager to go grab the first piece of paper to start work. You’d wanna build a really good plan before you go tackle that stack of paper.

That’s why SQL Server tries to build good execution plans.

In SSMS, click Query, Include Actual Plan (or hit Control-M), run the query again, and you’ll get an “Execution plan” tab:

Read the plan from right to left: SQL Server scanned the clustered index (aka the white pages, the table), pushing 299,398 things out to the SELECT statement. It had to scan the whole table because we asked for the whole thing – well, we only asked for one column (Id), but the Ids were scattered across all the pages, so we had to read ’em all.

You wouldn’t normally write a query without a WHERE clause though, so let’s add one.

Let’s be good and add a WHERE clause.

Let’s only get the people who accessed the system after July 1, 2014:

And my execution plan looks the same:

(If you’re following along on your own computer, your query might look a little different – I’ll explain why in the next post.)

Both of my queries – with and without a where clause – read the same number of pages. Here, I’m running both queries back to back, showing their stats in a single Messages tab:

And if you look at the 8KB page again, it kinda makes sense why we have to scan the clustered index in order to find rows with a LastAccessDate > 2014/07/01:

dbo.Users clustered index

Even though our query returns less rows, we still have to do the same amount of work because the data simply isn’t sorted in an order that helps our query. If we’re going to frequently query by LastAccessDate, and if we want that query to run faster, we’re going to need to help SQL Server out by designing an additional copy of our table, pre-sorted in a better way: a non-clustered index.

Disclaimer: I’m simplifying a lot of stuff here.

In this blog post series, I’m going to try to cover a lot of the most important ground, fast. To do that, I’m going to leave out a lot of details that you would probably find interesting – but look, this is a blog post, not a book. It’s a starting point for your learning journey.

However, there are a few disclaimers I need to make or else the armchair bloggers are going to complain:

  • Note that the AboutMe column is clipped off. The AboutMe column is an NVARCHAR(MAX) because Stack Overflow lets you store a huge amount of stuff inside your profile. In SQL Server, large AboutMe data can be moved off-row: saved on separate 8KB pages. When you design tables with big columns like VARCHAR(MAX), NVARCHAR(MAX), XML, JSON, etc., you can end up paying a performance price as SQL Server jumps around to read all that data. Ideally, design columns just wide enough to store the data you need, and no more.
  • The 8KB pages don’t use Excel-style grids. SQL Server needs to cram as much data in per page as it can, so no, it doesn’t organize rows and columns on the 8KB page like a spreadsheet. Similarly, the column headers aren’t stored in plain text on each page, nor are nulls aren’t stored with all capital NULL, hahaha. I’m just using a spreadsheet as a visualization tool because it helps you understand how things work.
  • The 8KB page I’m showing is called a leaf page. Indexes have more kinds of pages too, like B-tree structures that help the engine rapidly find the right page when it’s seeking for a specific row. However, even if you just focus on the leaf pages and don’t learn anything else, you can still do a phenomenal job of improving your database performance – and that’s the goal of this series, getting you up to speed on the most important stuff quickly.

And there’s one more complex thing I need to tackle, but it needs its own blog post, which is the next one in the series: why your query plan might not have exactly matched mine.

I’m coming to Gothenburg & Oslo next summer.

The Nordic countries are some of the most beautiful places I’ve ever seen. Erika and I are scheduling a few trips over there in 2020, and the first one I can announce is SQL Saturday Gothenburg and SQL Saturday Oslo.

I’m teaching my one-day workshop before each event: Performance Tuning in 21 Demos. You’re a developer or DBA who wants to tune indexes and queries to make your application go faster. You don’t like learning by watching slides or reading white papers – you want to jump right into the code and see what happens. You’re the kind of person who loves watching YouTube videos that start with, “Hold my beer and watch this.” Attendees get a year of my Consultant Toolkit.

These pre-cons are an awesome deal for one day of in-person SQL Server training, and they usually sell out. I’m announcing these a year in advance so that those of you who want to travel can schedule it in advance:

Erika and I are making a big vacation out of it, flying in a week early to take a Hurtigruten cruise around Norway and taking the Bergensbanen train, reputed to be one of the most beautiful train rides in Europe.

You can even watch the entire seven hour journey courtesy of the slow TV movement, something I’ve found really wonderful lately:

See you in Scandinavia!

New Free Azure and SQL Server 2019 Training from Microsoft

Folks often email me and ask, “Brent, why don’t you do training classes on Azure?” The answer is simple: the products change faster than I can recoup the cost of building the training material. By the time the class gets enough revenue to pay off the downtime for building an Azure (or AWS or Google) training class, the material’s out of date. I’m sure you’ve sat through an online video where you’ve noticed things that are already no longer true.

But there’s a company who CAN afford to constantly build new stuff without charging you for it: Microsoft!

The sqlworkshops Github repo is chock full of courses built by Microsoft names that you’ll probably recognize like Bob Ward and Buck Woody. It’s all open source, and written in a way that you can follow along. Now, granted, that’s not necessarily as good as an instructor-led class, because you’ve gotta teach yourself, but … you don’t have to pay for an instructor, so there’s that.

Their latest course is a one-day workshop called SQL Server 2019 Ground to Cloud, and it covers:

  • What’s new in SQL Server 2019
  • Working with Big Data Clusters
  • SQL Server in Azure – the differences between Azure SQL DB, elastic pools, VMs, etc.
  • Migrating data from SQL Server into Azure
  • Which databases to use for different business requirements

You can download the 219-page workshop PDF here, get the PowerPoint slides, and browse the Github repo with the class resources. Don’t be fooled by the times listed to complete each module: if you’re doing these classes yourself, they’re going to take you more time, and that’s not a bad thing. Lots of the slides in these presentations are going to prompt you to jump in a different direction and read up on related features & documentation that you want for your own business projects. That’s a good thing: it means this “one-day workshop” is really something you can dive into for several days, or a few weekends.

If you’re a consultant, your clients are probably going to ask you questions that are covered in these workshops. Microsoft *wants* you to be able to present these workshops to your clients to get ’em the answers they want. These workshops aren’t just about empowering your self-paced learning: they’re also about empowering you as a consultant and/or trainer to get the knowledge out there.

And if you like this, you’ll probably like the rest of the sqlworkshops Github repo. It’s all free – you can’t beat that. (I certainly can’t!)

Menu
{"cart_token":"","hash":"","cart_data":""}