Blog

New Buying Option: Lifetime Access to Class Recordings

Last Updated June 21, 2022

Wanna buy my Recorded Class Season Pass: Fundamentals or Mastering, but you don’t want an annual subscription?

Now you can buy either of those bundles, pay one price, and get permanent access to the classes without worrying about recurring fees.

The price is the same as if you’d have had the subscription for 2 years.

So you can kinda think of it as a gamble: if you can finish the classes in 1 year, you’re best off buying a subscription, but then canceling before the year is over.

Or, if you’re busy, or if you think you want to revisit the content over and over, you can buy the Lifetime Access version without worrying about when your content will expire.

I will say this: every month, I get emails from students who beg for just one more month of access because they postponed all year long, and then realized their subscription was almost up. I know how it goes: work is a zoo!

Update: I’ve gotten a lot of questions about buying the Level 2 Bundle for a lifetime. That isn’t an option at this time – that bundle includes software like SQL ConstantCare and the Consultant Toolkit. That software & online service takes an ongoing investment to maintain, so we don’t do lifetime pricing on that – just the class recordings.

[Video] Office Hours: Palm Springs, California Edition

Last Updated June 1, 2022

Brent Ozar

Videos

On a road trip from San Diego to Vegas, I stopped in Palm Springs and answered your highly-upvoted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Introductions
00:45 i_use_lowercase_for_select: Hi Brent, just wanted to give you my support on your May 24th blog.
01:19 SQL Crooner: Who would win a SQL trivia contest between Brent Ozar, Pinal Dave, Paul Randal, and Erik Darling?
03:13 Brandon F: Does SQL 2022 have any “killer” performance features that make it a must have upgrade from SQL 2019?
05:40 YouGottaDoWhatYouGottaDo: Hi Brent, do you know (or do you have any idea) if MS use telemetry to do statistics on what SQL Server features are the most/less used and appreciated by users, in order to model or deprecate features in new versions?
06:49 Too Much Spare Time: How much time if any did you/do you spend running sp_helptext on system procs/views/functions, etc (or is it just me that likes to nose about in these…?) and wondering about the souls who wrote it all? Most interesting thing you’ve seen in a system object?
08:11 Jacob H: Hi Brent, you mentioned previously that you recommend turning off adaptive memory grants and scalar function in-lining in SQL Server 2019. Can you elaborate more on the problems you have found with those features?
09:29 Have you tried turning it off and on: We want to implement RCSI on a (10k batches/sec / 4.5TB / 7K session) server to help reduce blocking. I understand the additional tempdb requirements, however what QA do you suggest to make sure the output to queries wont be affected? Any books/videos to consult?
11:34 Keller: I have a query with inner join and 2 more columns in the WHERE clause.Join columns are not primary key.What is the best way to create index on both tables.
12:53 Edward Anil Joseph: The auto-numbering for identity column got inserted late. The next number was inserted before the actual number should be inserted. Example: 1 was inserted at 2022-05-31 00:00:01.009 and 2 was inserted at 2022-05-31 00:00:01.001 How is this possible? Something to do with disk?
15:30 Preben: In your training classes do you touch the subject of using apply instead of joining to force a specific query plan? If yes, what training.
18:53 Wrap-up and discussing my Porsche 944 Turbo

Office Hours Speed Round, Text Edition

Last Updated June 12, 2022

Brent Ozar

Production Database Administration

Not all of the questions y’all post at https://pollgab.com/room/brento are hard – some of ’em can be answered in just a line or two:

Q: Hany: Hello Brent, Who’s “Brent Ozar” in the Azure world?

Microsoft, and I wrote why here.

Q: Neutron Jack: Do you see any concerning TSQL deprecations in SQL 2022?

No, neither deprecated nor discontinued.

Q: MuleDonkey43: Have scalar functions improved much in SQL 2022?

No.

Q: Looking For An Anti-Histagram: Our databases are many years old and over time have a lot of auto generated stats on the tables. I’m sure many are not used, is there an easy way to tell?

Not accurately, no.

Q: Yakira: Is there good way to identify the top 10 SQL queries using the most worker threads?

What’s the problem you’re trying to solve? If you’re working on THREADPOOL, I have a training class on that.

Q: Youssef: Do you have any recommended books for learning the XML behind a SQL query plan?

The XML, no.

Q: Ronaldo: Since SQL monitoring is broken in latest SQL 2022 CTP, do you foresee Microsoft kicking current monitoring vendors to the curb and creating their own commercial monitoring software?

You mean Microsoft System Center?

Q: Juan: Do you have any tips / precautions when using a table as a queue with RCSI?

Not RCSI specifically, but read this.

Q: Mashrur: Hello Brent, I completed your column store index training recently and it blew my mind. On that training you mentioned you only scratch the surface of table partitioning. You please kind enough to provide some advance reference (blog, YouTube, Paid) regarding table partitioning.

Thanks, glad you liked it! Sure, here you go.

Q: Kagamine Len: What are your thoughts about requiring a formal retention policy for all new tables created on production? Must have, nice to have, optional?

That’s determined by your company’s compliance department.

Q: Enrique: What are your thoughts on the new Json enhancements for SQL 2022?

I think it’s dumb to spend $7,000 USD per CPU core to use SQL Server as a file server. Microsoft thinks it’s a great idea. Go figure.

Q: Kol Dar: Is unbalanced parallelism a mainstream problem that SQL DBA’s should be regularly on the lookout for?

No. Use the methodology I teach you in the first module of Mastering Server Tuning.

Q: Joe: Good day to you Brent! The hardware provider allocated and presented 196 GB to our SQL 2016 Standard VM. After reading your BOU Weekly Links, June 6th Edition email it got me thinking. Would there be any benefit to setting the Max Memory above the 128 GB limit?

I don’t have experience with that. If you regularly need over 128GB RAM, you probably need Enterprise.

Q: Shlumiel: Are include fields part of your 5 x 5 suggestion for NC indexes?

Yes.

Q: Dominique B: Hi Brent, wanted to start by “I’m a big fan of your work” but that may sound a bit cheesy 😉 I’m reading about the memory optimize tempdb and want to validate my understanding… This feature will make the old “rule” for the number of tempdb data file an old story isn’t it ?

No, and I explain why in this module of my Fundamentals of TempDB class.

Q: Eric Swiggum: There was a recent infrastructure outage, once resolved a SQL transactional replication was not applying changes, however we were not alerted. Our Undistributed Commands alert failed us too. Should I monitor “Not Running” statuses for these publications too?

Every now and then, on live webcasts, a question makes me lose my mind. I rant and rave and throw things. And I realize, as I write this, that I’ve never actually done that in text format.

So here it comes, Eric.

(Deep breath)

Should you monitor something that caused an outage?

Is that what you’re asking me?

Nah.

Why bother? Screw it. Who cares? It’s not like outages matter, or that it’s your job. Forget monitoring. Just let the outages keep happening, and let your phone keep ringing. Life is meaningless. Death comes for everyone. Eat Arby’s.

…

And that concludes another episode of Office Hours.

[Video] Office Hours: Vail Fireside Chat

Last Updated June 9, 2022

Brent Ozar

Videos

You posted and upvoted questions at https://pollgab.com/room/brento, and I sat down by the fire on a chilly evening in Vail, Colorado to answer ’em. I was driving cross-country, heading back from a road trip to see my mom.

Here’s what we covered:

