Blog

Free Video: 300-Level Guide to Career Internals: Planning Your Career

Last Updated June 17, 2021

Who is that masked man? — Erika applies a mask to me at the Blue Lagoon

Hi. I’m writing this from a cottage in Iceland, in the midst of a 9-month digital nomad stint.

I’m teaching classes and doing client work for 1-2 weeks, then Erika and I take off and hit the road for 2-3 weeks at a time, driving around the country, seeing the sights, and sharing ’em on my Instagram feed.

I’m a database administrator.

I cannot believe this is my life.

I’ve always been really big on sharing everything that I know, and I wanna share everything that I know to help you crank up your career, too.

As the pandemic gradually comes to an end and companies change they way they work (again), so many folks are rethinking their careers – trying to work remotely forever, or getting back to the office that they miss, or changing their work/life balance.

You’re overwhelmed with choices: so many things you could learn, so many ways you could specialize in your career. Which one should you choose? What are the safe bets, and what are the risky bets? Should you be a contractor? A consultant? A freelancer? Specialize in the cloud, and if so, what products? There’s no guide for data professionals here, and it’s kinda scary.

About ten years ago, I sat down with a pen and paper to analyze the ways that I could make a living with data. I devoured a lot of business books, thought about the kinds of things companies pay money for, and laid out a simple grid to help decide what kind of career I wanted to build for myself.

Today, you’re facing the same problems. In one session, I’ll explain what companies pay for, what individuals pay for, and how you could build different careers with that knowledge. I won’t tell you what’s right for you – I just want to give you a map of different choices, like a guidance counselor would. I’ll finish the session by explaining the choice I made, and why.

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.

Feel free to ask followup questions here in the comments. I don’t get notified when folks ask questions in the YouTube comments on the SQLBits channel, so I don’t respond quickly there, but I respond here.

What does SET NOCOUNT ON do?

Last Updated June 4, 2021

Brent Ozar

T-SQL

When you’re working with T-SQL, you’ll often see SET NOCOUNT ON at the beginning of stored procedures and triggers.

What SET NCOUNT ON does is prevent the “1 row affected” messages from being returned for every operation.

I’ll demo it by writing a stored procedure in the Stack Overflow database to cast a vote:

CREATE OR ALTER PROC dbo.usp_InsertVote
	@PostId INT, @UserId INT, @VoteTypeId INT AS
BEGIN

INSERT INTO dbo.Votes(PostId, UserId, VoteTypeId, CreationDate)
	VALUES (@PostId, @UserId, @VoteTypeId, GETDATE());

UPDATE dbo.Users
	SET LastAccessDate = GETDATE()
	WHERE Id = @UserId;

END
GO

EXEC usp_InsertVote 1, 1, 1;

CREATE OR ALTER PROC dbo.usp_InsertVote

@PostId INT, @UserId INT, @VoteTypeId INT AS

BEGIN

INSERT INTO dbo.Votes(PostId, UserId, VoteTypeId, CreationDate)

VALUES (@PostId, @UserId, @VoteTypeId, GETDATE());

UPDATE dbo.Users

SET LastAccessDate = GETDATE()

WHERE Id = @UserId;

END

EXEC usp_InsertVote 1, 1, 1;

When this stored procedure runs, it does two things: insert rows into Votes, and update rows in Users. When it finishes, it returns two messages in SSMS:

However, if I add SET NOCOUNT ON at the top of the stored procedure:

CREATE OR ALTER PROC dbo.usp_InsertVote
	@PostId INT, @UserId INT, @VoteTypeId INT AS
BEGIN
SET NOCOUNT ON /* <---- This is new */

INSERT INTO dbo.Votes(PostId, UserId, VoteTypeId, CreationDate)
	VALUES (@PostId, @UserId, @VoteTypeId, GETDATE());

UPDATE dbo.Users
	SET LastAccessDate = GETDATE()
	WHERE Id = @UserId;

END
GO

CREATE OR ALTER PROC dbo.usp_InsertVote

@PostId INT, @UserId INT, @VoteTypeId INT AS

BEGIN

SET NOCOUNT ON /* <---- This is new */

INSERT INTO dbo.Votes(PostId, UserId, VoteTypeId, CreationDate)

VALUES (@PostId, @UserId, @VoteTypeId, GETDATE());

UPDATE dbo.Users

SET LastAccessDate = GETDATE()

WHERE Id = @UserId;

END

Then those messages go away, and SQL Server just reports that the query completed successfully:

