“I’m getting index seeks. Why are my row estimates still wrong?”

Execution Plans
11 Comments

If you’ve got good indexes to support your query, and statistics to help SQL Server guess how many rows will come back, how can SQL Server still come up with terribly incorrect row estimates?

To demonstrate, I’ll use the 2018-06 version of the Stack Overflow database, but any recent version will work as long as you understand the problem with this demo query:

I’m asking SQL Server to find users created in the last month (because I’m dealing with the database export that finished in June 2018), who have accumulated at least 100 reputation points.

SQL Server knows:

  • There have been a lot of users created in the last month
  • There are a lot of users who have > 100 reputation points

But he doesn’t know that that’s a Venn diagram of people who don’t overlap:

So when I run the query and get the actual execution plan – even though I’ve created two supporting indexes, which also create supporting statistics:

SQL Server’s actual execution plan ignores both indexes and does a table scan:

To understand why, we read the query plan from right to left, starting with the Clustered Index Scan operator. It says “419 of 40041 (1%)” – which means SQL Server found 419 rows of an expected 40,041 rows, just 1% of the data it expected to find.

SQL Server over-estimated the population of users because it didn’t know that very few recently created users have earned over 100 reputation points.

Why you care about estimation problems
(once you’ve learned about ’em)

The overestimation means SQL Server doesn’t think using the indexes will be efficient here. SQL Server thinks there will be too many key lookups, which would result in a higher number of logical reads than scanning the whole table.

He’s wrong, of course, and we can prove that by copying the query into a new version with an index hint:

The first query does a table scan and 142,203 logical reads.

The second query does an index seek and 1,735 reads – that’s 82x less reads! SQL Server should be choosing this index, but doesn’t, and that’s why you care.

Index hints aren’t the answer, either.

Because here’s the query plan with the index hint:

See the yellow bang on the select? Hover your mouse over it:

Because SQL Server overestimated the number of rows it’d find, it also overestimated the memory required (leading to Page Life Expectancy dropping, for those of you who track that kind of thing.) In real-life-sized queries, this kind of operation usually causes SQL Server to allocate multiple CPU cores for parallel operations, too, leading to CXPACKET waits when those cores aren’t actually used.

What’s the real solution? Sadly, it’s not as simple as building your own fancy filtered statistics or indexes because date & reputation values are usually parameters that can be set by the user, and vary. I cover better solutions in my Mastering Query Tuning class.


How to Patch SQL Server

Microsoft releases SQL Server Cumulative Updates about every 60 days. This is a part of your job that you’re going to be doing a lot, so let’s get good at it! Here’s the strategy I like to use.

Pick what patch you’re going to apply. Generally speaking, you should be on the most recent Cumulative Update available for your version. (Years ago, folks only applied Service Packs, but starting with SQL Server 2017, Service Packs are gone. Microsoft only ships Cumulative Updates now.)

Decide how you’re going to detect problems. Every now and then, an update breaks something. For example, SQL Server 2019 CU7 broke snapshotsSQL Server 2019 CU2 broke Agent, and so many more, but my personal favorite was when SQL Server 2014 SP1 CU6 broke NOLOCK. Sure, sometimes the update installer will just outright fail – but sometimes the installer succeeds, but your SQL Server installation is broken anyway, and it may take hours or days to detect the problem. You need to monitor for new and unusual failures or performance problems.

Design your ideal rollout strategy. Here’s the order I like to use, but I understand that not everyone has all of these environments. More on that in a second. Roll out the patch in this order:

  1. Development servers – you want your developers seeing any failures or behavior changes first so they can feel confident that the eventual production patch will produce behavior they’re used to.
  2. QA/test servers
  3. Disaster recovery servers – often, these are the least-critical servers in the list that are actually being monitored with monitoring software, and leverage SQL Server’s high availability and disaster recovery features like clustering, and also have to keep up with the level of writes happening in production. New problems will show up here, and hopefully monitoring will detect them before you apply patches to subsequent levels.
  4. Read-only replicas – servers where some end user activity happens, but it’s less critical than your primary servers. This advice applies whether we’re talking replication, log shipping, or Always On Availability Groups.
  5. Failover servers – now we’re getting really close. The idea here is to patch these without taking a production outage – but that’s not always possible depending on the HA/DR features you’re using, and the way you’re using them.
  6. Production primary servers – and the way you patch these is to actually not patch them at all. On “patch day”, simply fail over to your failover server, which has already been patched. This way, if you experience any surprise issues with the patch within the first few days, you can fail back over to your unpatched production server. (This also means you need to hold off patching that server for a few days to give yourself a safety net.)

Design your actual rollout strategy. Having read that above rosy-world scenario, now you’re looking at your own environment going, “Brent, I don’t have a bunch of those servers.” Okay, no problem: scratch out the lines you don’t have, but understand that you’re also scratching out possible safety nets. This is something to think about when you’re designing your next SQL Server architecture.