00:00 Introductions
00:46 Ms. Cosmos: What are the top traits for the perfect DBA?
02:09 Boris Karloff: What is your top SQL audit horror story?
05:31 Midwest DBA: My friend is assisting with designing a new Data Warehouse solution for the BI team at his company. They really want HA and DR for their DW. I’ve never managed a DW that had HA/DR. What issues could occur if with using FCI for HA and Log shipping for DR, or AG for HA?
07:58 i_use_lowercase_for_select: Hi Brent, I just watched Bob Ward’s pitch of SQL2022. Does compat. level 160 really solve the parameter sniffing problem as suggested by Microsoft?
09:59 Jonas: Did your parents instill you with the desire to teach? Were they teachers?
11:22 StatisticsRules: Hi Brent! What are you most excited about in the coming release of SQL Server 2022? Thanks for your epic contributions to the community!
13:05 Jim: What is your opinion of DOP feedback in SQL 2022? Will this get shops over the finish line?
15:15 VegasDBA: How are you enjoying living in Las Vegas? Any off the beaten path places you would recommend? I’ve been here for 20+ years and love it.
16:17 Don’t Bother Andy: Is CU16 ready for prime time?
18:00 MergeItLikeItsHot: Hi Brent, we have some very sensitive tables that we work on and before we update any value on that table we backup the entire table for fast recovery, do you recommend using temporal tables instead?
19:26 SQLForTheWin: What is the best way to manage index deployments across multiple RDS instances? We have an application deployed in three regions and we need to deploy the same indexes across the instances.
20:30 TurnerBurn: I have a table that contains no primary key but has a non-unique clustered index containing 3 columns and, of course, the 4-byte clustering key SQL tacks on for uniqueness. Is there an advantage with this design or would an id column PK and non-clustered indexes be better?
21:39 Jason Burton: How do you stay current with such a variety of skillsets?
25:16 Beaker: Are DBA’s typically disliked by their co-workers? Should we care?

[Video] Office Hours in Moab, Utah

Last Updated May 25, 2022

Brent Ozar

Videos

On a cross-country trip, I stopped in Moab, Utah and answered your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Introductions
01:20 YourbiggestFan: Do you have any plans to write an Azure first version of the first responder kit (sp_blitzfirst and sp_bitzcache)which can get the metrics from query store and thus overcome the issue of dmv stats resets on Azure platform? On PAAS platforms need a reliable method to analyse perf.
03:06 T.C.: What are the most under utilized features for SQL monitoring software?
04:24 Drew Furgiuele: What’s the most common “complaint” you hear from DBAs these days? Is it a lack of funding for good hardware/higher cloud performance tiers? Training budgets? Developers?
07:04 Zee: Hi Brent, How can a ORM developer (hibernate, spring) get better at sending queries to databases. And how a DBA can convince ORM developers that a 15k characters query with 29k output every 30k times is not a very good thing and can be done better in a stored procedure.
10:01 Yousef: What are the top unforgiveable DBA mistakes?
11:25 Ryan: Where do you see the DBA profession going in the next 5-10-15 years?
15:35 Hany Helmy: Hello Brent, my friend is searching for your article on “Your first day as a DBA in a new company”, couldn’t find it can you help him?
16:05 Dave Dustin: Of all the database project CI/CD pipelines you’ve seen in your career, do you have any recommendations or tips?
17:16 Does Basically Anything: I know your standard recommendation for storing BlitzFirst outputs are every 15 minutes, retaining for 7 days. What would be the maximum you’d recommend if a friend were interested in retaining more, or collecting all the data at more frequent intervals? (Using ALL output tables)
17:52 Harika: Do you have have an opinion for when the SQL Log backup job should be temporarily stopped on a busy SQL2019 OLTP server (i.e. index maintenance, full backups, diff backups, etc)?
18:52 Gülnaz: What post update sanity checks do you like to perform after applying a SQL cumulative update?
19:58 Midwest DBA: Have you ever worked with a Very large company? Target, Walmart, GMC, etc. Are their DBA team’s full of highly skilled DBAs, or are they much like everywhere else ,i.e. one expert and few regular folks?
22:11 Pessel: Who is the community’s “Brent Ozar” for all things SQL Security related?
22:51 NullPointer: My friend wants to know the best way to get some type of high availability (willing to have 30m-1hr of downtime). They have 1 database per client (sitting at 50 dbs but will grow) and use std edition with no dedicated DBAs (just devs). Suggestions (always on, log shipping, etc.)?
23:20 Alec Roques: Upon checking a table with sp_BlitzIndex, do you ever drop indexes with a low amount of reads to a high amount of writes (for example, Reads: 80 (56 seek 24 scan) Writes: 670,011)? How do you make that determination? Or will you wait until you see locking and blocking problems?
24:05 Preben: Are you open to visit belgium?
25:06 Latka: How do you determine the optimal time interval for backing up the transaction log, (30, 15, 10, 5 minutes, etc) if transaction log size / growth is primary concern ?
26:55 MancDBA: Non-SQL question – You have talked previously about some of your awesome cars (Helmut, Ferrari etc). What do you use as a daily driver? Cheers!

SQL Server 2022 Tells You Why A Query Can’t Go Parallel.

Last Updated June 7, 2022

Brent Ozar

Execution Plans, SQL Server 2022

Until 2022, when a query couldn’t go parallel, all we got was a really cryptic note in the execution plan properties saying NonParallelPlanReason = CouldNotGenerateValidParallelPlan.

But starting with SQL Server 2022, even when I’m running under older compatibility levels:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */
GO
DECLARE @TableVariable TABLE(Total BIGINT);
INSERT INTO @TableVariable (Total)
	SELECT COUNT(*) FROM dbo.Votes;

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 140; /* 2017 */

DECLARE @TableVariable TABLE(Total BIGINT);

INSERT INTO @TableVariable (Total)

SELECT COUNT(*) FROM dbo.Votes;

The execution plan gives me way more details:

Awww yeah! Here’s another example using a scalar user-defined function:

CREATE OR ALTER FUNCTION dbo.RightHereRightNow ( @Meaningless INT )
RETURNS DATETIME2
    WITH RETURNS NULL ON NULL INPUT,
         SCHEMABINDING
AS
    BEGIN
        DECLARE @Current DATETIME2;
        SELECT @Current = GETDATE();
        RETURN @Current;
    END;
GO
SELECT COUNT(*) FROM dbo.Votes
	WHERE dbo.RightHereRightNow(0) > '1900-01-01';

CREATE OR ALTER FUNCTION dbo.RightHereRightNow ( @Meaningless INT )

RETURNS DATETIME2

WITH RETURNS NULL ON NULL INPUT,

SCHEMABINDING

BEGIN

DECLARE @Current DATETIME2;

SELECT @Current = GETDATE();

RETURN @Current;

END;

SELECT COUNT(*) FROM dbo.Votes

WHERE dbo.RightHereRightNow(0) > '1900-01-01';

The actual plan now clearly explains that our query can’t go parallel because of the scalar function:

If our query uses two parallelism blockers at once:

DECLARE @TableVariable TABLE(Total BIGINT);
INSERT INTO @TableVariable (Total)
	SELECT COUNT(*) FROM dbo.Votes
	WHERE dbo.RightHereRightNow(0) > '1900-01-01';

DECLARE @TableVariable TABLE(Total BIGINT);

INSERT INTO @TableVariable (Total)

SELECT COUNT(*) FROM dbo.Votes

WHERE dbo.RightHereRightNow(0) > '1900-01-01';

The actual plan only shows one of the two reasons:

And the XML doesn’t show both reasons, either.

Still, that’s a really small complaint – at least SQL Server 2022 shows ANY of the reasons, which starts you down the road of performance tuning this query. I’ll take it!

SQL Server 2022 doesn’t remove those parallelism blockers, though.

If I change the compatibility level to 160 (2022), the table variable still goes single-threaded, as does the scalar function:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160; /* 2022 */
GO
SELECT COUNT(*) FROM dbo.Votes
	WHERE dbo.RightHereRightNow(0) > '1900-01-01';

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 160; /* 2022 */

SELECT COUNT(*) FROM dbo.Votes