This means less data gets sent from SQL Server, across the network, over to the application.

How much less data? Well, honestly, not a lot. In most cases, this isn’t a heroic performance change because it doesn’t make a meaningful performance difference. However, if you have code that works in a loop, or modifies a lot of different tables, then it starts to matter more.

You might say to yourself, “Well, then, I only need to add it when I’m working in a loop, or when I’m modifying a lot of different tables.” However, you can’t always predict who’s going to call your code, and they might run YOUR code in a loop – and in that case, reducing the message overhead helps.

That’s why it’s a good practice to start every stored procedure with SET NOCOUNT ON. Get in the practice, and it’s one less thing you’ll have to worry about as your workload scales.

Download the Current Stack Overflow Database for Free (2021-06)

Last Updated June 17, 2021

Brent Ozar

Stack Overflow

Stack Overflow, the place where most of your production code comes from, publicly exports their data every couple/few months. @TarynPivots (their DBA) tweets about it, and then I pull some levers and import the XML data dump into SQL Server format.

Stack Overflow’s database makes for great blog post examples because it’s real-world data: real data distributions, lots of different data types, easy to understand tables, simple joins. Some of the tables include:

Badges: 40,338,942 rows; 592.8MB
Comments: 80,673,644 rows; 14.4GB
PostHistory: 141,277,451 rows; 242.0GB; 221.9GB LOB
Posts: 53,086,328 rows; 137.6GB; 26.1GB LOB
Users: 14,839,627 rows; 1.4GB; 4.5MB LOB
Votes: 213,555,899 rows; 2.8GB, making for fun calculations and grouping demos

This isn’t the exact same data structure as Stack Overflow’s current database – they’ve changed their own database over the years, but they still provide the data dump in the same style as the original site’s database, so your demo queries still work over time. If you’d like to find demo queries or find inspiration on queries to write, check out Data.StackExchange.com, a public query repository.

New this month: I built it with page-level database compression, which requires SQL Server 2016 Service Pack 1 or newer (but doesn’t require Enterprise Edition.) I don’t have a before-and-after across all of the tables, but the Badges table was 2GB before, and 0.5GB afterwards. Woohoo! Every little bit helps, especially with the database size.

I distribute the database over BitTorrent because it’s so large. To get it, open the torrent file or magnet URL in your preferred BitTorrent client, and the 54GB download will start. After that finishes, you can extract it with 7Zip to get the SQL Server 2016 database. It’s 4 data files and 1 log file, adding up to a ~401GB database.

Want a smaller version to play around with?

Small: 10GB database as of 2010: 1GB direct download, or torrent or magnet. Expands to a ~10GB database called StackOverflow2010 with data from the years 2008 to 2010. If all you need is a quick, easy, friendly database for demos, and to follow along with code samples here on the blog, this is all you probably need.
Medium: 50GB database as of 2013: 10GB direct download, or torrent or magnet. Expands to a ~50GB database called StackOverflow2013 with data from 2008 to 2013 data. I use this in my Fundamentals classes because it’s big enough that slow queries will actually be kinda slow.
For my training classes: specialized copy as of 2018/06: 47GB torrent (magnet.) Expands to a ~180GB SQL Server 2016 database with queries and indexes specific to my training classes. Because it’s so large, I only distribute it with BitTorrent, not direct download links.

As with the original data dump, these are provided under cc-by-sa 4.0 license. That means you are free to share it and adapt it for any purpose, even commercially, but you must attribute it to the original authors (not me):

Happy demoing!

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

Last Updated June 6, 2021

Brent Ozar

Execution Plans

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:

SELECT *
FROM dbo.Users
WHERE CreationDate > '2018-05-01'
  AND Reputation > 100
  ORDER BY DisplayName;

SELECT *

FROM dbo.Users

WHERE CreationDate > '2018-05-01'

AND Reputation > 100

ORDER BY DisplayName;

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:

DropIndexes;
GO
CREATE INDEX CreationDate_Reputation 
	ON dbo.Users(CreationDate, Reputation);
CREATE INDEX Reputation_CreationDate 
	ON dbo.Users(Reputation, CreationDate);
GO
SELECT *
FROM dbo.Users
WHERE CreationDate > '2018-05-01'
  AND Reputation > 100
  ORDER BY DisplayName;

DropIndexes;

CREATE INDEX CreationDate_Reputation

ON dbo.Users(CreationDate, Reputation);

CREATE INDEX Reputation_CreationDate

ON dbo.Users(Reputation, CreationDate);

SELECT *