Design your rollback strategy. In the event that you do detect problems – and it’ll happen sooner or later – you want to have a rough idea of what you’re going to do. In dev/QA/test, you might just choose to uninstall the update and wait it out, giving other SQL Server customers time to troubleshoot the problem with Microsoft on their mission-critical servers, then apply the next fixed update instead. If the update made it all the way to your DR or failover tier without you catching the problem, you might not have the luxury of cleanly uninstalling the update, and your rollback strategy may be to open a support case with Microsoft to troubleshoot the problem – hopefully before applying the failed patch to your production primary servers.

What we’ve done so far seems like a lot of designing, but remember, you only have to do this once, and you can reuse it for every update you apply to this environment.

When applying the actual patch, here’s what I like to do, in order:

  1. Verify that you have backups. Ideally, do a test restore, too: backup success messages don’t mean you have working backup files.
  2. Stop or shut down client apps. You don’t want folks starting a transaction as your update begins.
  3. Make sure there’s no activity happening on the server, especially long-running jobs like backups.
  4. Apply the update – if you’re using PowerShell, check out how to automate patching with DBAtools.
  5. Apply Windows updates since you’re down anyway. (Sometimes I find folks have been applying SQL updates, but not Windows updates – they’re both important.)
  6. Confirm the SQL Server service is started, and check your monitoring tools for any unexpected failures.
  7. Confirm the SQL Server Agent service is started again, and kick off your next log backup job.
  8. Start client apps back up and make sure they function.

Over the coming days, keep a much closer eye than normal on monitoring tools looking for unexpected failures. Then, it’s time to hop back on the hamster wheel again, and start planning your next round of updates.


Who’s Hiring in the Database Community? June 2021 Edition

Who's Hiring

Is your company hiring for a database position? Do you wanna work with the kinds of people who read this blog? Let’s set up some rapid networking here. If your company is hiring, leave a comment.

The rules:

  • Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
  • An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
  • Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
  • Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
  • Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
  • Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

I close the comments here after 1 week, and publish another post each month in the Who’s Hiring category so y’all can get the latest opportunities.

If you want to be notified as people leave new job comments here, there’s a “subscribe without commenting” option where you leave a comment.


Updated First Responder Kit and Consultant Toolkit for June 2021

If you want to quickly understand the top health and performance issues on your SQL Server, there’s no better, easier, free-er way to do it than the open source First Responder Kit.

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

How I Use the First Responder Kit
Wanna watch me use it? Take the class.

To get the new version:

Consultant Toolkit Changes

  • Improvement: on SQL Server 2019, we export a sample query plan for each missing index request. This helps you know the kinds of queries that are triggering missing indexes, and helps you hand-craft an even better index.

That change does mean a new querymanifest.json, but no changes to the spreadsheet. You only need to use the new querymanifest.json if you want that new feature – otherwise, just copy your spreadsheet and querymanifest.json into the new release’s folder. (I’m planning a new first tab for the spreadsheet for next month’s release, so I don’t want you to have to do this work twice if you’ve been doing customizations – next month’s release will be the bigger bang for the buck there.)