WHERE dbo.RightHereRightNow(0) > '1900-01-01';

The execution plan still plays the sad trombone:

Because it doesn’t look like SQL Server 2022 is fixing the rampant issues with scalar function inlining.

[Video] Office Hours: Professional Development Edition

Last Updated May 31, 2022

Brent Ozar

Videos

I spent an entire video just talking about 3 questions y’all posted to https://pollgab.com/room/brento because these were pretty big-picture and important.

I know it sounds goofy and sentimental, but I genuinely care about y’all’s careers, and I want you to work as little as practical in order to spend as much time with your loved ones as possible. So here we go:

What we covered:

Kinneret: When are work communications appropriate for a chat channel such as Slack? When should those communications take place via direct audible communications (phone, webex, etc)?
WhatsUpDocs: Hi Brent, have you ever needed to look at business documentation (check business rules/logic) when consulting or as an employee, but it was severely lacking? Recently joined a different team in work and trying to find simple answers to questions is an uphill struggle…
Roy: Hi Brent, How did you manged to upskill yourself early in your career with a busy full time job? What recommendations will you give to somebody early in his DBA career?

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

Last Updated August 1, 2022

Brent Ozar

Who's Hiring

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

Office Hours Speed Round: Text Edition

Last Updated May 31, 2022

Brent Ozar

Production Database Administration

Some questions that come in at https://pollgab.com/room/brento have such straightforward answers that I don’t need to cover them on video. It’s time for a rapid-fire speed round:

Q: Uncle Buck: Is there a good way to tell if a given include column for a NC index is being used at all?

No.

Q: Steve E: Hi Brent, In the past you have shared links to a “reading list” tool you used to bookmark various online articles and share them with others,what was the tool please? I’d like to bookmark the various articles I’ve read over the years and be able to easily share those with others

For bookmarks I use Pinboard, and here are mine. For RSS feeds and newsletters I use Feedly, and here are my shared items.

Q: FrankieG: When faced with hundreds, if not thousands of NonSargable predicates over a wide range of solutions/products that have been in production for years in a huge base of legacy code how to begin to “fix” the problem?

Use sp_BlitzFirst @SinceStartup = 1 to find your server’s top wait types, and use sp_BlitzCache to find the top queries causing those wait types. I explain more about that process in my How I Use the First Responder Kit class.

Q: Faruk: Is it a fair expectation for employers to expect DBA’s to also write reports (SSRS, Power BI, etc)?

Generally, development and administration are separate job roles.

Q: Luis: Any performance gotcha’s to watch out for when the query plan is using the Filter operator to satisfy a non-sargable search predicate?

If you understand enough to write the question, then you already understand the answer. Performance will be bad.

Q: Last Action Hero: What are your thougths about contained databases?

I explained the problems with ’em here in 2009: part 1, part 2, and part 3. As I predicted, most DBAs have still never seen these in the wild, nor do I expect them to in the near term.

Q: Jose Luis: I need to migrate my existing DWH in SQL2012 to a new OnPremises SQL 2019. Should I consider AlwaysON for it? maybe SQL Failover Cluster? Or just a BIG Standalone Server? Most of my DBs are in Single Recovery Model.

For personalized architecture planning, hire me for consulting.

Q: Reed Richards: What is the best resource for extended events training?

Start with Jonathan Kehayias’ series 31 Days of Extended Events.

Q: Punxsutawney Phil: I thought that when you refresh a database on a nightly schedule for reporting/production use, that’s considered a “groundhog day” method and should be avoided. Yet, I’ve seen you twice in recent months recommend that as a solution to refresh production reporting databases.

Groundhog Day refers to the practice of reloading data via logged methods: inserts, updates, deletes, SSIS, etc. Restoring a backup is not a logged method.

Q: Leon Spangenberg: I keep seeing trace flag 3604 being turned on and off in my sql log. Why would someone do this?

That is detailed here.

Q: Max_Null :A very small table in Azure SQL used to keep track of locking in the application (~15 columns, no triggers) and has no more than 3 rows at any given time. Other than stale stats and blocking, is there any other reason that this table would be slow for a simple SELECT?

If you’re trying to track locks in a table, you’re going to hit locking problems, period, full stop, end of story. Bad design idea. Do something different.

Q: Alan: Do you think cheap cloud storage, like s3, will eventually replace old fashioned tape libraries and off-site backups?

For many of my clients, it already has.

Q: FrankieG: Hi Brent, can you talk about and explain why upgrading to the rewritten cardinality estimator (i.e. upgrading SQL Server from v2012 to v2016) causes serious performance degradation and the only way around it (that I’m aware of) is to force using the legacy estimator in settings?

Yes, I cover that and the alternatives in my Mastering Query Tuning and Mastering Parameter Sniffing classes.

Q: Debbie Downer: Does Stack Overflow db have more downvotes than upvotes? If so, why?

You can download the database here for free to find out.

Q: Stan Sitwell: Is PostgreSQL consulting as opportune and lucrative as SQL Server consulting?

I have no idea – I don’t consult on PostgreSQL.

Q: Pina: Should I drop temp tables at the end of my stored procedures? What if there is one stored procedure that calls a bunch of other stored procedures, each of which has several to many potentially large temp tables? Is there a performance gain to dropping them? A performance loss?

I cover this in my Fundamentals of TempDB class.

Q: Ravi Pratap Singh: what are the books need to cover for good SQL understanding

Check out this list.

Q: Does Basically Anything: Hi Brent! When looking to tweak MAXDOP from a “sane default” to the best setting for my workload, what metrics should I pay most attention to as I try different MAXDOP settings to test the performance difference?

I cover that in the parallelism module of the Mastering Server Tuning class.

Q: DGW in OKC: Have you ever used encrypted connections (like SSL) to a SQL instance? Is there ever a reason one might be required to do this?

No, but I’ve heard of security teams requiring it in some cases.

Q: Need4Speed: A friend asked if there was a way to bring a detached database back online if you are missing one of the three data files.

Detaching a database is dumb. Deleting a database is dumb. If someone did *two* dumb things to a database, I’d say you know what, I can’t trust those files. Let’s restore from backup. If that person tried to fight me, I’d say, “You’re the one who did two dumb things already – how’s about you step away from the keyboard and cool off for a while, and let the pros take over?”

Q: Kirk Saunders: Are there situations where you would use a RIGHT OUTER JOIN? I have found I can put that table higher in the JOIN structure and LEFT OUTER instead. I just don’t want to miss out on a solution option if there is a real strong use case for it.

Me personally, I’ve never used it.

Q: Have you tried turning it off and on: We want to implement RCSI on a (10k batches/sec / 4.5TB / 7K session) server to help reduce blocking. What QA do you suggest, and any books/videos to consult?

Check out the isolation levels module in my Mastering Server Tuning class.

Q: KIRBY W BURKHOLDER: While watching a recording of “Mastering Query Tuning” recently, I remember you mentioning a problem the CE has with “Ford” and “Mustang”. My friend has the problem but all Fords are Mustangs and all Mustangs are Fords. Vendor supplied code that he can’t change. Any suggestion

Leave a comment on the training class module. There’s space for longer questions in there, including PasteThePlan options to include the execution plan, so I can see the exact problem you’re talking about.

Q: George: What is your favorite way to measure storage speed in Azure SQL?

The fast/easy storage test is CrystalDiskMark. It’s not completely definitive – it’s just a hot-or-not test – but in most cases, the storage is nooooot hot, so I don’t need a definitive test.

Q: Neil: We always used default instances. Planning on a multi-instance server (in test) to host multiple web environments with copies of the same db’s on each instance. What are some ‘gotchas’ to look out for? I’d probably give each instance its own data/log folder names, anything else?

The term you’re looking for is instance stacking.