FROM dbo.Users

WHERE CreationDate > '2018-05-01'

AND Reputation > 100

ORDER BY DisplayName;

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:

SET STATISTICS IO ON;

SELECT *
FROM dbo.Users
WHERE CreationDate > '2018-05-01'
  AND Reputation > 100
  ORDER BY DisplayName;

SELECT *
FROM dbo.Users WITH (INDEX = CreationDate_Reputation)
WHERE CreationDate > '2018-05-01'
  AND Reputation > 100
  ORDER BY DisplayName;

SET STATISTICS IO ON;

SELECT *

FROM dbo.Users

WHERE CreationDate > '2018-05-01'

AND Reputation > 100

ORDER BY DisplayName;

SELECT *

FROM dbo.Users WITH (INDEX = CreationDate_Reputation)

WHERE CreationDate > '2018-05-01'

AND Reputation > 100

ORDER BY DisplayName;

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

Last Updated June 4, 2021

Brent Ozar

Production Database Administration

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 snapshots, SQL 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:

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.
QA/test servers
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.
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.
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.
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:

Verify that you have backups. Ideally, do a test restore, too: backup success messages don’t mean you have working backup files.
Stop or shut down client apps. You don’t want folks starting a transaction as your update begins.
Make sure there’s no activity happening on the server, especially long-running jobs like backups.
Apply the update – if you’re using PowerShell, check out how to automate patching with DBAtools.
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.)
Confirm the SQL Server service is started, and check your monitoring tools for any unexpected failures.
Confirm the SQL Server Agent service is started again, and kick off your next log backup job.
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

Last Updated June 15, 2021

Brent Ozar

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

Last Updated June 1, 2021

Brent Ozar

First Responder Kit Updates

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:

Download the updated FirstResponderKit.zip
Azure Data Studio users with the First Responder Kit extension:
ctrl/command+shift+p, First Responder Kit: Import.
PowerShell users: run Install-DbaFirstResponderKit from dbatools
Get The Consultant Toolkit to quickly export the First Responder Kit results into an easy-to-share spreadsheet

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

