Blog

Updated, Larger Stack Overflow Demo Database

Last Updated July 16, 2024

Stack Overflow publishes a data dump with all user-contributed content, and it’s a fun set of data to use for demos. I took the 2024-April data dump, and imported it into a Microsoft SQL Server database.

It’s an 31GB torrent (magnet) that expands to a ~202GB database. I used Microsoft SQL Server 2016, so you can attach this to anything 2016 or newer. If that’s too big, no worries – for smaller versions and past versions, check out my How to Download the Stack Overflow Database page.

Some quick facts about this latest version:

Badges: 51,289,973 rows; 4.7GB
Comments: 90,380,323 rows; 26.1GB
Posts: 59,819,048 rows; 162.8GB; 32.7GB LOB – this is where you’ll find questions & answers
Users: 22,484,235 rows; 2.6GB; 12.5MB LOB
Votes: 238,984,011 rows; 5.9GB – a fun candidate for columnstore demos

As with the source data, this database is licensed under cc-by-sa-4.0: https://creativecommons.org/licenses/by-sa/4.0/ And to be very clear, this is not my data. The data and the below licensing explanation comes from the Stack Overflow Data Dump’s page:

But our cc-by-sa 4.0 licensing, while intentionally permissive, does require attribution:

Attribution — You must attribute the work in the manner specified by the author or licensor (but not in any way that suggests that they endorse you or your use of the work). Specifically the attribution requirements are as follows:

Visually display or otherwise indicate the source of the content as coming from the Stack Exchange Network. This requirement is satisfied with a discreet text blurb, or some other unobtrusive but clear visual indication.
Ensure that any Internet use of the content includes a hyperlink directly to the original question on the source site on the Network (e.g., http://stackoverflow.com/questions/12345)
Visually display or otherwise clearly indicate the author names for every question and answer used
Ensure that any Internet use of the content includes a hyperlink for each author name directly back to his or her user profile page on the source site on the Network (e.g., http://stackoverflow.com/users/12345/username), directly to the Stack Exchange domain, in standard HTML (i.e. not through a Tinyurl or other such indirect hyperlink, form of obfuscation or redirection), without any “nofollow” command or any other such means of avoiding detection by search engines, and visible even with JavaScript disabled.

This will probably be the last database update.

Prosus (a tech investment company) acquired Stack Overflow a few years ago for $1.8 billion. When a company’s founders sell their baby for money:

The new owners usually want to make a profit on their large investment, and
The new owners rarely share the same goals as the original founders, and
Sometimes the new owners spent way, way too much (hi, Elon) and are forced to make tough decisions to make their debt payments and keep the company afloat

So now Prosus wants to earn their $1,800,000,000 back, and they’re looking at the actual product they bought. StackOverflow.com has 3 components[1]:

An online app that gives you good-enough answers, quickly
The existing past answers already contributed by the community
The potential of future answers continuing to go into the platform

Can Prosus compete on #1? No. Just no. Companies like OpenAI (ChatGPT), Google (Gemini), and Anthropic (Claude) simply have a better solution for #1, full stop, end of story. A web site – even a free one – can’t beat ChatGPT’s ability to integrate directly with your development environment, review your code & database, and recommend specific answers for the problem you’re facing. Game over.

Can Prosus compete on #2? No. The existing answers (as of April 2, 2024) are available for free with nearly no restrictions. The horse is already out of the barn. Moving on.

Can Prosus compete on #3? If ChatGPT and their friends win on #1 and #2, then the default place for developers to find answers is no longer the web browser. (It’s ChatGPT or Copilot or whatever). Whatever happens next is going to be intriguing. Today, you and I are conditioned to think, “I’ll post that question on Stack or a forum.” Tomorrow’s developers will not have that same bias:

Maybe the dev will prompt ChatGPT, “Can you find me answers online for this?” In that case, the LLM will search the web and summarize – and Prosus won’t stand a chance of convincing the user to post the question at StackOverflow.com.
Maybe the dev will open their web browser and ask the question. In that case, the search engine company will try to summarize answers too. These days, both Google and Bing try to avoid landing you on actual web sites, and try to give you the answers on their own pages instead, whether it’s AI-summarized answers or hallucinations or web page summaries next to each site.
Maybe the dev will go to the Github repo for the related project, and post a question there.

I don’t see an easy way for Stack Overflow to inject themselves into that workflow in the year 2030. I’m sad about that because I have a long personal history with Stack Overflow. At the same time, I’m also kinda glad that the original founders, employees, and advisors (me included) were able to cash out thanks to Prosus’s $1.8B overspending just before the generative AI boom hit.

Prosus needs solutions fast: Stack is now losing $150,000 per day. Prosus’s 2024 annual reports noted that Stack Overflow had $98M in incoming revenue – but lost $57M. I can understand why managers might flail at a company’s switches and dials trying to find a way to stop the financial bleeding.

One of the dials they’ve been flailing at is turning down community access to the past answer data, aka business part #2. In their minds, they’re trying to stop OpenAI/Google/Anthropic from making so much money on the back of Stack’s answers. Earlier this year, Prosus tried to pump the brakes on providing the data dumps in XML format on a regular basis, and there was some community outrage, so they relented. However, they’re back: last week, Prosus announced they’re limiting access again.

Based on what Prosus is saying in that post, going forward, I don’t think Prosus will approve of me redistributing new data dumps in a database format. I’m not going to waste time or energy fighting that battle – I’d rather they spent their own energy trying to figure out a way to keep StackOverflow.com a viable business concern going forward. Hopefully they find fun, productive ways to do that, ways that bring the community together onto Prosus’s side rather than turning consumers against Prosus.

However, if Prosus management is willing to limit the data dump, then I have a bad feeling that more barriers are coming over the years. Next, they’ll make answers harder to access for people who have an ad blocker, or who aren’t signed in, or who haven’t paid for a “premium” Stack membership. I’m not mad at them about this, because I don’t have any answers to turn the business around either, and I haven’t heard from anybody who does.

You either die a hero or live long enough to see yourself become the arch-enemy.

[1] Technically the company Stack Overflow has a couple other parts: advertising and Stack Overflow for Teams. Both of those business models are at risk due to AI as well. Their other attempts at diversification, like Articles and Jobs and Developer Story, never caught on.

[Video] Office Hours in Bulgaria

Last Updated June 30, 2024

Brent Ozar

Videos

I went through your top-voted questions from https://pollgab.com/room/brento while in front of the National Gallery for Foreign Art in Sofia.

Office Hours in Sofia, Bulgaria

Watch this video on YouTube

Here’s what we covered:

00:00 Start
03:12 MyTeaGotCold: If all of my columns are nvarchar, is there a performance benefit to always wrapping strings in N”? My tests have been inconclusive.
03:50 SadButTrue: Hey Brent, most of our Azure SQL DBs have top wait stats related to parallelism (CX***) and performance is not great. As we cannot modify the cost threshold for parallelism in Azure SQL, what other techniques can we use to reduce the waits associated with parallelism?
05:00 Dom: Hi Brent, I noticed something strange on a SQL Express 2012. I’m looking at the “Visible online” CPU and was expecting to see 4 (as Express limits to 4 cpu or 1 socket) but it shows 8 visible online cpu… Am I missing something or is my SQL Express really using 8 CPU ? Thanks !
05:36 dba jr: hi Brent, in my company users can design any query they want. for example they can choose multi column for order by or in where clause . i mean queries in APP are not fix. but they tell me queries are slow. tables have million rows. how can I handle this.
07:10 DBA in VA: SQL 2019: What causes a query not to use the execution plan I’ve forced in the query store? Isn’t that the whole idea of the forced plan??
08:00 Ruby Sunday: Is creating a NC index that mirrors the clustered index to avoid blocking considered a bad practice?
09:30 TheyBlameMe: Hi Brent. What’s you opinion of this MS recommendation to “prevent lock escalation” for long running batch operations? BEGIN TRAN; SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0; WAITFOR DELAY ‘1:00:00’; COMMIT TRAN;
10:40 DanishDBA: Hi Brent, my friend needs to create an index on a highly used table on a SQL Server 2019 SE (FCI). On a copy of the db on the same server he knows that it can take up to 4 minutes. The goal is to minimize the impact, as a service window is not allowed. What is the best approach?
11:48 JustWondering: Been suspicious that something “outside” of SS is the problem but don’t know how to prove it. Changed from using JTDS JDBC driver to MS JDBC driver and saw 40+% improvement in runtimes. How could I find what the MS driver is doing different vs JTDS? QryPlans seem the same. Thx.

Announcing Free SQL Server Monitoring.

Last Updated July 17, 2024

Brent Ozar

SQL ConstantCare

You already use sp_Blitz and the rest of the free, open source First Responder Kit to give your SQL Servers a health check now and then.

But let’s be honest: you don’t do it often enough.

You wish you had an easier way to know when your backups stop working, when corruption strikes, when a poison wait is detected, or when a new SQL Server patch comes out for one of your servers.

Good news! We’ve now got a free version of SQL ConstantCare®!

SQL ConstantCare® is our simple monitoring product that you install on a jump box or VM. It connects to your SQL Servers, Azure SQL DB, Amazon RDS, etc. just once per day, gathers diagnostic data, and sends it to our processing servers in AWS. We generate a single daily email per server telling you specific, actionable tasks that will make your databases safer.

The full-blown $695/year version of SQL ConstantCare® gives you performance advice on stuff like query plans, indexes, memory settings, and wait stats, but there are a lot of folks out there who just don’t need that. They can’t fix the databases, queries, or indexes, and they just wanna know that the server is healthy.

That’s where our free health-only monitoring comes in. Sign up here for free, then download the installer, and follow the installation instructions. In a matter of minutes, you’ll get an email, and then again once a day – but only when we’ve got stuff you actually need to do on the server. Otherwise, we leave you alone to do the other important stuff you gotta do every day.

Why are we doing this? What’s the catch?

It’s easy for us to support because we’re not doing anything complex like putting agents on SQL Servers, or running 24/7 monitoring. The app just runs on your jump box as a scheduled task, once per day, and that’s it.

It’s cheap for us to provide because the health-only processing costs us less than $1 per monitored SQL Server per month. We designed SQL ConstantCare® to be serverless and cloud-native right from the start, and Richie’s done a lot of work keeping the code and database lean and mean, so scaling has been pretty easy.

Our back end is fully cloud-hosted, which means your diagnostic data goes up to our services in AWS. If you’ve got questions about how that works, check out how we collect, transmit, and store your data and the frequently asked questions. To help us with GDPR compliance, we automatically delete all data older than 30 days. We won’t ever offer a version of SQL ConstantCare® that you can host yourself – we rely on too many AWS services, and helping you set all that up and troubleshoot it would make the price tag crazy high. If you want your own private monitoring, you’re better off buying a conventional 24/7 monitoring app. Of course, those are more expensive, and priced per monitored server – but if you want that level of control, that’s the price you pay. (Literally.) We’re trying to help as many people as we can here, as inexpensively as possible.

The signup process is a little wonky, not as smooth as I’d like. When you click the signup link, you’ll be asked to sign up with an email or Google. If you’ve bought training classes or software from me in the last few years from training.brentozar.com, you can use the same login. If you hit any roadblocks signing up and you can’t figure out how to get past ’em, email Help@BrentOzar.com and include a screenshot of the full browser, including the URL you’re on.

I hope this helps make your job easier. Like the First Responder Kit, this here blog, and the tons of videos we put out across my YouTube channel, TikTok, LinkedIn, etc, I really want to help do as much as I can, for free. That way, when you need training or consulting, you’ll remember who loves ya, baby. Enjoy!

Use “We” Not “You”. #tsql2sday

Last Updated July 2, 2024

Brent Ozar

Consulting Lines

For T-SQL Tuesday this month, Louis Davidson suggested we give our past self some advice.

I’d tell myself, “Use ‘we’, not ‘you’.”

For years, when I gave advice, I’d say things like:

“You’re doing A, when you should really be doing B instead.”
“Your code has a problem right here.”
“Your network settings are wrong, and you should change them to this instead.”

The very word ‘you’ sets up a confrontational tone that puts the recipient on the defensive. They can’t help but react by taking things personally. We’re just humans, meatbags of emotion.

Instead, use words like ‘we’ and ‘our’ that group us together. We’re on the same team, and our common enemy is technology. Dagnabbit, technology sucks hard. It’s always out to get us, to make our lives miserable, to refuse to work the way it says it’ll work in the manual.

Once we (see what I did there) get started using the term ‘you’ early on in our careers, it’s a really hard habit to break. I know that, because I’ve been trying to break it for years.

I do use the term ‘you’ a lot in blog posts and videos that are purposely designed to be confrontational and drive engagement. That’s on purpose. However, when I wanna give advice to someone on my own team, I try to remember that we are indeed on the same team, and I need to communicate that by using the word ‘we.’

[Video] Office Hours in Sofia, Bulgaria

Last Updated June 30, 2024

Brent Ozar

Videos

I was honored to speak at the Present to Succeed conference in Sofia, Bulgaria, run by a former SQL Server MCM. Sofia is a beautiful city, and the gorgeous Patriarchal Cathedral of St. Alexander Nevsky (Wikipedia) was close to my hotel, so I dragged my tripod over there for an Office Hours session.

Office Hours in Sofia, Bulgaria

Watch this video on YouTube

Here’s what we covered:

00:00 Start
01:19 Live is Life: After a migration to a new db, the old 3TB db is now read only. My friend is enabling row compression on the biggest tables/indexes and already got down to 1,5TB. The goal is to get faster reads and a smaller db. What are your thoughts about this? Is there a better way?
02:40 MyTeaGotCold: Is Enterprise Edition generally seen as the norm? Blogs and particularly the official docs rarely point out that something isn’t available in Standard, but I’ve gone my entire career without seeing it.
03:20 EthicalDBA: Hey Brent, have you ever faced any moral/ethical issues in your DBA career that caused you to really question the task you were working on?
04:35 With NeinLock: Greetings! As someone who is a household name when it comes to the SQL Server community, have you noticed members of the younger generation joining the community to give back and share knowledge? If yes, are there any you recommend following?
08:22 Roger ap Gwilliam: What’s the scariest RDBMS you have worked with?
08:58 DT_DBA: Do you think using the FORCESEEK hint “everywhere” is okay? (same as how some people use NOLOCK). I have a client that has started doing this. They aren’t specifying which index to use, just doing things like “select * from table WITH (FORCESEEK) join view WITH (FORCESEEK) …”
09:40 handysql: Helmet on the shelf. Is that for show or do you have a harley/rocket in the garage?
11:35 Mike: Is there a good criteria that can be used to tell if a query is OLTP or OLAP query ? Is it number of seconds (duration, or cpu time), or number of logical reads, or something else ?
12:48 VegasDBA: Hi Brent! Ever do any big physical to virtual conversions? I’ve been tasked with a very aggressive timeline to convert several physical SQL AGs to VMs. I was considering using AGs or distributed AGs to fail them over. Was curious of your thoughts and opinions.
13:46 Mike: Is there a good up-to-date article that describes differences between Junior, Middle and Senior DBA in SQL Server (and maybe Azure SQL ?). And where can you develop next after becoming “Senior” ?
15:53 Davros: When a traditional clustered index table starts to having too many indexes is this an indication that the table should be using column store?

Save $350 on the PASS Summit if You Register Now.

Last Updated June 30, 2024

Brent Ozar

#SQLPass

Next Tuesday, pricing on the 3-day tickets for the PASS Data Community Summit goes up to $2,095.

But if you register right now, plus use coupon code BRENTO24, it’s just $1,745.

It’s one in-person conference that covers Microsoft SQL Server, Azure, PostgreSQL, Snowflake, Oracle, and more. So many of us (me included!) are working with multiple databases these days, and it’s hard to find a single event with this kind of coverage.

I’m also teaching a 1-day pre-conference workshop on Monday, Tuning T-SQL for SQL Server 2019 and 2022. That’s an additional $595, and you can also sign up for another pre-conference workshop on Tuesday. There are great options in there including Query Quest with Erik & Kendra, Microsoft Fabric in a Day, PostgreSQL Fundamentals, Power BI Architecture, and a SQL AI Workshop run by Microsoft folks.

Go register now, and I’ll see you in Seattle!

Does Bit Column Order Matter in Tables?

Last Updated June 30, 2024

Brent Ozar

Development

At the PGConf.dev, where Postgres developers get together and strategize the work they wanna do for the next version, I attended a session where Matthias van de Meent talked about changing the way Postgres stores columns. As of right now (Postgres 17), columns are aligned in 8-bit intervals, so if you create a table with alternating columns:

MyBitColumn1 – 1 bit used
(7 bits wasted for alignment to get to the next byte)
SomeOtherColumn – any other datatype, but not a bit
MyBitColumn2 – 1 bit used
(another 7 bits wasted for alignment)

Matthias pointed out that was inefficient, and that Postgres should separate physical column order from logical column order. Under the hood, it should just store:

MyBitColumn1 – 1 bit used
MyBitColumn2 – 1 bit used
(6 bits wasted for alignment)
SomeOtherColumn – any other datatype

Microsoft SQL Server already does this with bits, as the documentation explains:

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

To demonstrate that, I whipped up a demo script showing two table creations: one with the bit columns scattered around through the table, and one where they’re all grouped together:

CREATE DATABASE TestColumnAlignment;
GO
USE TestColumnAlignment;
GO

CREATE TABLE dbo.Disorganized
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TinyInt1 TINYINT,
Bit1 BIT,

TinyInt2 TINYINT,
Bit2 BIT,

TinyInt3 TINYINT,
Bit3 BIT,

TinyInt4 TINYINT,
Bit4 BIT,

TinyInt5 TINYINT,
Bit5 BIT
);




CREATE TABLE dbo.Organized
(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

Bit1 BIT,
Bit2 BIT,
Bit3 BIT,
Bit4 BIT,
Bit5 BIT,

TinyInt1 TINYINT,
TinyInt2 TINYINT,
TinyInt3 TINYINT,
TinyInt4 TINYINT,
TinyInt5 TINYINT
);


INSERT INTO dbo.Disorganized
	(Bit1, Bit2, Bit3, Bit4, Bit5, TinyInt1, TinyInt2, TinyInt3, TinyInt4, TinyInt5)
	SELECT 1, 1, 1, 1, 1, 1, 1, 1, 1, 1
	FROM GENERATE_SERIES(1, 10000000);


INSERT INTO dbo.Organized
	(Bit1, Bit2, Bit3, Bit4, Bit5, TinyInt1, TinyInt2, TinyInt3, TinyInt4, TinyInt5)
	SELECT 1, 1, 1, 1, 1, 1, 1, 1, 1, 1
	FROM GENERATE_SERIES(1, 10000000);
GO
	
EXEC sp_BlitzIndex @Mode = 2;

CREATE DATABASE TestColumnAlignment;

USE TestColumnAlignment;

CREATE TABLE dbo.Disorganized

(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

TinyInt1 TINYINT,

Bit1 BIT,

TinyInt2 TINYINT,

Bit2 BIT,

TinyInt3 TINYINT,

Bit3 BIT,

TinyInt4 TINYINT,

Bit4 BIT,

TinyInt5 TINYINT,

Bit5 BIT

);

CREATE TABLE dbo.Organized

(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

Bit1 BIT,

Bit2 BIT,

Bit3 BIT,

Bit4 BIT,

Bit5 BIT,

TinyInt1 TINYINT,

TinyInt2 TINYINT,

TinyInt3 TINYINT,

TinyInt4 TINYINT,

TinyInt5 TINYINT

);

INSERT INTO dbo.Disorganized

(Bit1, Bit2, Bit3, Bit4, Bit5, TinyInt1, TinyInt2, TinyInt3, TinyInt4, TinyInt5)

SELECT 1, 1, 1, 1, 1, 1, 1, 1, 1, 1

FROM GENERATE_SERIES(1, 10000000);

INSERT INTO dbo.Organized

(Bit1, Bit2, Bit3, Bit4, Bit5, TinyInt1, TinyInt2, TinyInt3, TinyInt4, TinyInt5)

SELECT 1, 1, 1, 1, 1, 1, 1, 1, 1, 1

FROM GENERATE_SERIES(1, 10000000);

EXEC sp_BlitzIndex @Mode = 2;

sp_BlitzIndex shows that both tables have the same size:

If we add another bit column to both tables:

ALTER TABLE dbo.Disorganized
	ADD Bit6 BIT;

ALTER TABLE dbo.Organized
	ADD Bit6 BIT;
GO
EXEC sp_BlitzIndex @Mode = 2;
GO

ALTER TABLE dbo.Disorganized

ADD Bit6 BIT;

ALTER TABLE dbo.Organized

ADD Bit6 BIT;

EXEC sp_BlitzIndex @Mode = 2;

Size still remains the same:

Because SQL Server’s just making a metadata-only change, noting that 1 of the 8 bits in the bit-designated space is now available for use by the new Bit6 column. To really drive that point home, let’s go back and update the new Bit6 column to be 1:

UPDATE dbo.Disorganized SET Bit6 = 1;
UPDATE dbo.Organized SET Bit6 = 1;
GO
EXEC sp_BlitzIndex @Mode = 2;
GO

UPDATE dbo.Disorganized SET Bit6 = 1;

UPDATE dbo.Organized SET Bit6 = 1;

EXEC sp_BlitzIndex @Mode = 2;

And then check the space used again:

Yep, still 194MB. Good work, Microsoft.

The more I learn about Postgres, the more I appreciate so many little things that Microsoft has done over the years for performance & space optimization. The one that’ll really surprise you is that Postgres still doesn’t have table or index compression yet, although it does offer value-level compression.

Updated First Responder Kit and Consultant Toolkit for July 2024

Last Updated July 1, 2024

Brent Ozar

First Responder Kit Updates

The next release would normally be August, but I’m doing a release a little early in order to fix 2 issues with the Consultant Toolkit. The May release broke the ability to automatically upload results to Amazon S3, and didn’t include all of sp_BlitzLock’s results. Those are now fixed, so I wanted to get another release out quickly so consultants don’t have to keep using the old version.

If you’ve hard-coded First Responder Kit installer file names, there was a big recent change. There are now just 2 installer scripts: Install-All-Scripts.sql, and a new Install-Azure.sql, which only installs the scripts that are compatible with Azure SQL DB. The old Install-Core scripts are gone because we’ve deprecated sp_AllNightLog, sp_BlitzInMemoryOLTP, and sp_BlitzQueryStore. Read on for why.

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

We fixed the problems with AWS S3 uploads and sp_BlitzLock results that were introduced by May’s big changes. As a reminder, here’s what we did in May because the changes were pretty big:

Supports Microsoft Entra multi-factor authentication (MFA)
Automatically retries failed connections, allowing you to more easily gather data from Azure SQL DB Serverless databases that auto-paused
No longer requires create-table permissions, so it works better in environments where you can pull diagnostic data but not see database contents
Requires .NET Desktop Runtime 7 or higher on the machine where you run the Consultant Toolkit, typically your jump box or laptop (not the SQL Server itself)

sp_Blitz Changes

Enhancement: several checks for Query Store settings & trace flags. (#3531, #3535, #3536, thanks Reece Gooding)

sp_BlitzLock Changes

Enhancement: add @DeadlockType parameter to filter for just regular or parallelism deadlocks. (#3525, thanks Erik Darling)
Enhancement: add max CPU and elapsed times. (#3545, thanks Erik Darling)

sp_DatabaseRestore Changes

Enhancement: add @EnableBroker parameter to turn on Service Broker after a restore. (#3532, thanks John McCall)

Deprecating sp_BlitzInMemoryOLTP, sp_BlitzQueryStore, and sp_AllNightLog

sp_BlitzQueryStore was originally written by Erik Darling when he worked here. He’s moved on to start his own excellent company, plus his own sp_QuickieStore. You should be using that instead.

sp_BlitzInMemoryOLTP was always kinda distributed as a courtesy – the real home for it is in KTaranov’s Github repository, and you can still find it there. It hasn’t been updated in over 6 years, and I’ve never seen anyone using it, so I’m removing it to streamline support issues.

sp_AllNightLog was a ton of fun when we built it several years ago, but it’s consistently had a problem. Companies start using it, then decide they want to build something even more ambitious, typically a C# service with robust error handling and scheduling. sp_AllNightLog isn’t the kind of thing I want to encourage beginners to use – it’s complex.

So when Reece Goding started working on cleaning up sp_BlitzQueryStore’s code, I decided that now was the time to deprecate stuff we no longer use or recommend. You’re definitely welcome to continue to use ’em if you get value out of ’em! I’ve going to move these procs into the Deprecated folder, plus simplify the installation scripts. For the rest of 2024, the only installer script will be Install-All-Scripts.sql.

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.

[Video] Office Hours: I Don’t Have All the Answers

Last Updated June 5, 2024

Brent Ozar

Videos

When I went through the top-voted questions at https://pollgab.com/room/brento, I didn’t have all the answers today:

Office Hours: I Don't Have All the Answers

Watch this video on YouTube

Here’s what we covered:

00:00 Start
00:48 MyTeaGotCold: I increased my RAM from 16 GB to 32 GB and my buffer pool has grown, but my PAGEIOLATCH_SH waits went up by two percentage points. What might that indicate?
01:46 DavDBA: Hey Brent, Sp_Blitz is recommending that I change the target recovery interval from 0 to 60. I want to implement this, but I read in the Microsoft documentation that it can cause extra I/O activity on certain systems. Have you encountered such issues?
02:32 John: A friend of mine has several hundred SQL AG’s used for DR, not HA. These are all manual failover, async commit. They want to remove WFSC, implementing Read-Scale AGs instead. Is this risky on such a large scale? Are there serious gotchas to be concerned about?
03:30 WindowsMillenniumWasAnInsideJob: Conspiracy theory: Microsoft won’t take performance issues in SQL Server seriously until Azure SQL DB won’t have majority of the market share (vs boxed editions). Only then Microsoft will fix them because it will be in their interests to do so. What do you think about it?
04:55 SQL_TheOcean: Is there a way to copy the Managed instance databases across a different region in Azure.
05:32 Eduardo: What is your opinion of Natural language to SQL query in Azure SQL DB? Is this a game changer?
06:45 Steve E: Hi Brent, When query tuning, I often find myself trying to fix a bad estimate and wondering how SQL Server made an estimate. Is there a trace flag or tool that shows how SQL server came up with estimates on plan operators – what stats it used, what formulas it applied etc. Thanks
07:32 ConfusedDBA: My friend is using spatial index on a table with less than 10k rows. He does not have a problem with this table but often times he hears that the usage of spatial index/data type will cause issues. Are those remarks valid? If yes, what kind of problem he can expect in the future?
08:23 SQLBaller: Have you seen it where when running a stored procedure it will complete in two seconds? However, if you run that same stored procedure with include actual execution plan it takes ten minutes to run.
09:36 Need4Speed: Is there a way to optimize a table strictly used for inserts of archival records? The table currently has 62 million records and only has a primary key. I have noticed that my process for inserts is running a little slow and was wondering if there was anything I could check.
10:21 ChompingBits: My friend has a huge vendor owned application. The vendor’s configuration makes use of Resource Governor which frequently kills their OLA DBCC runs, and doing so in a way that creates a false positive for disk errors. Is there a way to verify RG killed a process?
10:57 i_use_uppercase_for_SELECT: Do you have any recommendations or best practices for query store when restoring to lower environments with a different database name? The plans seem to include the database name and fail forcing plans.
11:50 themoderndba: I had you mention in a prior podcast a Microsoft guide for faster data loads to a database. What is the exact name of the guide? I am interest in this for a current ETL task.

Join Me in Seattle for Watch Brent Tune a Query in SQL Server 2022

Last Updated June 19, 2024

Brent Ozar

#SQLPass

The PASS Data Community Summit session lineup for Nov 4-8 in Seattle was just announced, and I’ve been selected for a general session!

Watch Brent Tune a Query in SQL Server 2022: Ever wonder how somebody else does it? In this all-demo session, watch over Brent Ozar’s shoulder while he takes a slow stored procedure in the Stack Overflow database, analyzes it, and iterates over several improvements trying to make it go faster. He’ll explain his thought process as he goes, and get feedback from the audience on what they’d try as well.

Goals:

Learn to use sp_BlitzCache to identify which queries in a proc need tuning first
Understand the use of query hints to test possible database setting changes
See how to measure the overhead of query hints like recompiles

Prerequisites: You should be comfortable writing T-SQL, reading execution plans, and using STATISTICS IO to measure logical reads.

Register now because discounted early bird registration prices are available til July 9th:

3-day conference ticket $1,895: includes the Weds/Thurs/Fri general sessions like mine above
Use referral code BRENTO24 for another $150 off that 3-day price
Pre-conference workshops: $595 each, like my Tuning T-SQL for SQL Server 2019 and 2022 on Monday

See you in Seattle!

The SQL Language is Just a Hurdle You Gotta Overcome.

Last Updated June 25, 2024

Brent Ozar

T-SQL

I’m not talking just about Microsoft SQL Server specifically here, nor T-SQL. Let’s zoom out a little and think bigger picture for a second: is the SQL language itself a problem?

Sometimes when I talk to client developers, they gripe about the antiquated language.

The order of a SELECT statement doesn’t make any sense. You shouldn’t state what you’re looking for, before you even say where you wanna get the data from. The FROM should really go first so that query-completion tools like IntelliSense have a fighting chance to help you write the SELECT part. If we started writing our queries like this:

FROM dbo.Users u
INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId
SELECT ...

FROM dbo.Users u

INNER JOIN dbo.Posts p ON u.Id = p.OwnerUserId

SELECT ...

Then as you started typing stuff in the SELECT, you could actually get useful stuff out of IntelliSense. How many times have you started typing a query, and query completion tools start throwing all kinds of system functions at you? Idiotic.

Exception handling is a painful mess. Let’s be honest here: the majority of stored procedures and functions out there don’t have error handling. They YOLO their way through the data, hoping and praying that things are as we expect, we have the right permissions, structures haven’t changed, and the data is in a useful state. Everybody looks the other way and mumbles, “We’ll handle errors on the application side,” when in reality those errors are either thrown directly at the innocent user, or simply suppressed and not logged anywhere.

It’s not really a standard. Oh sure, SELECT/FROM/WHERE/ORDER BY works in most databases, but even trivially simple applications break if you try to port them from one database management system to another. Your skills transfer in a similar way: even if you’re great at T-SQL exception handling, you’re still gonna have to tweak the way you do it in Postgres. The concepts are standard, but the specifics are different.

Unit testing is a pipe dream. App code developers know if their code changes will break something. Database developers just punt their stuff into development, run the query a few times, nod because no errors get thrown, and then toss it into production. When code breaks weeks or months later, all we hear is, “Nothing’s been changed.”

So why haven’t we moved on past SQL?

In some ways, we have, with object-relational mapping (ORM) tools like Entity Framework, Hibernate, and Django. The database administrator readers here in the audience usually cringe when they hear those words, but the reality is that developers leverage those tools heavily to build new applications. I don’t blame them. I would too, for all the reasons I talked about above.

What those tools do is translate your desires into SQL, though, which brings us right back where we started. Often, the SQL they generate sucks for performance, thus the typical DBA’s feelings about ORMs. So why haven’t we got a new standard way for applications to talk directly to databases, in a secure, performant, and easy-to-write way?

It’s not for lack of trying: at least once every 6 months, I see a post on HackerNews about a better replacement for SQL. Someone puts a lot of thought into the problems, puts a lot of work into a replacement, and then proudly announces it.

And nobody uses it.

Because SQL is the lowest common denominator that works damn near everywhere, for values of “works.”

It works on the back end. Remember when NoSQL came out, and everybody was all “databases r doomd”? And remember what business users said when they wanted to run their reports? NoSQL persistence layers pretty quickly changed their tune, saying, “Oh, well, uh, we meant Not Only SQL, that’s what we meant,” as they struggled to quickly slap in SQL compatibility. Even MongoDB, king of NoSQL, implemented SQL support.

It works on the front end, especially the reporting front end, which is what managers care about. The people who sign the checks wanna see their data in Power BI and Excel. Every new reporting tool that comes out, in order to check boxes and say they’re compatible with every database, implements SQL support. Oh sure, these tools write horrific queries, but they check the box to say they can get data out of all your different persistence layers, and they do it with SQL first because it’s cheap and easy to support lots of databases that way.

I’ll leave you with an amusing quote from Bjarne Stroustrup:

There are only two kinds of languages: the ones people complain about and the ones nobody uses.

[Video] Office Hours: Database Answers from a Hotel Room

Last Updated June 5, 2024

Brent Ozar

Videos

I went through your top-voted questions from https://pollgab.com/room/brento before heading out to PGConf.dev in Vancouver.

Office Hours: Database Answers in Vancouver

Watch this video on YouTube

Here’s what we covered:

00:00 Start
01:43 Poul J: Hi Brent. Can you give some examples of how a CHECK() constraint is used by the optimizer. Is it similar to a filtered index… Or is there more to it?
03:54 RadekG: Hi Brent, Could you explain when high wait statistics of parallelism type indicate a problem? I wander what is even a point in monitoring them… (but I am almost sure that I am missing something obvious here)
04:51 Ricardo: My secret-sauce as a DBA was getting sophisticated work done through the GUI (EG: Always On). Now with Azure Portal, Databricks, etc, the GUI seems to change daily. Do you think the days of the GUI are numbered? (the days of knowing a GUI intimately, like an engine bay).
05:45 tibbler: Hi, imagin you have complex structured data in a database. You’d like to archive them for long term, the structure should be preserved too. Would you recommand a database for this purpose?
06:57 Ricardo: When performance tuning what ball-park figures do you use relating [time] to [rows returned]. EG: What is a reasonable amount of time to return 200,000 rows in a report?
08:48 Ozan: Hi Brent, when using SQLQueryStress with enough number of threads and iterations to get THREADPOOL waits, i can still see enough available worker threads after summing up the active_workers_count field in dm_os_schedulers. How is that possible? Thanks
09:50 Andrew: How transferrable is Oracle DBA experience to the Microsoft stack? I’m reviewing job applications for someone on my team, we use the Microsoft stack, but have a few applicants with years of Oracle experience – do you see the concepts as equivalent or transferrable?

Query Exercise Answer: Beating ChatGPT at Finding Good Question Times

Last Updated June 15, 2024

Brent Ozar

Query Exercise Answers

For this week’s Query Exercise, I asked you to write a better query than ChatGPT wrote. Your goal was to find the best days and times to post questions on Stack Overflow.

I found it interesting that a lot of the initial answers focused on the times when there were the most questions, or which questions were the most highly upvoted. For me, the best time to post a question is when you have the highest likelihood of getting the right answer, quickly.

When someone posts a question, they can accept an answer as the right one. You can see it by looking for checkmarks next to an answer. The checkmark indicates that the answer was accepted by the original question-asker.

The accepted answer may not be the best one overall, especially as additional answers come in later over time. However, the accepted answer was good enough for the person who asked the question – and when I’m asking a question, that’s what my goal is, to get an answer that’s good enough to solve my problem, and move on.

In the Posts table where questions & answers are stored, there’s an AcceptedAnswerId column. If a question has an Id in the AcceptedAnswerId, then that’s the Posts.Id for the answer row.

Let’s try this:

SELECT TOP 100 DATENAME(weekday, pQ.CreationDate) AS DayOfWeek,
	DATEPART(hh, pQ.CreationDate) AS HourOfDay,
	AVG(DATEDIFF(mi, pQ.CreationDate, pA.CreationDate)) AS AvgMinutesToAnswer,
	SUM(1) AS QuestionsAnswered
FROM dbo.Posts pQ
INNER JOIN dbo.Posts pA ON pq.AcceptedAnswerId = pA.Id
WHERE pQ.PostTypeId = 1 /* Question, but not really needed */
GROUP BY DATENAME(weekday, pQ.CreationDate),
	DATEPART(hh,pQ.CreationDate)
ORDER BY AVG(DATEDIFF(mi, pQ.CreationDate, pA.CreationDate)),
	SUM(1) DESC;

SELECT TOP 100 DATENAME(weekday, pQ.CreationDate) AS DayOfWeek,

DATEPART(hh, pQ.CreationDate) AS HourOfDay,

AVG(DATEDIFF(mi, pQ.CreationDate, pA.CreationDate)) AS AvgMinutesToAnswer,

SUM(1) AS QuestionsAnswered

FROM dbo.Posts pQ

INNER JOIN dbo.Posts pA ON pq.AcceptedAnswerId = pA.Id

WHERE pQ.PostTypeId = 1 /* Question, but not really needed */

GROUP BY DATENAME(weekday, pQ.CreationDate),

DATEPART(hh,pQ.CreationDate)

ORDER BY AVG(DATEDIFF(mi, pQ.CreationDate, pA.CreationDate)),

SUM(1) DESC;

The results in the 2018-06 version of the Stack Overflow database:

It’s looking like weekend mornings are the best times to post questions – but even then, it takes a week to get to a good answer! You might think (or at least I did), “Well, if it takes a whole week, does it even make a difference when I post the question?” Let’s flip the sort order and look for the worst times:

That’s kinda wild – it’s weekday afternoons! (We’ll set time zones aside for this, but that’s a whole ‘nother exercise.) That made me wonder: if we only group the data by day of week, what does it look like?

SELECT DATENAME(weekday, pQ.CreationDate) AS DayOfWeek,
	AVG(DATEDIFF(hour, pQ.CreationDate, pA.CreationDate)) AS AvgHoursToAnswer,
	SUM(1) AS QuestionsAsked,
	COUNT(DISTINCT pA.Id) AS QuestionsAnswered
FROM dbo.Posts pQ
LEFT OUTER JOIN dbo.Posts pA ON pq.AcceptedAnswerId = pA.Id
WHERE pQ.PostTypeId = 1 /* Question, but not really needed */
GROUP BY DATENAME(weekday, pQ.CreationDate)
ORDER BY AVG(DATEDIFF(hour, pQ.CreationDate, pA.CreationDate))

SELECT DATENAME(weekday, pQ.CreationDate) AS DayOfWeek,

AVG(DATEDIFF(hour, pQ.CreationDate, pA.CreationDate)) AS AvgHoursToAnswer,

SUM(1) AS QuestionsAsked,

COUNT(DISTINCT pA.Id) AS QuestionsAnswered

FROM dbo.Posts pQ

LEFT OUTER JOIN dbo.Posts pA ON pq.AcceptedAnswerId = pA.Id

WHERE pQ.PostTypeId = 1 /* Question, but not really needed */

GROUP BY DATENAME(weekday, pQ.CreationDate)

ORDER BY AVG(DATEDIFF(hour, pQ.CreationDate, pA.CreationDate))

I added a couple more columns because the results are pretty conclusive:

Post your questions on the weekends. Sure, there are way less questions coming in at that time – but that’s also when you get more eyeballs on your questions because you have less competition. You’re more likely to get a good answer, faster, when you’re not competing with other questions.

Is the moral of the story that ChatGPT’s answer was bad? No, or at least, no worse than some of the answers us meatbags came up with initially. I think the key to asking a good data question is to keep following up with more questions. What do the query results show? Where do we think the loopholes are? What’s the real business objective that we’re trying to achieve? How do we gauge the accuracy of an answer?

Join Me in San Diego for SQL Saturday!

Last Updated June 18, 2024

Brent Ozar

Conferences and Classes

I’m coming to San Diego on Sept 13-14 for SQL Saturday San Diego!

I’m teaching a one-day pre-conference workshop on Friday, September 13th.

Tuning Databases In One Day – You’ve got production databases in SQL Server or Azure SQL DB, and you want to make ’em faster. You need to identify the database’s bottleneck, prove the root cause, and then recommend fixes. You want to make the right choice for each bottleneck – should you do index changes, query tuning, or server-level settings?

The class will be a mix of 50% slides, and 50% live demos, with plenty of time for Q&A. We’ll even cover 3 sample client findings for the most common performance issues so you can see how I explain the issues to my own clients, and give them proof.

I’m Brent Ozar, and I do this for a living. In one day, I’ll teach you the exact same techniques I use with my clients. I can’t teach you everything about what I do in one day – but I’ll teach you the most important stuff.

Register for the $149 pre-con here, then register for the free SQL Saturday here. Only 100 pre-con tickets are available, so move quickly!

[Video] Creepy Office Hours in Vancouver

Last Updated May 24, 2024

Brent Ozar

Videos

While in a hotel room in Vancouver for PGconf.dev, I strapped on my Apple Vision Pro headset to take your top-voted questions from https://pollgab.com/room/brento. Somehow, the latest Vision OS update gave me a 1980s 3rd Bass haircut.

Office Hours: Creepy Vancouver Edition

Watch this video on YouTube

Here’s what we covered:

00:00 Start
01:03 MyTeaGotCold: Have you ever seen SSISDB migrated on to a new server without difficulty? I’ve never seen it go smoothly.
01:52 ThatSteveCena: https://www.sqlskills.com/blogs/jonat… Is the juice worth the squeeze to try individually tuning your index fill factors, or are there better means of performance gains?
02:40 Joseph: Getting a corrupt database message when running maintainance but the DB ID does not exist “DESCRIPTION: Corruption in database ID -4294967288, object ID 60 possibly due to schema or catalog inconsistency. Run DBCC CHECKCATALOG.” Have run DBCC Checks on all user and sys dbs.
04:05 TractorDBA: My friend’s security dept. decided that using the fallback cert is no longer good enough for internal network encryption. Security is not your thing, but is there a good resource who explains the ins and outs of certificate mgmt. for a large enterprise of SQL Servers?
04:50 SQL_Stormlight: GrumpyOldMan had a similar issue. SQL is recommending an index but it already exists and is being used. In my case, there are only two columns. I’ve tried swapping the column order and even tried different ASC/DESC yet SQL still wants this. Maybe it’s a bug or a bad rec?
05:45 aPartyPerson: My friend wonders if a View (with no WHERE and can’t be indexed due to bad life choices) used in a query could be replaced with a SP, and perform better. SELECT * FROM myV v WHERE v.typ = 1 SELECT * FROM OPENQUERY([LOCALSERVER], ‘SET FMTONLY OFF EXEC EXEC mySP @typ = 1’);
06:36 KyleDevDBA: Is dynamic SQL an acceptable way to add conditional joins/filters onto a query?
07:05 Ozan: Hi Brent, since MaxDOP can be set on DB level, is there still any reason why not to mix SharePoint with Non-SharePoint-DBs on the same SQL instance? Thanks
08:26 SQL_theocean: Was wondering which permission is required apart from db_owner in case the user wants to make his own database online after they make them offline in SQL server 2022.
09:36 MadridMoneky: We have AlwaysOn Availability Groups spanned across two data centers (~100 SQL Servers), one Data Center is migrating, unavailable for two weeks. How do we Manage the AGs? Do we have remove the DBs from the AG and reseed thousands of DBs when secondary is back online?
10:32 Richard at Analytic: Visual Studio Code vs Visual Studio. It appears that Microsoft is favoring Visual Studio Code over Visual Studio whenever there is no GUI or only an HTML UI associated with the project. What is your opinion on this?

Query Exercise: Beat ChatGPT at Finding Good Question Times

Last Updated July 3, 2024

Brent Ozar

Query Exercises

What are the best days of the week and times of the day to post a question at StackOverflow.com?

It seems like a simple question, but it’s surprisingly nuanced. I asked ChatGPT’s latest version, 4o, and its answer made me laugh out loud. First off, the T-SQL is terrible: it creates a completely unnecessary temp table. However, a bit of congratulations are in order: at least ChatGPT is trained on the Stack Overflow database schema, and it understood that you have to filter for PostTypeId = 1 for questions (and even put a comment indicating that!)

ChatGPT’s answer assumes that our criteria for a good question is the question’s Score – and I think that’s not a bad assumption to make. After all, a lot of people use Stack Overflow as a game, trying to maximize their reputations. However, let’s assume that we post questions in order to get answers.

I followed up by asking ChatGPT:

What are the best days of the week and times of the day to get good answers quickly?

Again, ChatGPT gets a low score for its generated T-SQL, a high score for understanding the database without additional training, and a flat out failure for botching the query and the results:

So, with that in mind, can you do better than ChatGPT?

Any size version of the Stack Overflow database will work for this exercise. (ChatGPT’s query even fails on the tiny 10GB version.)

For this exercise, I’m not expecting a specific “right” or “wrong” answer – instead, for this one, you’re probably going to have a good time sharing your answer in the comments, and comparing your answer to that of others. Feel free to put your queries in a Github Gist, and include that link in your comments. After you’ve worked on it for a while, check out the answers discussion post.

There’s a 6-Month Statute of Limitations on “The Last Person.”

Last Updated May 23, 2024

Brent Ozar

Processes and Practices

“The last person must have set it up that way.”

“The last person wrote that code.”

“The last person just didn’t configure it right.”

You can use that excuse for 6 months.

For six months, you’re allowed to get up to speed on the company’s politics, the intricacies of the app, and the responsibilities of different departments. You’re allowed to prove yourself to your peers, building up social capital so that they’ll take your recommendations and run with ’em..

You can take your time figuring out whether the last person knew what they were doing or not. You should start by assuming that you did, and give them some benefit of the doubt because they were under time pressure just as you are now. If you’re generous and curious, you can even try to contact them through unofficial channels, offer to buy them lunch, and chat about their experiences at the company.

But after 6 months, the statute of limitations is up.

After that, you’re not allowed to blame “the last person” anymore.

The last person can no longer be prosecuted for their crimes against the database, and they are absolved of any guilt. It doesn’t matter who was originally responsible last year: the person responsible is now you.

So pull yourself up by the bootstraps, write up a health check with sp_Blitz, and start working through the problems. Put the correct backups and corruption checking in place. Schedule that outage. Apply the patches. Fix those linked servers using the SA login.

Because after 6 months, if you’re not fixing these problems, the clock is already starting to tick down to when you will be referred to as “the last person”, and they’re going to roll their eyes when they talk about your inability to get the job done.

Just like you’ve been doing about “the last person” for over 6 months.

[Video] Office Hours in a Hotel Room

Last Updated May 16, 2024

Brent Ozar

Videos

While in Nashville for a creators conference, I went through your top-voted questions from https://pollgab.com/room/brento.

Office Hours: Database Answers in Nashville

Watch this video on YouTube

Here’s what we covered:

00:00 Start
00:38 Bisal Basyal: What is the best way to manage roles in sql server in Azure VMs (multiple). We want separate logins for each users but it should be same on all Azure SQL VMs. We are currently using windows Cred. setting credentials on credential manager for that server IP but it is too slow.
01:06 MyTeaGotCold: I’ve only ever heard bad things said of MySQL, but it’s often above SQL Server in surveys. What am I missing?
02:27 FIN7: What are the top gotcha’s you have run into when migrating SQL onprem to Azure SQL Managed instance?
03:39 Unspoiled: is there any database level statistic you would recommend to monitor to know the impact or pressure a single database is generating. the goal being to understand how much pressure a single database is generating with multiple on a the server.
04:59 Poul J: Hi Brent. I was wondering if you are using a dedicated tools for investigating complex query plans?
05:08 SteveE: Hi Brent, I’m looking at your SQL ConstantCare® Population Report: Spring 2024 and can see a spike for Azure SQL DB in 2022 Q3 which then drops back to approximately the prior level in the next quarter. Are you able to offer an insight as to why this is please?
05:41 Kansas4444: Do you often see CLR function / procedure used and what impact it has on performances ?
06:08 Mattia Nocerino: Hi Brent! I’ve inherited a 3 node cluster (2 nodes FCI + 1 node AG) but it keeps going down for all the wrong reasons. I’ve never built nor managed a similar infrastracture. Could you point to some resources to get me started figuring out what’s going on! Hope you’re doing good!
07:50 BackupsAreImportant: What’s the drawback to implementing a backup strategy that only used the read-only node of an AG? You can take COPY_ONLY backups and log backups from there. I know the COPY_ONLY won’t affect the log chain but you can still do restores and apply logs. Seems wrong but why?
09:02 ThatSteveCena: With file system advancements on Windows, should we consider formatting MDF/LDF drives using ReFS or stick with NTFS?
09:56 Peter: Hi Brent, some devs have started slapping OPTION (NO_PERFORMANCE_SPOOL) on their queries. This is not something you covered in your courses. Is it a real solution to a genuine problem or a workaround to a problem they could or should be fixing a better way.
11:23 DATA cow: ALTER DATABASE DBname SET MEMORY_OPTIMIZED = ON; Version : 2019 what benefit we get by enabling ?I understand frequent data will loaded to memory. is that mean are we end up with run out of memory or memory or server pressure ?

Two Tweaks for Faster Backups with Ola Hallengren’s Scripts

Last Updated May 23, 2024

Brent Ozar

Backup and Recovery

Ola Hallengren’s free maintenance solution is widely used as a replacement for SQL Server maintenance plans. It’s a more powerful, flexible tool for backups, corruption checking, and index & statistics updates.

If you’re using it for backups, there are two quick, easy changes that can dramatically reduce your nightly job runtimes.

First, set @NumberOfFiles = 4.

SQL Server has internal bottlenecks for single-file backups, and striping your backups across multiple files removes that bottleneck. I’ve written about testing the number of files before, and in a perfect world you’d have the time to do that testing, but for starters, just try writing your backups across 4 files.

You don’t need separate drive targets or network targets – even writing the 4 files to the same volume usually produces pretty dramatic performance improvements for free.

I only do this on my user databases because the system databases are generally so small that it doesn’t matter. To set it up, just right-click on the Agent job for user database full backups, go into the step properties, and add a line for @NumberOfFiles = 4, like this:

This does mean you need all 4 files at restore time, and it means your restore scripts will be a little bit more complicated. But who cares? You’re not writing those scripts by hand in the year 2024, are you? You’re using sp_DatabaseRestore from the First Responder Kit, like a boss, just pointing it at a folder and letting it grab the most recent full, diff, and log scripts:

EXEC [dbo].[sp_DatabaseRestore]
    @Database = 'StackOverflow',
    @BackupPathFull = 'Z:\MSSQL\BACKUP\SQL2022\StackOverflow\FULL\',
    @BackupPathLog = 'Z:\MSSQL\BACKUP\SQL2022\StackOverflow\LOG\',
    @TestRestore = 1, 
    @RunCheckDB = 1, 
    @RunRecovery = 1, 
    @Execute = 'Y'

EXEC [dbo].[sp_DatabaseRestore]

@Database = 'StackOverflow',

@BackupPathFull = 'Z:\MSSQL\BACKUP\SQL2022\StackOverflow\FULL\',

@BackupPathLog = 'Z:\MSSQL\BACKUP\SQL2022\StackOverflow\LOG\',

@TestRestore = 1,

@RunCheckDB = 1,

@RunRecovery = 1,

@Execute = 'Y'

Good job. Moving on – eagle-eyed folks will notice another config change in that screenshot. This next one’s going to be a little more controversial, but hear me out.

Next, set @Verify = ‘N’.

By default, Ola’s backup jobs have @Verify = ‘Y’, which means after the backup command finishes, SQL Server reads the whole backup file to check to make sure it’s okay. This can massively extend your backup job times.

I’m not saying you shouldn’t verify your backups! I’m saying don’t verify them from the production box. Instead:

Write your backups to a network share, like a UNC path
Use sp_DatabaseRestore to verify them from a separate server, like DR
Bonus points for using sp_DatabaseRestore’s @RunCheckDB = 1 parameter, which is way better than @Verify = ‘Y’

This has a few important performance & reliability benefits:

Your production server’s nightly jobs finish faster
You really test the hell out of the backup, making sure it’s free of corruption
You offload that resource-intensive check work to a secondary server, not production
Hell, you don’t even have to pay licensing for that, because as long as you’ve got Software Assurance, you can do CHECKDB on secondaries for free, regardless of what SQL Server version you’re using

Presto: those two free, simple changes might cut your backup times by 1/3 or more. You’re welcome!

Who’s Hiring in the Microsoft Data Platform Community? June 2024 Edition

Last Updated April 24, 2024

Brent Ozar

Who's Hiring

Is your company hiring for a database position as of June 2024? 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.

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.