Q: Doug E: Do you ever bother with reconciling AWS / Azure bills to actual resource usage?

Yes, we’ve done it for SQL ConstantCare’s own back end during performance tuning.

Q: SQL Serenader: Hi Brent, We have seen an uptick in dropped connections. Do you think using the deprecated SQL Native Client 11 driver, instead of the more connection resilient ODBC 17 or OLEDB 18 drivers could have anything to do with our connection issues?

I have no idea.

Q: SeeCoolGuy: what is your opinion if you find your colleague forcing the engine join hint to use a hash join vs letting the query engine find it’s own join criteria?

That colleague might have attended my Mastering Parameter Sniffing class where I cover hint usage.

Q: Philip: I’m getting a warning against an Azure SQL DB saying “Non-parameterized queries are causing performance issues” with Azure wanting to turn on ALTER DATABASE Name SET PARAMETERIZATION FORCED. Are you able to talk about this a little, and how you would go about investigating?

I assume you’re getting that warning in sp_BlitzCache or sp_Blitz. Copy the URL into your browser and read that. I’ve written extensively there on how to fix it.

Q: Enzo: Hi Brent, As you like fast cars, will you be attending the first F1 race in your home town next year (Las Vegas 2023)?

Yes, if I can get good tickets.

Q: Roadtripping fool.: Any chance you can make these into a podcast like your old office hours? I used to make my family listen to them on road trips. Ah memories.

No, it took time and cost money.

Q: Michael Devor: Hi Brent, My friend would like to know if there are any special considerations or concerns when tuning indexes on a partitioned table?

Yes. Have your friend attend my Mastering Index Tuning class.

Q: SQL Crooner: What is your opinion of the Parquet file format?

I have no opinion. Never used it, never opened it. Nothing against it.

Q: Festus: What percent of the time do you see customer tables where the primary key / clustered index are on same vs different columns?

Way, way, way less than 1%.

Q: prasad: What r the steps we should take to learn sql in depth?

Use it, help other people solve their problems with it, and then teach it.

Q: Eric S: How many years before our AI overlords displace human DBA’s?

OtterTune is already working towards that for MySQL and PostgreSQL, and I’ll be excitedly watching their progress. I think it’s a cool problem. Given Microsoft’s lack of progress on the automatic indexing front, and given how little of that is coming in SQL Server 2022, I think our jobs are still safe for a long, long time.

Q: SQL_Ninja: Hi Brent, what is your thoughts about SQL server vulnerability assessment functionality or would you recommend any other third party software for similar assessment ?

I don’t get the chance to try every tool out there, so I’m not really qualified to give a good review.

Q: cloudy: Hi Brent, we are currently use on-prem sql server for dwh & reporting, my company is starting to run some workload on aws, what’s your take on migration to Amazon Aurora (we can change the application to support it)?

What’s the problem you’re trying to solve?

Q: Anatoli: What is the best cloud database technology / brand for a shop that has plenty of developers but doesn’t want a DBA?

Use the one most of your developers are most familiar with. That’ll result in faster delivery to customers.

Q: Joe: Hi Brent, Under what circumstances would you set Min SQL Memory to anything other than zero?

None, because I hate instance stacking.

Q: Maksim: What is your opinion of disabling the guest and SA users in SQL Server?

I don’t really care. My bigger issue is usually shared passwords, like app passwords, and disabling accounts doesn’t fix that.

Q: Haydar: Do you foresee any DB company acquisition / buyouts on the horizon? What company takeover is the odd’s on favorite?

I don’t track the business side of the industry.

Q: Gary: Hi Brent. What’s your take on the number of instances one Production DBA should be responsible for? Automation helps and it’s rare everything breaks or needs service at the same time, but it can be a burden for one employee to manage 200+ instances. Thanks!

See this post.

Q: Joel: Do you ever look at sys.dm_os_schedulers for performance related troubleshooting?

Yes. We talk about it in my Mastering Server Tuning class.

Q: ?akir: What is your favorite tool / technique for monitoring failed SQL agent jobs?

Alerts.

Q: Gözde: How often should we back up SQL agent job definitions?

Back up your system databases daily. Jobs are stored in the msdb database.

Q: Ronaldo: Is tech documentation reading a lost art? If so, why?

Yes. Words are great for searchability, but videos are often easier for beginners to approach. Thus the rise in just-in-time learning: people searching for something, finding a 5-minute YouTube video on it, watching it while doing the thing, and then discarding the video, and never really learning the technique. I’m fine with that. It works for beginners.

Q: Leon Spangenberg: Hello Brent. I am inserting row into an existing table. However when ever I do the insert with a predefined set of filter the insert just does not work. I don’t get an error its just like sql sits there and does nothing. Any advice?

Use sp_WhoIsActive to see what it’s waiting on.

Q: Brent: Why are so many of these answers pointing people towards a training class?

Because the answers are 30-60 minutes long, and that’s why I teach classes. I love these questions, don’t get me wrong – but I love them so much that I’ve built entire presentations to do justice to their answers.

I’m teaching Mastering Parameter Sniffing at the PASS Summit!

Last Updated May 25, 2022

Brent Ozar

#SQLPass

At the PASS Data Community Summit in Seattle on November 12th, I’m teaching a one-day pre-conference workshop.

This year, the Summit is a hybrid event: you can either attend in-person in Seattle, or online via live streaming. If you attend online, PASS is recording the live stream, and you’ll be able to re-watch it during the week of the conference. This is super helpful for folks who have an emergency at work, have to drop off the call, and want to be able to catch up on what they’ve missed.

Here’s the abstract for the workshop I’m teaching on Mastering Parameter Sniffing:

You’re a database developer or DBA with at least 5 years experience performance tuning queries and indexes.

You already know you have parameter sniffing issues, and now you need to figure out how to fix it. In this one-day class, you’ll learn how to reduce the blast radius with index changes, query tuning, and database-level settings. You’ll also see firsthand how SQL Server 2017, 2019, and 2022 try to reduce it with adaptive joins, adaptive memory grants, automatic tuning, and caching multiple plans.

This course is 100% demos: the only slides are the introduction and recap. The rest of the time, I’ll be working live in SQL Server Management Studio and SQL Server 2022. You can even follow along in your laptop if you bring SQL Server 2017 or newer and the 50GB Stack Overflow 2013 database.

We’re going to be moving quickly and covering a lot of ground, and I know it can be tough to keep up. That’s why attendees will also get one year access to the full 3-day Mastering Parameter Sniffing recordings, too! That’s a $395 value, and it’s free with your workshop admission. (You’ll get the access in class.)

Registration is open now with early bird pricing. See you there!

Breaking News: SQL 2019 CU16 Changes Backup Formats, Can Break Log Shipping.

Last Updated May 25, 2022

Brent Ozar

Backup and Recovery, Breaking News

The newest Cumulative Update for SQL Server 2019 has this gem in the release notes:

It’s not a known issue, it’s an improvement

This is a massive problem if:

You have databases encrypted with TDE
You take backups WITH COMPRESSION (which has a history of bugs already), or you have the server’s default compression option turned on
You’re doing log shipping to other 2019 servers
You patch the primary to CU16 before you patch the secondaries

You might say, “Well, you should always patch the secondaries first” – but in some shops, here’s how they approach patching:

Patch the secondary (Server2)
Fail over to the patched secondary (Server2) but do not patch the former primary (Server1) just yet, because you’re worried about something going wrong in the patching
Wait a few hours or days before patching Server1

At that point, the unpatched Server1 is the secondary – but it can no longer restore backups because of this “feature” of CU16. (That’s exactly how I discovered this issue – a client’s log shipping was broken when they had problems with CU16 and tried to fail back to Server1 – but they couldn’t. CU16 is a one-way trip if you’re using log shipping.)

The “feature” is detailed in this KB article, which says:

In my client’s case, to get back to the CU15 servers quickly, we had to take uncompressed full & log backups from the CU16 primary over to the CU15 server, and were then able to go live on CU15 again. (I wasn’t involved in troubleshooting the issues they had with CU16 that drove them to fall back to CU15.)

Designing a Data Model for Gender and Sexuality (Oh And Also, I’m Pansexual)

Last Updated June 17, 2022

Brent Ozar

Personal

382

These days, BrentOzar.com mostly focuses on the Microsoft data platform. However, you and I have a relationship, dear reader, so I just need to talk about some personal stuff today.

Lemme get two things out of the way first:

I’m pansexual, and
From a data modeling perspective, that’s different from bisexuality

I totally understand if you’re breathing into a paper bag right now, freaked out because that’s a lot to take in. You can close this blog post and come back to it later, or even just close it, hahaha – I understand that it’s a challenging topic.

But if you wanna learn more about me and data modeling, let’s dig in.

Let’s design a data model
for gender and sexuality.

Most of us are aware of two genders: male and female. In terms of data modeling, we might think of it as a bit column, IsFemale. 0 would be male, 1 would be female.

Sure sure, you could do IsMale, and have 0 be female, 1 be male. I’m fine with either definition – I swing both ways. (Brace yourself – the jokes only get worse.) So:

CREATE TABLE dbo.Bloggers(
    BloggerId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    BloggerName NVARCHAR(50),
    IsFemale BIT
);
INSERT INTO dbo.Bloggers(BloggerName)
    VALUES(N'Brent Ozar', 0);

CREATE TABLE dbo.Bloggers(

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

BloggerName NVARCHAR(50),

IsFemale BIT

);

INSERT INTO dbo.Bloggers(BloggerName)

VALUES(N'Brent Ozar', 0);

Then if gender is a bit column, then defining sexuality is pretty easy too. We can’t do it with just a bit flag, but here are the four possibilities:

Heterosexual: someone is attracted to the “opposite” gender – for example, if you’re a man, you’re attracted to women
Homosexual: attracted to the “same” gender – for example, if you’re a man, you’re attracted to other men
Bisexual: attracted to both men and women
Asexual: not attracted to either (listen, there are days when I want y’all to all die in a fire, especially the days when I read the comments)

We might design it as:

CREATE TABLE dbo.SexualPreferences (
    SexualPreferenceId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    SexualPreferenceName NVARCHAR(50)
);
INSERT INTO dbo.SexualPreferences(SexualPreferenceName)
VALUES (N'Heterosexual'),
       (N'Homosexual'),
       (N'Bisexual'),
       (N'Asexual');

CREATE TABLE dbo.SexualPreferences (

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

SexualPreferenceName NVARCHAR(50)

);

INSERT INTO dbo.SexualPreferences(SexualPreferenceName)

VALUES (N'Heterosexual'),

(N'Homosexual'),

(N'Bisexual'),

(N'Asexual');

Most data modelers would stop there, and I would understand why. That’s all most of us have been exposed to. If you’re nodding along, that’s fine – you would define me as IsFemale = 0, SexualPreference = Bisexual.

I would totally understand if you said, “Ugh, Brent, this is way too much information,” and you closed the blog post here. I get it. This is an uncomfortable topic. But if you wanna learn about why bit columns don’t work for genders, then we need to keep going.

Gender isn’t really a bit flag.

In about 0.02% to 0.05% of births, the person is born with ambiguous genitals.

I know some readers are gonna rage-quit right there, and I understand. It’s a touchy topic. (No pun intended. (Okay, maybe the pun was intended.)) You’re welcome to do your own research on the intersex topic.

But if we agree that there are any births where it isn’t black-and-white which gender someone is at birth, that opens up a data modeling problem. A simple bit column isn’t going to be enough, because there’s more than just male and female. Gender is non-binary.

Furthermore, as people age, they may find that the bit value they were assigned at birth is wrong. The truth is, your biological sex isn’t carved in stone, but a living system with the potential for change. (I stole that sentence from this Scientific American piece, HT Andy Mallon, and I have so much respect for transgender folks who have to deal with this struggle.)

As a data modeler, it’s not my job to lay out every possible option for gender. I just need to be aware that a bit column isn’t going to cut it, and I’m going to need a lookup table along the lines of:

CREATE TABLE dbo.Gender(
  GenderId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  GenderName NVARCHAR(50)
);

CREATE TABLE dbo.Gender(

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

GenderName NVARCHAR(50)

);

For the purposes of this blog post, I don’t give a damn what’s in the table. I don’t even care if you define it as:

INSERT INTO dbo.Gender(GenderName)
VALUES (N'Female'),
       (N'Male'),
       (N'Grey Area');

INSERT INTO dbo.Gender(GenderName)

VALUES (N'Female'),

(N'Male'),

(N'Grey Area');

Even if those were the only 3 options, that’s clear enough that a bit flag isn’t going to cut it.

So if a bit flag isn’t going to cut it, then we’re gonna need more than 4 options for sexual preferences.

Our 4 starting sexual preferences
are still a thing, but there are more.

All of these are still valid:

Heterosexual can still mean men who are attracted to women, and women who are attracted to men. That’s fine.
Homosexual can still mean men who are attracted to men, and women who are attracted to women.
Asexual can still mean people who aren’t sexually attracted to anyone else.
Bisexual can still mean men attracted to men & women, and women attracted to women & men.

But we’re gonna need more definitions, too, because people who are bisexual aren’t necessarily attracted to people whose gender is in the grey area. We need something like bisexual, but that also means any gender is okay, not just male or female.

That’s pansexual: people who can be sexually attracted to anyone else regardless of their gender – not just men or women, but people who are in the grey area. (I know, before you read this post, you thought bisexual people were open to a lot! Turns out there’s a world in which bisexual people are considered picky, hahaha.)

There are more sexual preferences than that, too. My goal in this post isn’t to define every possible sexual preference – it’s just to remind you that gender is not a bit flag, and the table for sexual preferences has more rows than you might consider at first glance.

I’m pansexual:
gender isn’t a factor for me.

That sounds like I have really low standards, hahaha, like I’m attracted to everything that moves. That couldn’t be further from the truth: I am very, very picky, but I just don’t consider gender as part of the criteria that I look for. I’ve met sexy women, men, transgender folks, and nonbinary folks.

Right now, I’m in a relationship with someone non-binary who was born a man, but is comfortable presenting as either a woman or a man, depending on what they feel like doing that day. Most of the time, they present as a woman.

You might be asking yourself, “Wait, Brent – is that your wife?” No, my wife and I got divorced when we returned from Iceland in 2021.

I’m blogging about my sexuality
for 3 reasons.

First, I wanna be here for folks who are surprised by this. Right now in politics, there’s a lot of divisiveness, a lot of us-versus-them. I know some of my readers are going to be shocked that I’m one of “them”, the sexual deviants who have some kind of agenda. It’s okay – I understand if you’re shocked, and I’m here if you want to talk about it. You can ask me anything you want publicly or privately, and not feel dumb or guilty.

Second, I wanna be here for folks who feel seen. Right now, non-binary, transgender, and pansexual people are reading this blog post going, “OMG, I’m not alone in this industry.” If there’s anything I can help you with, please don’t hesitate to reach out.

Third, because I can. I’m blessed enough to be at the point in my career where I can talk publicly about this stuff without fear of retribution. Oh, retribution is going to happen, for sure – there are readers right now going, “Screw this guy, I’m unsubscribing from this craziness.” That’s fine. I will survive.

But don’t worry,
this blog is still about data.

I don’t want you to think that I’m constantly going to be pushing this in your face, dear reader. The blog is still going to be about databases, and my social media feeds are still going to be about cars and travel and food.