sp_Blitz Changes

  • Improvement: if you’ve accidentally got “show actual execution plans” turned on while running any of the First Responder Kit scripts, it’s now automatically turned off inside the proc. (#2911, thanks Daniel Hutmacher and Erik Darling.)
  • Fix: now skips Instant File Initialization check on Amazon RDS because we can’t run xp_errorlog. (#2879, thanks Ray Rankins.)
  • Fix: more Amazon RDS compatibility improvements. (#2888, thanks Ray Rankins.)
  • Fix: Agent jobs without failure emails were only showing up in the alerts if they were scheduled. (#2905, thanks Mike Scalise.)

sp_BlitzCache Changes

  • Fix: queries with a duration of more than 2,147,483 seconds (that’s 596 hours) would cause an arithmetic overflow. (#2890, thanks Ron MacNeil.)
  • Fix: the Multiple Plans warning was only including the number of plans in the top 10 result set, and now instead shows the total number of cached plans for this query altogether. (#2900, thanks Erik Darling.)
  • Fix: the Many Duplicate Plans warning math is getting closer to accurate. (#2909, thanks Erik Darling.)

sp_BlitzFirst Changes

  • Fix: in the high CPU details in the How to Stop It column, results could be out of order depending on varchar sorting. (#2876 and #2878, thanks Adrian Buckman.)

sp_BlitzIndex Changes

  • Improvement: table-level columnstore visualization on partitioned tables now shows the partition boundaries. (#2860, thanks Nick Papatonis.)
  • Fix: included columns now show their correct sizes in bytes. (#2880, thanks Scott Holiday.)
  • Fix: SQL Server 2019’s new sample query plan for missing indexes is now more likely to produce a query that generated the missing index request. I think there’s still possibility for edge cases here where this is wrong, and the 2019 user base is so small that we don’t have great testing on this yet. (#2883, thanks Greg Dodd and Erik Darling.)

sp_BlitzLock Changes

sp_BlitzWho Changes

sp_DatabaseRestore Changes

  • Fix: the @StopAt and @OnlyLogsAfter parameters couldn’t be used at the same time. (#2898, thanks Greg Dodds.)

SqlServerVersions.sql Changes

  • Fix: the uninstall.sql script now removes this table from any database, not just the current one. (#2893, thanks Andreas Jordan.)
  • Fix: updated the latest build numbers. (#2897, thanks Anthony Green.)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

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.


Announcing My 2021/2022 Training Class Schedule

Company News
0

If you want to attend my live online classes, it’s really easy: just buy a Live Class Season Pass and you can drop in on any class that works for your schedule.

I recommend attending ’em in this order:

  • Fundamentals, required, in this order: Index Tuning, Query Tuning, Parameter Sniffing
  • Fundamentals, optional: Columnstore, TempDB (can be taken in any order)
  • Mastering, required, in this order: Index Tuning, Query Tuning, Parameter Sniffing, Server Tuning

So I schedule ’em in that order, too. Here are the rotations for the rest of this year and all of next year for your scheduling pleasure:

Summer/Fall 2021 Rotation:

I’m in the midst of an EU-friendly time schedule rotation:

And here’s the next US-friendly time schedule rotation:

Fall Fundamentals:

All of the Fundamentals classes share these prerequisites except where noted.

Winter 2022 Mastering Rotation:

The Mastering classes require this VM setup.

Spring/Summer 2022 Rotation:

All of the Fundamentals classes share these prerequisites except where noted, and the Mastering classes require this VM setup.

Summer/Fall 2022 Rotation:

All of the Fundamentals classes share these prerequisites except where noted, and the Mastering classes require this VM setup.

Can’t attend live? Check out the recordings.

Recorded Class Season PassYou can buy a Recorded Class Season Pass of the Fundamentals classes, or of the Mastering classes. That gives you the ability to stream the most recent recordings of each class anytime you want on your desktop, laptop, tablet, phone, or Internet-connected fridge.

Whew! That’s about enough scheduling for a while. I do have plans to bring out two more training classes in 2022, but I’m not quite ready to announce those yet. And as always, your Season Passes include access to any new classes that I bring out during your subscription. (I don’t plan to do any in-person classes during 2021 or 2022.)

See you in class!


Do You Have to License Your Standby SQL Server?

Licensing
23 Comments

Businesses who need high availability and disaster recovery usually want three servers:

  1. A primary SQL Server where all the writes & reads take place
  2. A high availability SQL Server sitting right next to the primary, in the same data center, keeping up with its writes, and
  3. A disaster recovery SQL Server sitting in another city or region, keeping up with the writes, but not necessarily in real time – the writes here are usually asynchronous

There are several different techniques we can use to keep those in sync, like failover clustering, Always On Availability Groups, SAN replication, log shipping, and database mirroring.

Regardless of which technology you use, there’s an important question: do you have to license the standby SQL Servers, aka #2 and #3? Since November 2019, you don’t have to pay licensing for those SQL Servers in most circumstances. Microsoft changed the licensing terms when SQL Server 2019 came out.

As long as your licensing is covered under Software Assurance, you get several new benefits including:

  • One high availability server is free
  • One disaster recovery server is free
  • You can offload corruption checks, full backups, and log backups to these servers too
  • But you still can’t query them. As soon as you start offloading end user queries onto them, they need to be fully licensed.

Isn’t that awesome? And the best thing is that you don’t even have to be running SQL Server 2019 in order to get those benefits! As long as you’re current on Software Assurance, you get this on any supported version.

This is the kind of thing that really makes an impact on your high availability and disaster recovery architectures, and I’ll be talking about that on Thursday, June 17th.


Free Webcast: Architecture Design for SQL Server High Availability & Disaster Recovery

Architecture
10 Comments

Brent Ozar

You’re building a new SQL Server, and you need to protect it. You want to learn when it’s right to use clustering, AlwaysOn Availability Groups, virtualization, or storage replication. You don’t have an unlimited budget, either, so you need to learn how SQL Server licensing impacts these choices.

Or maybe you’re not doing it yet – but you just want to keep your skills sharp.

On Thursday, June 17th, I’m doing a free one-hour webcast with Pure Storage to bring you up to speed. If you can’t make it live, no worries – Pure will be recording the webcast, and they’ll share the recording link with the registered folks.

We’ll cover situations when you might choose failover clustered instances, Always On Availability Groups, log shipping, virtualization, or just a plain ol’ standalone instance.

Register here for free. See you in class!


You Captioned This Pic and Won Free Stuff.

Humor
5 Comments

Last week, over 600 of y’all captioned this picture, and we had a lot of laughs:

Brent Ozar Feels

My favorite caption overall was from Aart Bluestoke, who won a Live Class Season Pass for this:

Is that a date I see in that varchar column?

The funniest part to me was that I’ve had that exact experience several times with several different data type combinations, and I think I’ve made that same face every time. The worst one involved storing the same GUIDs in several tables in different data types each time – as in, CustomerID was a GUID in the Customers table, VARCHAR in the Orders table, and NVARCHAR in the Payments table, and we had implicit conversions all over the place every time we joined on anything. <sigh> We couldn’t just fix the tables, either, because stored procedure parameters also seemed to be chosen at random.

Ten Runners-Up

The below runners-up got both a Recorded Class Season Pass: Mastering Classes and Recorded Class Season Pass: Fundamentals Classes:

“HELLO BRENT! PLEASE UPLOAD YOUR MASTERCLASS VIDEOS ON YOUTUBE SO I CAN DOWNLOAD THEM FOR FREE!!” – Dready

Can you help me move? – Jodi F

Day 37. The northern lights just won’t quit. Trying squinting. – Bill Sanscrainte

Oh you want help with your specific problem in the middle of this webinar that hundreds of people are attending? – Andrew Boggs

LinkedIn message: “Dear Brent – In these uncertain times, the only certainty is that the role of the DBA is becoming obsolete. Protect your future today, and enroll in NoSQL for beginners.” – James McGillivray

Microsoft’s newest CU release notes: “bug fixes and performance improvements” – Steve H

The look when you see a busload of tourists have arrived at the Blue Lagoon just before you. – Greg Burnett (Brent says: especially funny because I’m going there next week, hahaha.)

You’re available for emergencies while you’re in Cabo, right? – Kelley Pribil

You think I have a lane…? – Justin Bird

Live footage from Reykjavik as the legal team representing Mr. C. Lippy formally issue defamation of character papers against Mr. B. Ozar as the ‘SQL Server Tuning Wars’ reaches a new low. – David

Nine Honorable Mentions

The below honorable mentions got a Recorded Class Season Pass: Fundamentals Classes:

Can I borrow the keys to the Porshe? I’ll be right back I promise! – Kevin McDonnell

I love the smell of unused indexes in the morning. – Joe Webb

Psyllium husk fiber capsules kick in. – Jason F (Brent says: bonus points for inside joke – no, not that kind of inside – although yes, also that kind of inside)

It’s as fast as it can be, I created all the recommended indexes. – Shaun Simon

They only use lowercase to keep their databases small. – Chris

Grumpy Cat Moves to Iceland – Phillip Griffith

“Don’t worry I used NOLOCK” – Stu H.

It’s called “Hákarl” – David Lafayette

Brent, *sigh*, you broke the build…again. – Richie Rump


9 Signs Your T-SQL Might Fail a Code Review

T-SQL
25 Comments

It’s hard to set absolute rules about, “Feature X should absolutely never be used.”

However, there are some features that set off alarm bells when I see them. Usually, when I start asking more questions about when we’re using those particular features, I get answers of, “Oh, I didn’t know that was a problem.” As we have a bigger discussion, it leads to the piece of code failing the code review, and going back to the drawing board for improvements.

  1. Joining to table-valued user-defined functions: if it’s a multi-statement function, its underlying work doesn’t show up in execution plans, and it’s single-threaded, running one row at a time. I’ll pop open the plan to just double-check if it’s multi-statement or inline, and heaven forbid it calls another function.
  2. Joining to table variables: even with 2019’s improved row estimations, table variables still don’t get statistics on their contents, and inserting data into them is still single-threaded. There are a few edge case uses that we discuss in Fundamentals of TempDB, especially around reducing recompilations, but generally when I see table variables in code, it’s because someone didn’t know about their weaknesses. (The weaknesses of table variables, I mean, not their own weaknesses. Although now that you say it that way…)
  3. CROSS JOIN: sure, there are legitimate reasons why you might want every row from a table with no filtering whatsoever, but it’s fairly unusual. These two words set off an alarm bell that make me look more closely.
  4. Multiple joins to the same CTE: CTEs can be fine, although a temp table is often a better fit. The thing that raises flags during a code review is seeing the same CTE referred to multiple times, like FROM cte JOIN cte JOIN cte, each with a different alias. That CTE will show up multiple times in the execution plan, hitting the underlying tables repeatedly.
  5. The kitchen sink design pattern: WHERE (CustomerId = @CustomerId OR @CustomerId IS NULL) is tough for SQL Server to optimize, and we typically change that over to dynamic SQL or judicious use of RECOMPILE hints.
  6. SELECT (things) INTO #TempTable: I love temp tables, and I think they get a bad rap, and even SELECT INTO can have advantages over explicitly creating a table. However, when I see SELECT INTO, it’s usually because someone was working quickly, and they didn’t want to take the time to write CREATE TABLE and figure out all the datatypes. For example, during a recent review, the query author was copying an entire table into TempDB, and then doing filtering rows there – thinking that they were minimizing the amount of work they were doing in the user database.
  7. Creating indexes on a temp table: this sounds like a good thing – after all, who doesn’t like indexes? – but most of the time when I see it and I start asking questions, I get answers like, “I just guessed that the index might help.” That triggers us to spend a little more time experimenting, and also leads to teachable moments about how that affects temp table caching.
  8. WITH (NOLOCK): I keep demoing that it gets random results, and I know you might find this hard to believe, but not everyone reads my blog. (I know, right?) There are indeed places where random results are just fine, but most of the time when I see this red flag and I ask questions, the query author just didn’t know how dangerous this hint was. Bonus points for trying to use it in an UPDATE statement.
  9. BEGIN TRAN & COMMIT with no error handling – when I see this, I ask, “So you’re doing an explicit transaction because it’s important to you that all of this stuff gets committed, or not at all, right? And you’re expecting to have the transaction fail every now and then and you want to roll back, right? So, uh, what happens to your app when that happens?” If you care enough to specify a transaction in the database, then you need to keep going and specify how to handle the errors, too. Erland Sommarskog’s Error and Transaction Handling post is a great place to start.

I have absolutely given the thumbs-up to code that had more than one of these anti-patterns – heck, this stuff is in the First Responder Kit – but they’re all signs that I’m gonna look a little more closely at your code and ask questions, and you should probably ask those questions, too.


Contest: My Favorite Caption Wins a Live Class Season Pass

Company News, Humor

Update 2021-05-24: the contest is over, and the winners are here.

Let’s have a few laughs this week. In the comments, write a caption for this:

Brent Ozar is Disappointed

Prizes:

Rules:

  • To submit your caption, leave a comment with your caption here (BrentOzar.com) before Sunday, May 23, 2021.
  • You can enter multiple times – either with multiple comments, or multiple captions in the same comment.
  • The winners will be chosen by me, Brent Ozar, judged using completely unreliable and unpredictable methods. Winners will be announced within one week of the contest closing.
  • If multiple people submit the same caption, the winner will be the first one.
  • Taxes are the responsibility of the winners.

When you comment, I would highly recommend not subscribing to the comments unless you’re prepared for an avalanche of caption emails, hahaha. (Me, I get the notifications – but I don’t mind having a few laughs along the way, heh.)

Update: you can also use this meme generator.


I’m on the KasperOnBI Podcast Talking About Careers and the Future of SQL Server

I sat down with Microsoft’s Kasper de Jonge this month for an hour-long conversation about how I got started speaking, careers, and the future of SQL Server:

You can also listen on Spotify and Apple Podcasts.

In the podcast, we talk about my Epic Life Quest – that’s over here.

If you’ve got followup questions, feel free to leave ’em in the comments. Enjoy!


Free Video: Deadlocks: Let’s Do One, Understand It, and Fix It

Deadlocks, Videos
3 Comments

You keep getting warnings and emails about deadlocks, but let’s be honest: you’re not really sure how they happen or what to do about it. In this one-hour session from the SQLBits conference, I will show one, use sp_BlitzLock to analyze it, then fix it.

If you like this video, check out the SQLBits YouTube channel. They’re releasing all of last year’s conference videos online for free in YouTube! When you subscribe to their channel, you’ll automatically get notifications as new videos get uploaded.


What’s the Buggiest Feature in SQL Server?

Not the one you like the least, or the one that requires the most work.

What’s the one that you can’t use because it’s just flat out busted?

For me, it’s SSMS’s Live Query Statistics, aka live query plans. The idea is so awesome – you click Query, Include Live Query Statistics, and then when you run queries, you get this awesome animated execution plan:

Live query plan animation
Click to see full resolution

This is really useful when I’m troubleshooting long-running queries or procs, especially cases when I can’t wait long enough to get the full actual query plan, but I need to know where we’re hung up, which parts are taking the longest.

Except…

Except the live plan only shows the first statement in a batch. In the movie above, notice how I’ve got two queries in my batch. I hit execute, but then only the live query plan for the first query – the Users scan – shows up. Then the live query plan stops updating until the entire batch finishes. If you want to see the live plan for subsequent statements, you have to go into Activity Monitor – which you shouldn’t be using – and then go into active expensive queries. You can briefly see the live plan for whatever one statement happens to be running, but as the next statement starts, you have to jump through more hoops to see the next statement.

Except the percentages are misleading. The live plans show percentages on them, which makes it seem like it’s the percent complete for an operation, but it’s not. It’s the percentage of rows SQL Server found in various stages of the plan, compared to its original estimates for that operator. People see 1%, and they think that means it’s 1% done – but that part of the operation might actually be fully complete, but it just brought back less rows than SQL Server expected. Or they’ll see 100%,  but the operator is still working – that’s just because SQL Server found some data, but it’s still churning away on that operator. I could go on and on.

Except the final results are wildly misleading. In the movie above, notice how the final plan shows three statements, not one: it shows the Users operation twice for some reason.

Except often, it doesn’t even work at all. Even if it’s the perfect use case – a single-statement query that takes forever – when I demo the feature, I have to explain, “Now what I’m about to do may not even work at all, so forgive me if I curse and then stop the query and then start it again, or jump over to Activity Monitor.” It doesn’t feel like I can predict success of it at all.

I really want to love Live Query Statistics, except…

What about you? What feature drives you crazy due to the bugs?


If You Could Only Say One Thing About Writing Good Queries…

SQL Server
33 Comments

If you could give just one piece of advice about writing good queries, what would it be?

I asked Twitter yesterday because I wanted to make sure I didn’t miss anything in a new training course I’m working on, and the replies were fantastic. Here were some of my favorites:

What about y’all? Leave your most important tip in the comments, and read the Twitter thread for tons of gold nuggets.


Execution Plans Don’t Have the Yellow Bang They Really Need.

Execution Plans
12 Comments

When SQL Server is purely making an estimate up out of thin air, with no freakin’ idea what the real answer is, it doesn’t give you any kind of warning. It just produces an estimate that looks scientifically accurate – until you know how it’s actually getting calculated.

Let’s ask SQL Server how many users have cast more than ten million votes. You can use any Stack Overflow database for this:

To give SQL Server the best possible chance to calculate it, I’ll even create a couple of indexes, which will also create perfectly accurate fully scanned statistics on the relevant columns:

When I run the query, it doesn’t actually produce any results:

But if I look at the execution plan, the estimates are a little off:

SQL Server estimated that precisely 2,675,250 rows would come back – but none did.

So where does that 2,675,250 number come from? It would sound like it was the result of some really scientific calculations, but in reality, it’s just a hard-coded 30% of the number of rows in the table.

There’s absolutely no logic behind that estimate.

30% is a purely made-up number, and it’s hard-coded: you get the same answer if you use greater than OR less than, and with ANY NUMBER YOU COULD PICK. In my case, I picked ten million rows, but you can put any number in that query, and you still get a 30% estimate.

When SQL Server makes an estimate up out of thin air based on hard-coded rules like 30%, it needs to warn us with a yellow bang so that we can jump to fixing that specific problem.

The bigger your query gets,
the more important this becomes.

I started with a really simple example with a small blast radius: that estimation error doesn’t really harm anything. However, in real-world queries, you start to layer in joins and ordering, like finding all of the users and their badges:

You would hope that SQL Server would go find the users first, and then short-circuit out of the rest of the query if it didn’t find any matching users. You would have also hoped that we didn’t have a hard-coded estimate of 30% here too, though, and I’ve got bad news about your hopes. I’m here to turn your hopes to nopes, because here’s the live query plan:

SQL Server scans both tables at the same time because it assumes so many users will match – might as well start work on the Badges table, right? No sense in waiting around to short-circuit – we’re going to be working with millions of Users rows! Better get this party started.

After you learn about this, your next steps should probably be:


Announcing Fundamentals Week: May 10-14, $295

Company News
0

You love learning from me. You’ve attended some of my free online streams, read my blog posts, and you use the First Responder Kit. You enjoy my laid-back, humorous approach to sharing what I’ve learned over the years.

You love live classes. You’ve tried watching recordings or stepping through demos yourself, but…you just can’t block out the time, and you can’t stay motivated while you’re watching a recording. You like the fast-paced energy of seeing me live, asking me questions, and seeing me respond to your chats.

You hate conferences that feel like Zoom meetings. You’ve tried attending a few community events, but they felt just like sitting through one meeting after another. They didn’t dive deeply into any one subject – they just hopped around from one topic to another, and many of those topics just weren’t relevant to you. They were good for free stuff, but…

You’re ready for Fundamentals Week. On May 10-14, you’ll spend the week with me learning about indexes, query tuning, columnstore, how I use the First Responder Kit, and more. Here’s the lineup, all 9AM-4PM Eastern US time:

Your Fundamentals Week registration gets you into those live classes. Live Class Season Pass holders are always welcome, too. To keep prices low, recordings are not included – this event is really just for folks who like to learn live. If you want the recordings, check out my Recorded Class Season Pass.

Registration is open now. See you there!


Updated First Responder Kit and Consultant Toolkit for April 2021

This month’s release has two gems for SQL Server 2019 users. First, thanks to Greg Dodd, when you’re dealing with parameter sniffing issues, sp_BlitzWho can now show you the cached and live parameters for running queries. This lets you quickly see when the parameters may be wildly different, leading to issues like incorrect memory grants and index choices:

Second, thanks to Erik Darling: when sp_BlitzIndex shows a missing index recommendation, it also shows you one of the queries that triggered the missing index request:

Both of these require SQL Server 2019, and they’re both just awesome for performance tuners. There’s lots more new stuff this month too – keep scrollin’ for more details. Unlike Microsoft’s SQL Server Cumulative Updates, we actually take the time to link to each Github issue so you can see exactly what changed. We figure it’s the least we can do for you, dear reader.

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

Watch and learn

To get the new version:

Consultant Toolkit Changes

I updated it to this month’s First Responder Kit, but no changes to querymanifest.json or the spreadsheet. If you’ve customized those, no changes are necessary this month: just copy your spreadsheet and querymanifest.json into the new release’s folder.

sp_Blitz Changes

  • Fix: no more arithmetic overflow when we check drive space on a drive with 8MB free. (#2837, thanks ScottL1969.)
  • Fix: removed language that suggested SQL Server needed a domain account. I don’t want sp_Blitz to be used as a security audit tool, and our warning wording was misleading there. (#2854)
  • Fix: the USERSTORE_TOKENPERM cache size check had the wrong math. (#2858, thanks Erik Darling.)
  • Fix: volume names were getting truncated on the drive space alerts. (#2865, thanks Erik Darling.)

sp_BlitzCache Changes

sp_BlitzFirst Changes

  • Improvement: when @OutputXMLasNVARCHAR = 1, output is trimmed to 32K for easier copy/pasting into Excel. (#2823, thanks Ties Voskamp.)

sp_BlitzIndex Changes

  • Improvement: SQL Server 2019 users can see an example of the query that triggered a missing index request. (#2185, thanks Erik Darling.)
  • Improvement: new @ShowColumnstoreOnly parameter lets you visualize the columnstore contents for a table. This was always shown with table-level details (if you pass in a table’s name), but it was the last result set, and it can be pretty long for big tables. This way your results are easier to see on big monitors. I’ll be using this a lot in my Fundamentals of Columnstore class. (#2851, thanks DjHeath2000.)
  • Improvement: new @SortOrder options for ‘create date’ and ‘modify date’ for @Mode = 2, the inventory of your indexes. (#2845, thanks Erik Darling.)

sp_BlitzLock Changes

  • Fix: removed questionable documentation text. (#2849, thanks AdrianB1.)

sp_BlitzWho Changes

  • Improvement: new @ShowActualParameters (default 0) will show you the parameters that were used to compile and to run the execution plan. Only works on SQL Server 2019 & newer. (#2591, thanks Greg Dodd for heroically working around memory dump bugs in SQL Server to get this across the finish line.) These new columns also get logged to sp_BlitzWho’s output tables, and we’ll automatically add the columns if they don’t exist.
  • Fix: the above improvement broke on case-sensitive collations. (#2870, thanks Adrian Buckman.)

sp_DatabaseRestore Changes

  • Fix: no more arithmetic overflow when you try to restore a database with over 32,768 log backups. (#2839)

SqlServerVersions.sql Changes

  • Fix: corrected the build number for SQL Server 2017 CU23. (#2856, thanks Anthony Green.)

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.


Who’s Hiring in the Database Community? April 2021 Edition

Who's Hiring

I’ve gotten a lot of emails from friends lately who are looking for work. It seems that as some companies go back to the office, some people are realizing they want to work remotely forever. And on the flip side, other folks are realizing they desperately want to get back into an office, but their companies have gone fully remote.

So let’s set up some rapid networking here. If your company is hiring, leave a comment.

The rules:

  • Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
  • An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
  • Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
  • Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
  • Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
  • Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

I close the comments here after a week, and publish another post each month in the Who’s Hiring category so y’all can get the latest opportunities.

If you want to be notified as people leave new job comments here, there’s a “subscribe without commenting” option where you leave a comment.


Free TempDB Training Class Next Monday

TempDB
1 Comment

You’ve been working with Microsoft SQL Server for a couple of years, and you know a little bit about TempDB. You’ve heard that temp tables and table variables have different performance characteristics than regular user database tables, and you’ve heard that you’re supposed to have multiple data files.

You’re wondering what exactly goes on behind the scenes.

This class is for curious folks who want to learn:

  • What uses TempDB, and how that affects performance: not just temp tables and table variables, but also triggers, cursors, sorting indexes, workspace spills, the version store, and more
  • How to host TempDB depending on how you use it: understanding what goes in the log file, the data files, why the number of data files matter, and whether your particular workload should be on SAN, local storage, or ephemeral storage, and why
  • How to monitor it: after you understand what uses it and how to host it, you need to keep tabs on how its performance changes over time using DMVs, Perfmon counters, and the First Responder Kit scripts

This course is 90% demos: the only slides are the introductions at the start of the day, illustrations to support a few topics, and the recap at the end of the day. The rest of the time, we’ll be working in SQL Server Management Studio. Roll up your sleeves and join me!

No registration required – just add this event to your calendar, then show up at BrentOzar.com/training/live/ on next Monday at 8AM UTC. This is a Europe-friendly time zone class – folks in other parts of the world will either need to get up at an oddball time, or else buy the recordings.

If you’d like to follow along with the demos during class – totally optional, not required – you’ll need a SQL Server 2016 or newer instance. Your server’s power doesn’t really matter – you can run this class’s labs on your local desktop just fine as long as you’ve got enough space for the database. I will show you the differences in SQL Server 2019, but if you’re not using 2019 yet, no worries – you can just watch those portions of the demos. You’ll also need any Stack Overflow database. In the class, I’ll be using the 50GB StackOverflow2013 version, but any version/size will work.

See you in class!


Azure SQL DB Frequently Asked Questions

Azure SQL DB
20 Comments
Brent Reading Book
I’ve seen your search history.

Let’s get you some answers.

Is Azure SQL DB PaaS, IaaS, or SaaS?

Azure SQL DB is platform-as-a-service (PaaS). Microsoft manages most common database tasks for you, and they act as your DBA.

This also means that like a DBA, Microsoft will tell you “no” if you try to implement something that they don’t support. There’s no negotiations: the answer is just no.

Who is Azure SQL DB for?

Azure SQL DB is a good fit for new applications.

It’s a great fit for software-as-a-service companies and independent software vendors (ISVs) who want to put each client in their own database. Azure SQL DB gives you performance and cost controls because you can tune how much resources each database gets, or put them into pools of resources that are shared across lots of databases.

SaaS and ISV companies don’t usually have full time database administrators, so they like that Microsoft handles the basic care and feeding of Azure SQL DB. There’s no dealing with patching, clustering, backups, and recovery – Microsoft manages all that for you.

Who is Azure SQL DB NOT for?

It’s not a good fit for companies with existing applications that do cross-database queries. If your app is scattered across multiple databases, and you’re used to doing joins across them, you’re going to be frustrated with Azure SQL DB. It’s technically possible, but it’s a giant pain in the rear, especially for established databases with hundreds or thousands of tables in each database.

It’s also not a good fit for companies with a lot of SQL Servers that already talk to each other doing things like linked servers, replication, and Agent jobs that fire off tasks on different servers at different times.

If you fall into the above categories, that’s where Azure SQL DB Managed Instances come in.

Compare Azure SQL DB versus Managed Instances.

Azure SQL DB is for one standalone database, versus Managed Instances which are more like one standalone SQL Server with multiple databases on it.

If you’re used to managing SQL Server, you’ll probably prefer Managed Instances: they have things like cross-database queries and Agent jobs. Databases fail over together, too. It’s basically like IaaS Availability Groups in the cloud, managed by Microsoft for you. Make no mistake, though: they’re still PaaS, and they’re priced accordingly.

Compare Azure SQL DB versus Synapse Analytics.

Azure SQL DB is mostly for transactional systems like web sites. Synapse Analytics is for data warehouses.

In a transactional system, queries deal with a few rows at a time, and they complain when queries take more than a second or two to complete.

In a data warehouse, people queries deal with millions of rows at a time, and their complaints tend to start when reports take more than 10-30 seconds to finish. They’re used to taking a longer time because of the size of data they’re dealing with.

Could you run a data warehouse in Azure SQL DB? Sure, but…it’s going to be expensive due to Azure SQL DB’s throughput limitations. It’s much easier (and more cost effective) to load large amounts of data into Synapse Analytics. Just don’t expect Synapse Analytics to handle a thousand queries at a time each with millisecond-level response times: it’s not designed for that.

Is there a free tier for Azure SQL DB?

No. The cheapest Azure SQL DB is the Basic tier at $5 per month, but it only gets 5 database throughput units (DTUs). This is effectively useless as even the simplest queries will take seconds to complete.

This is frustrating for shops that are used to using the free SQL Server Developer Edition for their development work, and hosting that development server centrally for multiple developers to work on at the same time. That’s okay: the better approach is to have every developer use their own local development database, like in a Docker container, and –

What’s that you say? Your development laptops aren’t large enough to host your full database size? Well, no, of course not, but you’re not supposed to be developing with production data. You’re supposed to be using an empty database for your proof-of-concept work, and then testing with a manufactured set of similar-to-production data.

What’s that? I’m afraid you’re going to have to speak up – your monitor has a really crappy microphone. Oh, I hear you – you’re asking about your QA, load test, and user acceptance test environments, which use databases restored from production. Well, uh, I don’t have good answers there: there’s no free Developer Edition for Azure SQL DB, and you can’t restore your Azure SQL DB databases down to a regular SQL Server. There are some kludgy hacks around exporting all your data, but those are performance-intensive and slow. Let’s just ignore this business problem and move on for now.

What about Azure SQL DB Edge?

The Azure SQL Edge page says it simplifies application development, but that’s not really what it’s for. It’s an innovative new way for Microsoft to extract more licensing fees by getting you to host databases in more places.

Wait, I don’t think I said that quite right. I mean when you have important data that lives in remote places, like in sensors or frequently-disconnected devices, Azure SQL Edge lets you have all of the power and complexity of a database server there, without the ease of management or cost-effectiveness of a centralized…

Hold on, I don’t think that was quite it either. Lemme get back to you on that one.

Why is my Azure SQL DB slow?

Most of the time when I work with clients, it’s because they criminally under-sized the server.

Azure SQL DB can be provisioned in very, very small hardware sizes for hobbyists or development purposes, and it’s very inexpensive. However, I find that folks provision it in those inexpensive tiers, and then they’re totally disappointed by the performance. You’re not hitting Azure SQL DB architectural limitations – you just provisioned it too small.

To find out if you’re hitting your account’s limitations, install and run sp_BlitzFirst from my free First Responder Kit. It’ll tell you if you’re hitting your Database Throughput Unit limits:

Azure SQL DB is maxing out

If you’re hitting your limitations on CPU, throughput, memory usage, etc, then you have two simple choices:

  • Turn up your size to a higher number, or
  • Tune your queries & indexes

If you’re spending less than $500 per month on Azure SQL DB, it probably just makes sense to turn the size up. If you’re spending more than that, it’s probably time to attend a performance training class or hire me for performance tuning help.

How can I learn about Azure SQL DB for free?

If you prefer to learn via live online events, Microsoft’s running a free Azure SQL Digital Event on May 4.

If you like self-learning – reading through material and running demos yourself – check out Microsoft’s SQLWorkshops Github repo. Scroll down through the readme, and there’s a list of courses. Each course is very well thought-out with tons of resources and demos to run.

If you prefer books, get Azure SQL Revealed by Microsoft’s Bob Ward. It’s fairly new, so it’s still relevant – which can be tough to find in the constantly changing churn of the cloud.


Menu