Fix: avoid arithmetic overflows in wait time checks. (#2916, thanks Erik Darling.)

sp_BlitzWho Changes

Fix: last month’s release wouldn’t save output to table on SQL Server 2008 & R2. (#2875, thanks Mike Halford and Vladiir Vissoultchev aka wqweto.)
Fix: add 1-second lock timeout to avoid blocking on QRY_PROFILE_LIST_MUTEX. (#2907, thanks larkinis.)
Fix: error due to unclosed quotation mark. (#2914, thanks Igor.)

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

Last Updated June 18, 2021

Brent Ozar

Company News

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:

June 15-17: Mastering Query Tuning – Add to Calendar (Europe-friendly times)
July 14-16: Mastering Server Tuning – Add to Calendar (Europe-friendly times)

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

July 19-21: Mastering Index Tuning – Add to Calendar
August 17-19: Mastering Query Tuning – Add to Calendar
August 31-Sept 2: Mastering Parameter Sniffing – Add to Calendar
Sept 21-23: Mastering Server Tuning – Add to Calendar

Fall Fundamentals:

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

Oct 12: How to Think Like the Engine – Add to Calendar (Europe-friendly times)
Oct 13: How to Think Like the Engine – Add to Calendar
Oct 19: How I Use the First Responder Kit – Add to Calendar (Europe-friendly times) – Prerequisites
Oct 20: How I Use the First Responder Kit – Add to Calendar – Prerequisites
Oct 26: Fundamentals of Index Tuning – Add to Calendar (Europe-friendly times)
Oct 27: Fundamentals of Index Tuning – Add to Calendar
Nov 2: Fundamentals of Query Tuning – Add to Calendar (Europe-friendly times)
Nov 3: Fundamentals of Query Tuning – Add to Calendar
Nov 16: Fundamentals of Columnstore – Add to Calendar (Europe-friendly times)
Nov 17: Fundamentals of Columnstore – Add to Calendar
Nov 23: Fundamentals of TempDB – Add to Calendar (Europe-friendly times)
Nov 24: Fundamentals of TempDB – Add to Calendar

Winter 2022 Mastering Rotation:

The Mastering classes require this VM setup.

Dec 6-8: Mastering Index Tuning – Add to Calendar (Europe-friendly times)
Dec 8-10: Mastering Index Tuning – Add to Calendar
Jan 10-12, 2022: Mastering Query Tuning – Add to Calendar (Europe-friendly times)
Jan 12-14, 2022: Mastering Query Tuning – Add to Calendar
Feb 7-9: Mastering Parameter Sniffing – Add to Calendar (Europe-friendly times)
Feb 9-11: Mastering Parameter Sniffing – Add to Calendar
Mar 7-9: Mastering Server Tuning – Add to Calendar (Europe-friendly times)
Mar 9-11: Mastering Server Tuning – Add to Calendar

Spring/Summer 2022 Rotation:

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

March 14-18: Fundamentals Week, US-friendly times
Monday: How I Use the First Responder Kit – Add to Calendar – Prerequisites
Tuesday: Fundamentals of Index Tuning – Add to Calendar
Wednesday: Fundamentals of Query Tuning – Add to Calendar
Thursday: Fundamentals of Parameter Sniffing – Add to Calendar
Friday: Fundamentals of Columnstore – Add to Calendar
March 21-25: Fundamentals Week, Europe-friendly times
Monday: How I Use the First Responder Kit – Add to Calendar – Prerequisites
Tuesday: Fundamentals of Index Tuning – Add to Calendar
Wednesday: Fundamentals of Query Tuning – Add to Calendar
Thursday: Fundamentals of Parameter Sniffing – Add to Calendar
Friday: Fundamentals of Columnstore – Add to Calendar
April 4-6: Mastering Index Tuning – Add to Calendar (Europe-friendly times)
April 6-8: Mastering Index Tuning – Add to Calendar
May 2-4: Mastering Query Tuning – Add to Calendar (Europe-friendly times)
May 4-6: Mastering Query Tuning – Add to Calendar
June 13-15: Mastering Parameter Sniffing – Add to Calendar (Europe-friendly times)
June 15-17: Mastering Parameter Sniffing – Add to Calendar
July 11-13: Mastering Server Tuning – Add to Calendar (Europe-friendly times)
July 13-15: Mastering Server Tuning – Add to Calendar

Summer/Fall 2022 Rotation:

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

June 27-July 1: Fundamentals Week, US-friendly times
Monday: How I Use the First Responder Kit – Add to Calendar – Prerequisites
Tuesday: Fundamentals of Index Tuning – Add to Calendar
Wednesday: Fundamentals of Query Tuning – Add to Calendar
Thursday: Fundamentals of Parameter Sniffing – Add to Calendar
Friday: Fundamentals of Columnstore – Add to Calendar
July 4-8: Fundamentals Week, Europe-friendly times
Monday: How I Use the First Responder Kit – Add to Calendar – Prerequisites
Tuesday: Fundamentals of Index Tuning – Add to Calendar
Wednesday: Fundamentals of Query Tuning – Add to Calendar
Thursday: Fundamentals of Parameter Sniffing – Add to Calendar
Friday: Fundamentals of Columnstore – Add to Calendar
Aug 1-3: Mastering Index Tuning – Add to Calendar (Europe-friendly times)
Aug 3-5: Mastering Index Tuning – Add to Calendar
Aug 29-31: Mastering Query Tuning – Add to Calendar (Europe-friendly times)
Aug 31-Sept 2: Mastering Query Tuning – Add to Calendar
Sept 26-28: Mastering Parameter Sniffing – Add to Calendar (Europe-friendly times)
Sept 28-30: Mastering Parameter Sniffing – Add to Calendar
Oct 24-26: Mastering Server Tuning – Add to Calendar (Europe-friendly times)
Oct 26-28: Mastering Server Tuning – Add to Calendar

Can’t attend live? Check out the recordings.

You 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?

Last Updated May 27, 2021

Brent Ozar

Licensing

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

A primary SQL Server where all the writes & reads take place
A high availability SQL Server sitting right next to the primary, in the same data center, keeping up with its writes, and
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

Last Updated May 26, 2021

Brent Ozar

Architecture

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.

You Captioned This Pic and Won Free Stuff.

Last Updated May 23, 2021

Brent Ozar

Humor

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

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

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

Last Updated May 17, 2021

Brent Ozar

T-SQL

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.

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.
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…)
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.
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.
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.
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.
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.
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.
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

Last Updated December 15, 2021

Brent Ozar

Company News, Humor

650

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:

Prizes:

My favorite caption overall wins a Live Class Season Pass
Runners-up get both a Recorded Class Season Pass: Mastering Classes and Recorded Class Season Pass: Fundamentals Classes
Honorable mentions get a Recorded Class Season Pass: Fundamentals Classes

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

Last Updated May 13, 2021

Brent Ozar

Professional Development, Videos

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

Last Updated May 10, 2021

Brent Ozar

Deadlocks, Videos

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?

Last Updated May 5, 2021

Brent Ozar

Execution Plans, 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…

Last Updated May 4, 2021

Brent Ozar

SQL Server

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:

As you’re writing a query, give a play by play in the comments explaining what you’re doing and why. A. You will catch more of your mistakes.

— BD Softley (@BigfootSql) May 3, 2021

Understand execution plans, look at actual ones and analyse.

— Stefán Jökull Sigurðarson (@stebets) May 3, 2021

anything that makes your life easier makes the optimizer's job harder

— Darling Data (@erikdarlingdata) May 3, 2021

Don’t let perfect be the enemy of good. Write queries up to the point that they do what you want in an acceptable amount of time and using an acceptable amount of resources.

— Mike Scalise (@themikescalise) May 3, 2021

Write two. Pick the better one, if you can work out why it's better.

— Human Teapot (@TimBSpeaks) May 3, 2021

https://twitter.com/gortok/status/1389207832818556931

It formats the query or it gets the hose again.

— Cody Konior (@codykonior) May 3, 2021

https://twitter.com/sqlcheesecake/status/1389239486261997573

Write it, give it to the person next to you. If they can understand its intent, then everything else is easy.

(Because now, there is an infinite amount of help out there should you run into problems…But if no-one can understand it…its a tough road ahead)

— Connor loves SQL , ?and ?? (@connor_mc_d) May 3, 2021

this is my second one, couldn’t resist giving one more:
comment the “special tricks” employed in a query

eg why is there an ‘optimize for’, was a redundant filter added intentionally, etc https://t.co/DCcAZDZvmy

— L_ N___ (@sqL_handLe) May 3, 2021

https://twitter.com/BeginTry/status/1389204584120430592

Understand that your query is only half of what creates the program to fetch your results. The rest is what’s there on the server – statistics, indexes, the capabilities of operators, etc.

— Rob Farley (@rob_farley) May 3, 2021

Share your query online with the title "the best way to write queries”.
Next day you will have a good selection to copy paste.

— ?ister?agoo ???? #Blazor (@mistermag00) May 3, 2021

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.

Last Updated April 29, 2021

Brent Ozar

Execution Plans

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:

SELECT *
FROM dbo.Users u
WHERE u.UpVotes + u.DownVotes > 10000000;

SELECT *

FROM dbo.Users u

WHERE u.UpVotes + u.DownVotes > 10000000;

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:

CREATE INDEX UpVotes_DownVotes ON dbo.Users(UpVotes, DownVotes);
CREATE INDEX DownVotes_UpVotes ON dbo.Users(DownVotes, UpVotes);

1 2	CREATE INDEX UpVotes_DownVotes ON dbo.Users(UpVotes, DownVotes); CREATE INDEX DownVotes_UpVotes ON dbo.Users(DownVotes, UpVotes);

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:

SELECT *
FROM dbo.Users u
INNER JOIN dbo.Badges b ON u.Id = b.UserId
WHERE u.UpVotes + u.DownVotes > 10000000
ORDER BY (u.UpVotes + u.DownVotes) DESC;

SELECT *

FROM dbo.Users u

INNER JOIN dbo.Badges b ON u.Id = b.UserId

WHERE u.UpVotes + u.DownVotes > 10000000

ORDER BY (u.UpVotes + u.DownVotes) DESC;

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

Last Updated April 27, 2021

Brent Ozar

Company News

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:

May 10: Fundamentals of Columnstore – iCal
May 11: Fundamentals of Index Tuning – iCal
May 12: Fundamentals of Query Tuning – iCal
May 13: How I Use the First Responder Kit – iCal
May 14: Fundamentals of Parameter Sniffing – iCal
Bonus: May 20: Fundamentals of TempDB – iCal

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

Last Updated April 20, 2021

Brent Ozar

First Responder Kit Updates

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.

To get the new version:

Download the updated FirstResponderKit.zip
Azure Data Studio users with the First Responder Kit extension:
ctrl/command+shift+p, First Responder Kit: Import.
PowerShell users: run Install-DbaFirstResponderKit from dbatools
Download the updated Consultant Toolkit in your account

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

Fix: some object names would cause errors when saving results to a table. (#2816, thanks Erik Darling and Jefferson Elias.)
Fix: avoid errors with nested parenthesis with compile time values. (#2847, thanks Pete Sral.)

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.