No, I’m not going to hit on you at a conference or a user group. I’ve never done that before, and I’m not about to start now. If you’re worried that I’m looking at you with some kind of ulterior motive, relax. You’re not my type. I’m specifically talking to you, not the other readers here.

The eagle-eyed will notice that I try to use gender-neutral names and terminologies as often as I can. I try to sneak in little fun stories and lessons where it makes sense, and I’m sure I’ll start working in more subtle stuff in demos.

But this blog isn’t going to become some kind of pansexual activism point. The biggest reason is simple: the pansexual pride flag is ugly.

C’mon, seriously, it’s as if someone said, “These pansexuals say they’re okay with anything? Well, let’s see how they feel about THIS, ha ha ho ho!” Ugh.

I know this is complex.
Feel free to ask me questions.

If you want to know more about me, gender in general, or sexuality in general, you’re welcome to post comments here or email me directly at brento@brentozar.com. You can also check social media today because May 24th is #PansexualVisibilityDay.

I know this topic is very different than the ones we usually discuss here. I will make sure the comments section is a place for honest discussion and learning, free from trolls or abuse.

Update: wanna know what
kinds of messages I got for this?

After publishing this blog post, here’s an example message I got on LinkedIn:

Part of me is honored that y’all think I’m professional.

The other part of me is completely unprofessional, and is surprised that it took you so long to find out.

As a reminder: BrentOzar.com is my blog, not yours. I’m here for a good time. If you’re not here for that, it’s best if you unsubscribe.

Never been to a SQL Server training class? Let’s fix that.

Last Updated May 22, 2022

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 online conferences, 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. Next month, you’ll spend a week with me learning about indexes, query tuning, columnstore, how I use the First Responder Kit, and more. I’m teaching it two weeks in a row:

June 27-July 1: Fundamentals Week, US-friendly times:

Monday: How I Use the First Responder Kit – Add to Calendar
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
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

Bonus: live Fundamentals of TempDB on June 9 & 10 – if you buy your Fundamentals Week ticket quickly, I’ll let you sneak into this class too!

June 9: Fundamentals of TempDB EU-friendly times – Add to Calendar
June 10: Fundamentals of TempDB US-friendly times – Add to Calendar

Registration is open now. If you can’t make the live classes, like if you have an emergency at work, there’s a new option during check-out – you can add on lifetime access to the recordings, too. See you in class!

[Video] Office Hours: Cabo Balcony Edition

Last Updated May 15, 2022

Brent Ozar

Videos

Post your questions at https://pollgab.com/room/brento and upvote the questions you’d like to see me discuss.

The light balance on this video is all over the place, getting brighter and darker constantly, so you might wanna leave this one playing in the background and just listen to it rather than watch.

Here’s what we covered:

00:00 Introductions
00:32 SQL Crooner: What are the tell-tale signs that your SQL Server has been granted too much memory and the OS not enough?
02:14 Gülnaz: Is ‘SELECT *’ ok or discouraged when used with ‘IF NOT EXISTS…’ ?
03:27 Shingen Yashida: Is table partitioning largely unnecessary since modern SANs can move data to hot / cold storage tiers?
04:14 sp_azuresql: Hi Brent – What is the best cut-over strategy in PROD migration scenarios from SQL Server AG to Azure SQL in terms of blocking the incoming traffic (if any) like disabling logins etc?
05:53 TurnerBurn: Hi Brent. My company moved a database from a physical SQL 2016 SE cluster to a VM; same SQL ver. VM has fewer cores (24 vs 32) but processing power on the VM is better & memory is the same. I now see many, more deadlocks reported from sp_BlitzLock on the VM vs the physical box.
07:38 Noam: On a scale of 1-10, how would you rate the online Microsoft SQL Server Documentation and the online PostgreSQL documentation?
09:20 Piotr Rasputin: What is your opinion of DP-900 Azure Data Fundamentals certification?
10:42 Q-Ent: Hi Brent, is there any scenario where enabling RCSI on a database that uses the default isolation level to turn your data into trash?
11:30 Prohiller: Hi, can we somehow predict % decrease of overall CPU usage of a server when optimizing a query? Given that we know difference of CPU time of current and optimized query and number of executions per hour/day? I’m looking for additional KPIs when selling optimization to Business.
12:37 MyFriendtheDBA: My friend has a table with 450,000,000 rows on a production server. They want to know the lease disruptive way to index this table.
15:10 Yoshiya: What is your opinion of Azure Synapse? Does it compete well with Databricks and Snowflake?
15:18 SQL Crooner: What are your top SQL best practices that you wish more clients would follow?

[Video] Office Hours: Cabo Pool Edition

Last Updated May 15, 2022

Brent Ozar

Videos

You posted SQL Server questions and upvoted the ones you’d like to see at https://pollgab.com/room/brento, and before the Cabo Clasico finals start for the day, I sat by the pool to talk through ’em:

Here’s what we covered:

00:00 Introductions
00:53 Neil: Does putting up a giant monitor on the wall with SQL Server info like CPU %, Disk activity, blocked sessions, top queries, etc, so non-DBA’s and Executives can see the server status sound like a good idea or is that just inviting trouble?
03:07 Omer: What are the possible causes for sp_whoisactive to show a SPID with a long running INSERT statement (non blocked) but the SPID is in a sleeping state?
03:42 Theo: Does the order of include columns ever matter for a non-clustered index?
03:53 Uncle Leo: What are the pros/cons of using third party SQL backup software over native SQL backup?
05:05 Mehdi: Hi Brent! Auto-update stats is enabled on a database. Does the database need up-to-date statistics with the maintenance plan?
06:07 DBA_Mufasa: Hi Brent! What’s your best approach for tracking tables usage in SQL databases, in order to find tables that haven’t been touched (for eg. no select, updates, or inserts ) for a a defined amount of time. Considering that table statistics get cleared after server restart.
07:49 default blame acceptor: Hi Brent, could you advise a KPI that I can show not-technical folks to convince them that SQL is not a root cause of the application slowness (when it seems that it is true!) and they should look for a solution somewhere else instead of convincing me to restart SQL’s VM…
09:03 Yousef: How do you determine the optimal auto growth size for a given SQL data file running on NVME SAN?
09:47 Ted Striker: What are the pros/cons of running sp_blitzfirst for longer periods of time (@Seconds = 300)? Any maximum value of @Seconds we should stay under?
10:57 Wilma: What is the optimal monitor size / count / configuration for viewing large query plans in SSMS?
11:25 Alain: Hi Brent, what are the advantages of using VMWare stretched cluster instead of AlwaysOn? Thank you
12:56 TeeJay: We’re expecting to move from on-prem (2017 vintage) to azure VMs soon. What should I benchmark on our old servers that’s useful to assure us that the virtual hardware in azure is (at least) not going to leave us with worse performance than we currently have?
13:56 Ain’t Lyin’: Got a tuning problem with a query inner joining some large tables to a table with 0 rows. Optimizer estimates 1 row anyway and does a lot of work before realizing there are no rows to return. I can’t change the query, how do I get the optimizer to figure this out early?
16:29 Hamid: What is the fastest way to split a comma delimited string in in TSQL 2019 / 2014Compat level DB?
17:56 Kfir: What is the maximum size key/include column you would consider adding to a non-clustered index?
18:58 Thomas Franz: How much memory should I reserver for OS / other stuff when I configure the Max Memory settings for servers with 1 or 2 TB (!) RAM. Following the common recommendations I would end with ~256 GB for OS / 1.792 for SQL which seems a bit too much (most servers have much less RAM).
20:55 CKI: Management wants to create a copy of the production database for reporting purposes. The new “reporting” copy should be refreshed nightly with production data. Production database is on AWS Web Edition 45GB. What is easiest way to do it? Thank you very much!
22:04 Gerald Krobath: Is it recommended to install CU updates for MSSQL 2016/2019 automatically via the regular updates or should this always be done manually?
23:47 Sir Galahad: Has OPTION (FAST N) query hint ever got you across the finish line?
23:59 Buck: Have you noticed the annoying issue in SSMS 18.11.1 where it does not maintain your query plan scroll position when you switch off that tab over to another tab then back to the original tab with the originally displayed plan? Is this worth submitting to feedback.azure.com?
27:27 Recap, discussing skimboarding

I’m Speaking at the PASS Data Community Summit This Year!

Last Updated May 15, 2022

Brent Ozar

#SQLPass

For years, the biggest conference in the Microsoft data platform community has been the PASS Summit.

This November 15-18, it’s coming back to the Seattle Convention Center. You can attend in person, or virtually.

I’m going to be there in person. It’s not a decision I take lightly – I still get nervous when I see rooms full of unmasked people. However, I was so overwhelmed with happiness at the in-person SQLBits this spring that I’m ready to give it another shot. (HA! Get it? Booster shot! It’s a joke about… okay never mind.)

This year, I’m not going
to attend a single session.

Not one. I’m gonna spend every single conference hour hanging out in the Community Zone, talking to people in the hallways, and catching up with people that I haven’t seen for a couple of years.

Weird, right? Especially since this is a release year for SQL Server 2022, and there are probably gonna be a lot of good sessions from Microsoft. You know what? I don’t care. I don’t. I’ll watch the recordings and read the slide decks later. When I’m in Seattle, I’m gonna have the experience that I missed so much.

I wanna see y’all again, hear what you’ve been up to, take pictures with each other, and be as friendly as we can while still maintaining good precautions. (No hugs, no shaking hands, no kissing with tongue.)

I can’t talk about my pre-conference session just yet, nor can you register for it. However, now’s a good time to start having conversations with your company management and your significant other to talk about whether maybe an in-person event makes sense this year – or whether you’d rather do it virtually.

In the immortal words of the sisters Pointer, I’m so excited.

The 6-Year-Old T-SQL Book That Never Goes Out of Style

Last Updated May 7, 2022

Brent Ozar

T-SQL

You’ve been working with Microsoft SQL Server for yeeears, and you’re pretty confident that you know how to write a query.

So when I tell you about a book called T-SQL Fundamentals, you’re all, “nah, man, I got this covered.”

You are incorrect.

Sure, the first 5 chapters of the book are about 1-2 table queries, CTEs, subqueries, etc, and you’ve probably got that stuff down cold. But here’s where chapter 6 and 7 go:

UNION, INTERSECT, EXCEPT
Windowing functions
Pivoting data
GROUPING SETS, ROLLUP

And subsequent chapters only get more detailed from there: how to properly modify data with transactions in mind, temporal tables, and more.

When you start your 3rd year of working with T-SQL, this is the book your manager should hand you. By that point, you’ve learned enough about querying that you’re getting cocky, and yet there are so many more awesome possibilities available to you.

And if you’re the one who’s the manager – if you have junior folks on your team – then it’s your responsibility to hand Itzik Ben-Gan’s book T-SQL Fundamentals to your team. They’re not getting any smarter on their own, and every day that they don’t read this, they’re producing more inefficient technical debt that you’re going to have to go back and fix later.

Office Hours Speed Round: Text Edition

Last Updated May 8, 2022

Brent Ozar

Production Database Administration

I recorded an Office Hours stream answering a bunch of your questions quickly, but…I forgot to turn on my microphone, so the video only had the sound effects:

Oops.

So here are the questions in text form instead:

Q: 22DBA: what performance recommendation you see people give all the time but it actually doesn’t work

Lowering fill factor server-wide. They somehow think that making your table larger will make things go faster.

Q: Not the Licensing Police But….: I work for a company that has 18 SQL Servers (mix of Standard and Enterprise) that I know are not licensed. I’ve flagged it and management simply don’t care about them not being licensed, and want to leave as is. “We need to save money” -What would your approach be here?

Put it in writing. Email your manager with a list of SQL Servers, their core count, and the approximate licensing fee. Don’t do it as an accusation, just put in writing that we have this many SQL Servers, and I don’t think we’re licensed for it. It’s then the manager’s problem. If the company doesn’t solve it, then it’s up to you to decide whether you want to keep working for that company – because if they’ll screw Microsoft, they’ll probably screw you when they get a chance, too.

Q: Higgins: Is Disk Queue Length a good metric to monitor for SQL server? If so, what are recommended values?

No. (Good job on the Ferrari-related name though, hahaha.)

Q: Alf: For partitioned tables, do you ever see partition keys on fields other than dates? If so, what are they?

No. I’m sure they’re out there, but I just don’t see ’em.

Q: Wally: When should “DBCC UPDATEUSAGE” be run? Is it expensive like CHECKDB?

After you upgrade SQL Server 2005 to 2008. I haven’t run it in over a decade though.

Q: Keld Rasmussen: Hi Brent, thanks for great posts. When I do select I some times use Read uncommitted because otherwise I lock the database for the application and the application updates fails. – How do I read committed from database without locking for the application? Best regards Keld

Check out RCSI.

Q: NotCloseEnough2RetirementToStopLearning: Hi Brent Any advice on how to move from a production DBA role to a Data Architecture role?

Talk to the data architects at your company to start gradually transitioning into that work. You probably won’t be able to leave your current company and jump to a new company in a data architect role – that jump is too far.

Q: Krishna: How many hours do you sleep and work per day on average?

I usually sleep 8-9pm to 3-4am, plus a 1-hour nap in the afternoon. On the days when I work, it’s usually 8-10 hours, but I just try to work as few days as possible.

Q: .NET User: Hi Brent, Love your office hours, thanks it helps a lot. In previous session someone asked about soft delete and you suggested to add IsDeleted field to the table. I wonder what are your thought about using soft delete vs temporal tables approach? Thanks.

Temporal tables make copies of the entire row as the row changes – that’s a waste of space when you just need soft deletes.

Q: Ezra: The previous CTO convinced all the developers to always use NOLOCK in all their sp’s. How do we change the mindset for this bad practice?

Search for Brent Ozar nolock and you’ll find a ton of videos and demos.

Q: Wally: What is your opinion on using views as constants holders? e.g. CREATE VIEW vStatus AS SELECT 1 AS Active, 0 AS Inactive, 2 AS Paused. Could multiple CROSS JOINs to similar views cause compilation issues?

It’s dumb because there are no statistics on the output of views. Use tables instead.

Q: Midwest DBA: What are your thoughts on Ottertune.com? Seems like a cool technology. Do you foresee Microsoft employing Machine Learning in this way to improve database/server performance?

Love the idea of OtterTune. Microsoft doesn’t appear to be taking this approach. They’re hard at work figuring out Cost Threshold for Parallelism. Gotta learn to crawl before you learn to walk, I guess.

Q: DBA_Mufasa: Hi Brent! What’s your best approach for tracking tables usage in SQL databases, in order to find tables that haven’t been touched (for eg. no select, updates, or inserts ) for a a defined amount of time. Considering that table statistics get cleared after server restart.

What’s the problem you’re trying to solve? If you’re trying to drop tables, go talk to the users.

Q: Morty: How do you determine the optimal number of DBA’s for a given product / company?

This.

Q: CKI: Management wants to create a copy of the production database for reporting purposes. The new “reporting” copy should be refreshed nightly with production data. Production database is on AWS Web Edition 45GB. What is easiest way to do it? Thank you very much!

Nightly restores.

Q: Quincy: How do you determine which SQL changes are low risk and which changes are high risk?

Whoever makes the change needs to list at least 2 ways the change might screw things up. If they can’t come up with 2 ways, they don’t understand enough about the change. Then, think about the business risks for those problems.

Q: Brutus: How do you determine the optimal Windows OS page file size for a bare metal SQL server?

I read the documentation. Doesn’t work for everybody, I know – some people struggle to read.

Q: Sandeep Pawar: I am a SQL DBA with 12 years of experience and also learning and trying my hands on Postgres as well. Is SQL+ Postgres DBA combo would be beneficial? Thanks

I wouldn’t go learning random technologies. Ask what your company needs, or if you want to change companies, think about the technology you wanna use for the rest of your life. Don’t gamble on what unknown companies might like – companies need janitors. Does that mean you should pick up janitorial skills?

Q: Stimpy: For concurrently executing queries against the same tables, will SQL Server share the read operation data between the two different queries?

Read this.

Q: cyrpl: Moving SQL query analysis out of SSMS to Azure data studios will be a big hill to climb for most DBAs, where are you on that hill Brent? Have you written any Jupyter notebooks yet, it looks like Microsoft and others are moving all in on this, what do you think?

Yes, I’ve written blog posts with notebooks. I can’t really teach with notebooks yet because they don’t have execution plan support.

Q: Twiki: What are the top SAN concepts that are beneficial for the SQL DBA to know about?

Start learning the storage your company uses. Often, it’s not SAN at all, but instead it’s cloud-based storage, and that’s completely different.

Q: TJ: We have 1TB database containing 950 GB single table. This table has 95% of data that is not needed anymore. Application team is recommending a slow and gradual delete of the data from front end, but it can lead to performance issues as you know. Can you advise best approach

If you’re going from a 1TB database down to 100GB, don’t do deletes. Insert the data you’re keeping into a new database.

Q: WhatsUpDocs?: Hi Brent, have you ever needed to look at business documentation (check business rules/logic) when consulting or as an employee, but it was severely lacking? Recently joined a different team in work and trying to find simple answers to questions is an uphill struggle…

The vast, vast majority of companies don’t document their technology. The tech is in a constant state of flux, and it’s a miracle if the tech even works, let alone is documented accurately. If you’re the kind of person who needs accurate, up-to-date documentation on the tools you use, you’ll be happier working for very large, slow-moving companies with compliance needs. Think giant global financial corporations.

Q: Neil: When a query goes parallel, does it use the MAXDOP number of threads or only as many threads as it needs ?

Generally speaking, it starts at MAXDOP + 1 coordinating thread.

Q: Erdem: Why does multi column index only have single column histogram step value?

Because Microsoft chose to fit stats in an 8KB page, and that required design tradeoffs.

Q: Conan: Non Tech question: Next Travel plans if any?

Israel this fall, Antarctica this winter.

Q: Preben: What is your least favorite operator in an index plan? E.g: EriK Darling’s eager spools

I thought a lot about this, and to refresh my memory, I looked at Hugo Kornelis’s operator list. My current obsession is the Parallelism Gather Streams operator, but I’m still in the honeymoon phase with it because everything seems so cool. Each new thing I learn about it, I think, “Ooo, neat, I can understand why they made that tradeoff.”

When I was looking at Hugo’s list just now, I cringed a little when I saw the Bitmap operator. I’ve managed to get through almost 25 years of working with SQL Server without having to know what that operator did. I’d kinda always had it in the back of my mind as, “I should learn more about that someday,” and so I read through Hugo’s explanation just now. I nodded my way through it, and closed the browser tab like Grandpa Simpson walking back out of the club. If you made me pick, that’s probably my least favorite.

Don’t Treat SQL Like a Programming or Scripting Language.

Last Updated May 10, 2022

Brent Ozar

T-SQL

In a programming language like C# or Java, you tell the computer what to do, in order.

Get the customers from California, then get their invoices, then sum the total.

SQL, on the other hand, is a declarative language where you declare the shape of your result set:

Get the total sales from Californian customers.

You’re declaring the output that you want, not the methods the database server uses to build it. Oh sure, you CAN use SQL to declare the shape of your query plan, but generally that leads to heartbreak and despair because:

You don’t know the database engine’s internals as well as you think
You’re backing the database server into a corner, forcing it to use specific techniques, when it has better ones available at its disposal
Your data distribution is going to change over time, meaning the method you use to process the data today won’t make sense in a year or two
Your database server is going to change over time as you patch and upgrade it, meaning it has newer and better methods to process data, and it needs the flexibility to leverage those
Your hardware is going to change over time, and the query plan that made sense with 16GB RAM won’t make as much sense with 512GB RAM

When your application is young (1-5 years), use as few engine-specific hints as possible in your queries. Don’t tell the database server which index to use, what order to do its processing, or what kinds of joins to prefer. Give it as much flexibility over time as possible by keeping your query simple and letting the engine make different decisions as your data, database version, and hardware changes.

When your application is mature (5-10 years), then your data distribution, database server version, and hardware has started to specialize. This is the time where you can start bossing the database engine around with specific hints to tell it how to do processing.

But if you use those hints when your application is young, you’ll find that you’re constantly revisiting old code, having to fix query performance, because the query hints you used on a 1-year-old application just don’t make sense anymore, and they’re making performance worse instead of better.

The Top Feature Requests for SQL Server

Last Updated May 5, 2022

Brent Ozar

SQL Server

If you want to get a look at where Microsoft SQL Server is heading in the future, it helps to get an idea of what users are repeatedly asking for. Here’s a quick rundown of the top requests from feedback.azure.com as voted by the people who cared enough to log in and upvote ’em:

10: Enable Query Store for collection on a read-only replica in an Availability Group: 563 votes

9. Add an SSMS keyboard shortcut that executes the statement where the cursor is placed: 589 votes

Of course I have a straight face under here, why do you ask?

8: Improve NoSQL functionality: 652 votes

7: Run DBCC CHECKDB with PHYSICAL_ONLY automatically in the background: 672 votes

6. Support DISTINCT for STRING_AGG: 717 votes

5. Add a row position column to STRING_SPLIT: 747 votes

4. Develop an SSRS ReportViewer for ASP.NET Core: 1,262 votes

3. Restore a table from backup: 1,347 votes

2. Dark theme for SSMS: 1,958 votes

1. Put the Debugger back into SSMS: 2,041 votes

When thousands of folks band together to ask for something, Microsoft hears your voice. They know it’s important to keep the end user community satisfied so folks will keep evangelizing the database they know and love. That’s why in every release, Microsoft keeps bringing crowd-pleasing game-changers like SQL Server Ledger, blockchain technology in the database that keeps your transaction log around forever inside the database. I’m pretty sure that one was #11 in the list above. Maybe #12.

That’s why it’s so important that you create feedback requests to reflect what you’re interested, take time to read the requests from other folks, and upvote the ones you’d like to see implemented. Microsoft believes that your votes should matter, and that’s why they show the list of feedback items sorted by votes. Your voice matters: check out Microsoft Feedback today.

Blog

SQL Server 2022 doesn’t remove those parallelism blockers, though.

Let’s design a data model for gender and sexuality.

Gender isn’t really a bit flag.

Our 4 starting sexual preferences are still a thing, but there are more.

I’m pansexual: gender isn’t a factor for me.

I’m blogging about my sexuality for 3 reasons.

But don’t worry, this blog is still about data.

I know this is complex. Feel free to ask me questions.

Update: wanna know what kinds of messages I got for this?

This year, I’m not going to attend a single session.

Hi! I’m Brent Ozar.

Let’s design a data model
for gender and sexuality.

Our 4 starting sexual preferences
are still a thing, but there are more.

I’m pansexual:
gender isn’t a factor for me.

I’m blogging about my sexuality
for 3 reasons.

But don’t worry,
this blog is still about data.

I know this is complex.
Feel free to ask me questions.

Update: wanna know what
kinds of messages I got for this?

This year, I’m not going
to attend a single session.