Blog

[Video] Office Hours at Megunticook Falls in Camden, Maine

Last Updated October 25, 2023

While in historic and beautiful Camden to hang out with friends, I went to Megunticook Falls and answered your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:05 Mike: Hi Brent. You’ve mentioned there are two types of DBAs: Production DBAs and Development DBAs. Are recruiters and hiring managers familiar with the term “Development DBA” or would “Database Developer” be a more well-known title to use?
03:03 Margaret Norkett: We are transitioning to SQL2019 and notice that many queries that ran well on 2014 or 2016 are now very slow. We have added the OPTION (USE HINT (‘FORCE_LEGACY_CARDINALITY_ESTIMATION’)) to help and do see it running better, but still not at pre-upgrade speeds. Where else to look?
06:13 Matt: What’s the best way to handle hierarchical data? I’ve got 6 levels of data and 95% of the time, given the top level, I need all the children. root = 1, level 2 = 3 rows, level 3 = 5-10 per level 2, level 4 = 10-20 per level 3, level 5 = 5-7 per level 4, level 6 = 2-3 per level 5
06:52 Hany Helmy: Hi Brent, SQL server Enterprise Edition is $7,000 per core, Standard Edition is $2,000 per core, question: Is this is annual payment? or lifetime payment?
08:20 Montro1981: Hi Brent, can you make another “questions I didn’t want to answer (online) post” I see some really good candidate questions on PollGab (I’ve been bingeing Office hours at work so I can hear you answers in my mind)
09:06 Alma Fahlstrøm: What is your opinion of database sharding?
11:31 consultantwannabe: hey Brent, what’s the most common issue you find when consulting when clients? Things that make you think: “really? c’mon. It’s 2023, why are you still doing that?”
13:17 Johnkurt: our architect suggests read-scale with Always On availability groups to a analyze server. What is your experience and is it something we should be aware of?

Office Hours, Silent Film Edition

Last Updated November 11, 2023

Brent Ozar

Videos

Hey, remember in yesterday’s video how I said I was having a really bad day? Well, here’s how bad it was – I recorded two videos in a row, and I forgot to turn my microphone on for the second video.

<sigh>

So here are the questions & answers:

00:25 Calvin H: Any tips or gotcha’s for query tuning of hierarchy id data types in MSSQL? No. Just be aware that recursive CTEs (which are usually used with hierarchy IDs) produce single-threaded plans. If you have a big complex query, and HierarchyIDs are part of that, you might consider dumping the HierarchyID info to temp table first, and then joining to that.

01:22 Artis Leon Ivey Jr: What is your opinion of using Azure Backup to backup SQL VM instead of native SQL backup? Use the tool that your team is most comfortable with. Backups aren’t the place to experiment. If most of your team is Windows admins and they’re used to Azure Backup, then stick with that.

02:22 B. Horrowitz: What are your thoughts on having multiple different DB technologies across different microservices for a single app? What should the DBA team look like in this scenario? The place where you usually see that is when you have a lot of microservices, each with their own dedicated development teams. You don’t see it with 2-3 microservices with 5-10 total developers. So if you’ve got several 10-20 person dev teams, each using their own data persistence layer, then you usually see them doing their own database administration.

04:04 Cameron Harding: Who is the Brent Ozar for Microsoft Fabric training? Microsoft. They’re the only company that can afford to continuously lose money building training material for a product that’ll get a name change and architecture revamp every full moon.

04:59 Red Utley: Is Intel Hyper-Threading ever worth the licensing pain for SQL Server performance improvements? When you license physical boxes, you license physical (not hyperthreaded) cores, so there’s no licensing pain there. If you’re licensing by the guest, then typically the bigger CPU problem is the noisy neighbor VMs, not hyperthreading.

05:55 ChompingBits: I found out our backup plan, doing full backups on all our user databases (around 46 TB) was a out of the norm. (I cede it is overkill to do Fulls every day, but it sort of shook my confidence a bit. In your career what did you find our was a house policy and not a best practice? Happens all the time – it’s called cargo cult programming. Also, be aware that daily fulls on 46TB is indeed totally normal – it’s just that you’ll wanna use SAN snapshot backups at that size.

07:32 Maria Bonnevie: Should we ever be worried about high number of sessions / connections to SQL Server in relation to other metrics (sessions to sever memory, etc)? No, but I’m usually concerned when I see >1000 connections because at some point, they’re all going to pipe up and do something simultaneously. That’s where your heartaches begin.

09:07 Montro1981: Hi Brent, I’m on a fishing expedition and I hope you can direct me to the right pond, I have a filtered index on a date range. But the stats histogram is going all wonky on me all the time the first 100 steps are 1 day each but the rest (7470) gets put in 1 step. That’s beyond an Office Hours question, and it’s a consulting-style question. It’s just such a niche topic.

09:50 hamburger sandwich: Is there automatic windows OS patching for availabilty groups where it will failover and update? Not in a way that you’ll be comfortable with if you have 24/7 activity and long-running tasks like backups or CHECKDB. Every patching tool I’ve ever seen will happily patch and reboot in the middle of a database backup or CHECKDB.

10:51 Aashly: Hi Brent, We have an upcoming project where the team plans to to perform an in-place upgrade from SQL Server 2017 with mirroring to SQL Server 2022 with AOAG on a prod server. What are your thoughts on this and what precautions can be taken if we are left with no choice. I don’t do it.

11:51 Paul Oakenfold: Will you be visiting the new Fontainbleau Hotel when it opens? Not for a while at least. To learn more, check out VegasPaulyC and VegasStarfish on TikTok – they’ve done behind-the-scenes tours & discussions of it.

12:33 She-Ra: Is clustered index fragmentation any more consequential than non-clustered index fragmentation? Watch this.

13:10 Hal: What’s your opinion of Jupyter Notebooks and have you considered using them in your training classes? I absolutely love them, but … because they don’t show query plans, I can’t use them in training classes. You can get plans in Azure Data Studio query tabs, but not in notebooks.

And now if you’ll excuse me, I’m going to go throw my computer in a river and pour myself a drink.

The Real Problem with SQL Server’s Licensing Costs

Last Updated November 11, 2023

Brent Ozar

SQL Server

At least once a month, a client has The Postgres Conversation™ with me.

It happens at the end of a consulting engagement, after we’ve talked about the work that the team needs to do in order to meet their performance objectives.

A manager who’s been quiet the whole meeting suddenly pipes up and quietly says, “I know you don’t really want to hear me ask this, but … what’s the point where we start thinking about different database back ends?”

They expect me to valiantly defend Microsoft SQL Server, talk about how powerful it is, discuss how it makes your app perform fantastically. However, they’re taken completely off guard when I say,

“It shouldn’t be used for most new applications you build today. If you have an existing app built on it, you’re kinda stuck, but if you’re building a new application, you should use Postgres instead.”

On a recent call, the client’s DBA disagreed with me, saying that Microsoft SQL Server’s query optimizer is better, so it makes queries run faster. Who cares? For the price of the licensing, you can buy a dramatically larger Postgres server:

(Details about those numbers: I’m using x2iedn reserved instances at EC2, with licensing included. Vantage screenshot of more detailed pricing comparison, and you’re free to make your own at EC2instances.info or whatever Azure pricing calculator you wanna use. If you use an Azure pricing tool from Microsoft, though, make sure it includes the cost of the licensing – a lot of their tools do a magical “licensing assurance” thing that implies you’re bringing some imaginary free licensing from somewhere.)

If you go with Postgres, you get literally twice the hardware for the same money – and by the time you hit SQL Server Enterprise Edition tiers of hardware, four times as much hardware! Why on earth would you pick SQL Server given that comparison?

Or to put it another way, these two things cost the same:

Just 1 32-core SQL Server running Enterprise Edition
A 4-replica fleet of 32-core servers running Postgres

Microsoft’s problem is that good hardware got too cheap.

We’re living in a world where $6,000 buys a Lenovo laptop with 24 cores and 128GB RAM – but to license that laptop with SQL Server Standard Edition would cost about $50,000. Don’t even get me started on the fact that when the next bigger laptop comes out, you would have to step up to SQL Server Enterprise Edition – just to license that laptop! During the lifespan of SQL Server 2022 and 2019, we are absolutely going to see laptops so fast that they would require Enterprise Edition.

Server pricing isn’t much higher: you can configure a low-end Dell PowerEdge R6515 with a fast 24-core processor and 128GB RAM for less than $7,000.

SQL Server’s licensing is desperately out of touch given today’s hardware. The Microsoft tax doesn’t offset the ability to use two to four times more hardware with Postgres for the same price as licensing a box with SQL Server.

Don’t give me the “total cost of ownership” spiel, either, unless you’re willing to show me how Microsoft SQL Server DBAs somehow cost less money than Postgres DBAs.

So, is SQL Server going to get cheaper?

Honestly? I doubt it, and if I was in Microsoft’s shoes, I wouldn’t discount the boxed product either. Existing applications are trapped in their SQL Servers, and they’re used to paying $2,000 Std & $7,000 Enterprise per CPU core. They don’t realize how expensive that’s become today.

Even customers who do understand it, look at the cost of migrating their application from SQL Server over to Postgres, and most of them quickly decide to kick the can down the road a while longer. “We’re going to rewrite the app from scratch soon,” they say, “and when we do, we’ll take a fresh look at our database choices then.”

That’s why Microsoft offers Azure Database for PostgreSQL, and why they bought Citus Data a few years ago. Microsoft saw this coming too, and they’re hedging their bets to make sure whatever database you wanna use – expensive or not – they’re going to offer it on Azure.

And that’s a good thing for all of us. More competition and more offerings is good.

Office Hours: “Read This” Edition

Last Updated November 9, 2023

Brent Ozar

SQL Server

Sometimes when people post questions at https://pollgab.com/room/brento, the answer is just a link, nothing more, nothing less. It doesn’t make sense for me to rehash something that’s been covered really well online, and I want to link ’em to the best resource possible.

Petr: Huge slowdown of basic queries when using a partitioned table (Partition_Key = ‘YYYYMM’). Queries: select max(ID) from PartitionTable; select top (1) ID from PartitionTable order by ID desc; scans all partitions and then limits the result (Sort, Stream Aggreate). What with this?

Read this and this.

MyTeaGotCold: Outside of T-SQL and Postgres, what SQL implementations do you find that people take seriously? I can’t remember the last time I heard a good thing about Oracle.

Read this.

Kulstad: I’ve just run SP_BLITZ on one of my production servers, and Query Store Disabled came up as a Priority 200 on SSISDB. Do you recommend turning QueryStore on for SSISDB? Are there any special config options or “gotchas” that I should be aware of in doing so?

Watch this.

Brent Reading Book — “What do all these big words mean?”

Sylvie: Do you have a good way to see which deprecated features are used by a given TSQL query?

Read this.

ReadyToJump: You have said you are into software version control. I would like to jump to the bandwagon, where do I start? How can we do version control of stored procedures?

Watch these.

A Business Intelligence guy: Kind of off topic, but I’m building up my home bar and wanted your recommendation on a range of tequilas to consider. I seem to recall you have mentioned some in the past but I can’t seem to locate those mentions again

Try these. (That’s not a random Google link either – I like most of those.)

need for spid: Is memory-optimized tables a good fit to implement a queue table, where there are multiple producers and multiple consumers ? We don’t need the queue data to be persisted. Are there any drawbacks to consider ?

Use this or this or this.

chandwich: In the world of SQL Server data, the veterans tend to have the most influence, given their lengthy presence. So, how can a mid-20s SQL DBA stand out in a field ruled by these seasoned pros?

Read this.

chandwich: May I use the StackOverflow databases for reference on my website?

Read this.

bill keegan: What are the advantages / disadvantages of multiple mdf / ndf’s?

Watch this.

[Video] Office Hours: Having a Bad Day Edition

Last Updated October 25, 2023

Brent Ozar

Videos

The first day back after a long vacation is always rough, and today’s no exception. I took your questions from https://pollgab.com/room/brento in an effort to turn things around and get back in the groove:

Here’s what we covered:

00:00 Start
01:12 NotCloseEnoughToRetirementToStopLearning: If you got a new job production DBA where you had to support one of the open source RDBMS (mariadb, postgres etc) how would you start learning that new technology?
02:27 i’m a potato: Hi Brent, how would you configure SQL Server to survive frequent power outages? Is disabling disk cache an option, for example? we can’t buy UPSs and config poweroffs at this point. The new site just sucks this hard.
03:18 Stockburn: Hi Brent, Random message but have you still kept your place in Mexico? Used to love the turtle videos. As always thanks for everything you do for the SQL community.
04:12 Sam Gompers: What is your opinion of a 32 hour work week for corporate America?
04:48 Thorbjørn Harr: What are your pros / cons of using SQL Server to send customer facing emails?
05:41 Miles: Hi Brent, RCSI enabled on the database and (NOLOCK) hint is being used all over places.We also see a lot of Sch-S locks and blocking happening. SELECT’s blocking INS/UPD/DEL/TRUNCATE..Could you please explain a scenario where NOLOCK can be evil in any one of above situations?
06:52 Q-Ent: HI Brent, Do you plan any changes on your mastering Index tunning class in order to add columnstore indexes? Can columnstore be part of DEATH method?
07:16 Mr. SqlSeeks: How did your experience with Invisalign go? How long did you use it and how do you feel about the results?
07:40 sureman: As a consultant, how do you protect yourself from finger pointing/blame for problems that occur after you leave? what would you advise other consultants who may face this issue?
09:13 M. Cardona: Can we look forward to any new online SQL training content from Brent Ozar in 2024?
10:15 The Edge: What’s the Venetian Sphere like?

[Video] Office Hours: Yes, I’m In the Same Shirt

Last Updated October 25, 2023

Brent Ozar

Videos

Why yes, I’m wearing the same shirt as the video from “yesterday”. Why do you ask? Stop paying attention to that, and check out the top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
00:12 DBABA (database administrator by accident): I have a complex, nested view. everytime after a dacpac deployment this view runs into timeouts for a couple of days. during deployment no indexes are rebuilt. what could cause this? the only suspect left to me are view refreshs during deployment/skewed statistics.
01:28 Sean: Is a buffer pool extension worth using if your storage is SSD anyway?
02:56 Christopher Wallace: What is your opinion of using Azure Elastic SAN for hosting SQL VM data files?
04:58 Stanley Burrell: What is your opinion of using HammerDB for load testing prospective new SQL Server VMs?
06:10 Blutarsky: What is you recommended naming convention for clustered indexes, non-clustered indexes, and constraints?
07:26 gserdijn: Hello Brent, if I have a table with a composite primary key (C1, C2) which is also the clustered index, does it make sense to have a nonclustered index on column C1? I found a few of these, and I would like to trim the fat.
08:41 Danielle Bregoli: Is there a good way to determine if a given query / query plan was the result of a plan guide?
09:45 Gonzo: What are your pros/cons of SSMS vs Azure Data Studio? When should you use one over the other?
11:10 Merrill Stubing: What are your protips for combating sea sickness and jetlag?
14:15 pete: ping me please – I try to watch all the office hours but I do miss a few. Would be nice if we could get notified that our question was being covered. Office hours are like free training.
15:20 Ricardo: Is there any way I can throttle (not stop) I/O for a specific SQL login in SQL Server 2016 standard edition? (it’s in an Azure VM by-the-way)

Free Webcast: How to Think Like the Engine, Live

Last Updated November 6, 2023

Brent Ozar

Conferences and Classes

You’re comfortable writing queries to get the data you need.

But you’re uncomfortable if someone asks you how it works, how SQL Server and Azure SQL DB turn your queries into results. You know there are execution plans, but … you’re uncomfortable reading them. You know queries need indexes, but you’re not sure which columns to put in order, or how SQL Server chooses between them.

I’d like to teach you How to Think Like the SQL Server Engine for free.

In a 2-hour live session, you’ll learn the differences between clustered & nonclustered indexes, why seeks aren’t necessarily good and scans aren’t necessarily bad, why queries run so much faster in development than production, and much more. I wanted to design the most important concepts you could learn in your first 2-hour training session – whether you’re a developer, report writer, sysadmin, or DBA.

I’m doing 2 live sessions in different time zones:

Weds, Nov 15 10AM-Noon Eastern, 7AM-9AM Pacific
Thurs, Nov 16 3PM-5PM Eastern, Noon-2PM Pacific

Who’s Hiring in the Microsoft Data Platform Community? November 2023 Edition

Last Updated November 6, 2023

Brent Ozar

Who’s Hiring

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

[Video] Halloween Office Hours in Salem, MA

Last Updated October 25, 2023

Brent Ozar

Videos

I visited friends in Salem, Massachusetts, home of the 1692 witch trials, and it turns out Salem is a great place to visit around Halloween! There were tours, characters in costume, witch gear shops, and all kinds of spooky-themed happenings.

I sat down outside of the Charter Street Cemetery to take your top-voted questions from https://pollgab.com/room/brento:

Here’s what we discussed:

00:00 Start
01:33 SQLDevDBA: Hey Brent, have you thought about an episode where you put the questions asked here into Chat GPT or (the little brother it has to take everywhere) Bing AI to see how hilariously bad and misleading they are? It seems those systems go mostly unchecked and it would be cool to see.
02:27 Bill Grates: I want to test how long a query takes to run, without actually returning results. I vaguely remember some kind of Select Into Null construct. Does that still exist, what is the syntax?
03:33 ProochingMan: Too eliminate bloat and lower work for daily maintenance jobs, what do you think about periodically cleaning up auto-created statistics by dropping any that are older than a set threshold (e.g., 6 months) and just allowing any that are being used to be recreated again?
04:28 MyTeaGotCold: How do I transition from SQL Server to Postgres without having to start from scratch? What are the most important skills that won’t be transferable?
05:25 sqltzy: Hi Brent. A colleague mistakenly ran an INSERT selecting from a table with 16B rows. 4 hours and many log file growth events later, I noticed and killed it. The rollback took ages. For next time, is there a way to kill without a rollback when you really don’t care about the data?
06:33 Sylvie: In what scenarios do you like to use the SSMS option for discarding query results after execution?
06:51 Montro1981: Hi Brent, I hope you are doing good. Can you share one of the craziest thing you needed to do to get across the finishline?
08:38 AllHailOurMachineOverlords: Did you get to see the recent Northern Lights while in Iceland and are they as amazing as people say?
10:06 Calvin Broadus: Have a NC index with high writes to low reads for a busy table that we are considering dropping. Would like to know which app(s) are using this NC index before we drop it. Is there a good way to search which apps are using this index before we drop it?
11:22 Stupid DBA: I’m having an Ascending Key Problem in a table. I have attempted to update statistics, but no matter what I do SQL Server estimates 1 row to be returned and actual is 3M from the start of the query plan.. This makes me feel stupid, please unstupid me.
12:55 CaliDBA: Hi Brent, Hope you enjoyed your travels. What would be your advice on the best/quickest way to back up a multi terabyte database which is accessed by users 24/7?
13:33 soupnazi: Do you have any horror stories for us where a shop ran their web app under the SQL SA account?
14:56 MyFriendAsked: Hi Brent, have you had any experience with 3rd party backup tools, such as Redgate SQL Backup Pro or LiteSpeed for SQL? If you had to choose a 3rd party tool for backups of VLDBs, what would you go for? Thank you
16:00 Gonzo: Why did SQLBits change their speaker reimbursement model? Will this have an adverse effect on speaker turn out?

Announcing Buy-Now-Pay-Later

Last Updated December 1, 2023

Brent Ozar

Company News

Some of you are staring longingly at my training classes. You don’t get a training budget at work, so anything you invest in your own career has to come out of your own pocket.

Good news: we now offer buy-now-pay-later! It’s perfect for one-time purchases like the Lifetime Fundamentals & Mastering Bundle. (We only offer buy-now-pay-later on one-time purchases, not subscription products.)

Right now, it’s only available for USD ($) purchases. When my provider adds more countries & currencies, they’ll automatically be enabled in the store, but for now, your best bet is to try to check out and see if you get the buy now pay later option pictured in the screenshot.

Depending on what you buy, you can choose from a list of terms. Here are a few examples for a $1,695 product:

Here are the products where buy-now-pay-later makes the most sense:

Feel free to comment if you’ve got questions. Enjoy!

Updated First Responder Kit and Consultant Toolkit for October 2023

Last Updated October 20, 2023

Brent Ozar

First Responder Kit Updates

The last couple of releases have focused on running sp_Blitz with limited permissions, like not being able to see inside some user databases. Those efforts continue this month with a lot of work from Montro1981.

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

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

sp_Blitz Changes

Enhancement: now automatically skips MSDB checks in environments where you don’t have permissions for MSDB. (#3355, thanks Montro1981.)
Fix: won’t error out if you don’t have permissions to view traces. (#3326, thanks Montro1981.)
Fix: better detection of whether you have permissions in the model database. (#3334, thanks Montro1981.)
Fix: more clear explanation of what sp_Blitz’s @Debug parameter does. (#3348, thanks Montro1981.)
Fix: skip DBCC DBINFO calls correctly. (#3350, thanks Montro1981.)
Fix: better checking of permissions on sp_validatelogins, xp_regread, and xp_readerrorlog. (#3356, thanks Montro1981.)

sp_BlitzCache Changes

New @SortOrder = ‘duplicate’ shows the queries with the most cached plans. Here’s an example of the output:

This one’s a little tricky: the result set IS the top X queries with the most duplicated plans in cache, however, the list isn’t sorted by that. It’s sorted by CPU by default. Also:

Fix: when using @SortOrder = ‘all’, avg spills were being saved to table as ‘avg grant’. (#3331, thanks eschnepel.)
Fix: reduce likelihood that sp_BlitzCache’s global temp tables will disappear while the proc is prepping to run. (#3342)
Fix: single-use plans were reporting over 100% if you were running the exact same code in multiple databases. (#3353)
Fix: case sensitive collations would fail due to different variable casing for @MinimumExecutionCount. (#3368, thanks Mark Keyworth and Montro1981.)

sp_BlitzFirst Changes

Fix: if you took a 1-second sample of waits, you could get a divide by zero error. (#3370, thanks Montro1981.)

sp_BlitzLock Changes

Enhancement: new check for deadlocks with background processes. (#3329, thanks Erik Darling.)
Fix: don’t tell people to enable RCSI if it’s already enabled. (#3347, thanks Erik Darling.)

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: Back in the Vegas Home Studio

Last Updated October 18, 2023

Brent Ozar

Videos

After a long trip through Scandinavia, I’m back in the desert. You posted questions at https://pollgab.com/room/brento and I took the hot seat:

Here’s what we covered today:

00:00 Start
03:15 Marek: Hi Brent, what is the best solution to continuously synchronize Logins between two stand alone SQL Server instances or two Azure managed instances?
04:13 MancDBA: Hi Brent, have you ever walked off a job because the place is beyond help? For example, combative DBAs or difficult clients who don’t want to play ball?
06:31 Developer who cosplays as a DBA: Your video about deadlocks from a couple years ago is fantastic. Your demo was focused on writer/writer deadlocks – do you think you’ll ever do a demo video for understanding and troubleshooting deadlocks between a reader and a writer?
07:07 Ethan: What are your pros / cons of a shared development SQL server?
08:21 Zac: Most of the AWS RDS instances don’t support TempDB on the local ephemeral storage, only on EBS volumes. How important do you think it is to have TempDB on local storage?
09:37 Shay: Question: is there a way that remarks are not be part of query cache, we use remarks to track query source and query plan is created per query text.
10:20 SeeCoolGuy: HeyO Brent! when do we get an AMA from your home in Vegas in the hot tub 😛
11:33 Bishal: We have a database with a table to store document files (.pdf &.doc files) to use it to search texts on document using Full-text. However, this table is so huge & giving us a headache during backups. Are there any ways to separate documents from table and still use FullText?
13:44 Karthik: What is your opinion of the Azure SQL VM SKU recommendation engine in migration extension for Azure Data Studio? Does it produce good recommendations?
14:17 Red Utley: Should SQL CPU licensing ever drive VM sizing selection or only disk IOPS/ throughput?
15:07 Miles: Hi Brent, Very Good morning. RCSI is turned on on one of the user database. However, we still see SELECT’s are getting blocked by INSERT statements. Any reason why? Please explain.

SQL ConstantCare® Population Report: Fall 2023

Last Updated October 18, 2023

Brent Ozar

SQL Server Population Report

The short story for this quarter: SQL Server 2019 is on fire, dominating the market.

For the long story: ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates? Let’s find out in the summer 2023 version of our SQL ConstantCare® population report.

Out of the thousands of monitored SQL Servers, a whopping 44% are SQL Server 2019! That’s the highest percentage we’ve seen for any version in the 3 years that we’ve been doing this analysis.

I’ve excluded 2008, 2008R2, and the various flavors of Azure SQL DB from that chart because the numbers are all much smaller than SQL Server 2012.

SQL Server 2019 is up 6% from the last quarter, and it’s taking market share from every other version but SQL Server 2022. Every other version’s adoption rate is down 1-2% this quarter.

SQL Server 2017 is now the version that time forgot: folks are just skipping past that version, standardizing their new builds on 2019 rather than 2017. There wasn’t anything wrong with 2017, per se, but it just came out too quickly after 2016. These days, if you’re going to do a new build, I can’t think of a good reason to use 2017. I’ve also updated my Which Version to Use post to reflect that.

After a year of availability, SQL Server 2022’s adoption rate is 4%. That’s somewhat behind SQL Server 2019’s 6% adoption rate of late 2020, but look how big 2019 is today. While I’ve repeatedly gone on record saying 2022 is a mess today, I still think it could be fine long term if two things happen:

If Microsoft gets the Azure-MI-as-DR thing easy enough for small businesses to use, because companies will adore that, and
If Microsoft doesn’t release the next version of SQL Server before late 2024 at the earliest. If another version comes out quickly, then 2022 will be the next 2017, skipped along the way. I think this is a safe guess because here in late 2023, we still don’t even have an announcement of the next version, let alone feature lists, public test versions, or a release date. Let’s generously say the next version comes out in 2024 – that still gives SQL Server 2022 several years of time to be the “latest safest version” after vNext ships. And…
If companies don’t decide to migrate to Azure SQL DB, Managed Instances, or rewrite their applications for Postgres or whatever. It’s conceivable – not likely in the short term, but conceivable – that those competing products might get popular quickly enough that SQL Server 2022 might never catch on. (I’m less worried about this for 2022 than I am for its successor.)

Here’s how adoption is trending over time, with most recent data at the right:

SQL Server 2014 goes out of support in July 2024, so if you’re still running 2014 (or prior) in production, it’s time to make those transition plans to get onto a supported version. 2014 is currently at 7% of the market, and 2012 & prior are at 3%. As those versions continue to taper down, it’ll be interesting to see if SQL Server 2019 grows even further. Wouldn’t it be wild if fully half of the servers out there were running 2019?

These fantastic 2019 adoption numbers really influenced my training class plans for 2024. I’m going to do another round of live classes next year, updating all of my classes to focus on 2019’s features that are widely available. It feels like 2019’s the version companies are standardizing on for the next few years, so it makes sense to standardize our skills on tools that will be available on most of our servers, if not all.

Who’s Hiring in the Microsoft Data Platform Community? October 2023 Edition

Last Updated November 6, 2023

Brent Ozar

Who’s Hiring

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

Fundamentals Week is Coming. Every Day, the Price Goes Up.

Last Updated October 25, 2023

Brent Ozar

Company News, Conferences and Classes

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

You’re ready for Fundamentals Week! You’ll spend the week with me learning about indexes, query tuning, columnstore, how I use the First Responder Kit, and more. On November 6-10, 2023 (add to calendar), 10AM-6PM Eastern, 9AM-5PM Central, 7AM-3PM Pacific, we’ll get together to learn:

Monday: How I Use the First Responder Kit
Tuesday: Fundamentals of Index Tuning
Wednesday: Fundamentals of Query Tuning
Thursday: Fundamentals of Columnstore
Friday: Fundamentals of TempDB

Register right now because it’s the lowest price you’ll ever get. Tomorrow, tack on another $25, and then another $25. Every day that goes by, it’s just more money you’re gonna give me to attend the class, up to the $695 price that will hold up til the class date.

Or, you know, uh, wait. I like money.

[Video] Office Hours with a Surprise Special Guest

Last Updated September 24, 2023

Brent Ozar

Videos

I have a special guest star on today’s episode: Pinal Dave dropped by the house! We went through a few of the top-voted questions from https://pollgab.com/room/brento.

00:00 Start
00:15 What happened to you? Did you spend too much time in the sun?
00:48 AccidentalDBA: How do I explain to the 3rd party IT group that manages our servers that server image backups are not enough to backup/restore SQL. They keep deleting our maintenance plan backup files due to disk space issues. Or am I wrong and everything will be okay?
02:32 Nikos Georgakis: What are the best / worst database technologies for placing career bets currently?
04:39 Minnie Pfister: What was your least favorite version of SQL Server and why?

[Video] Office Hours at the Northernmost Town in the World

Last Updated September 20, 2023

Brent Ozar

Videos

Despite its location, Hammerfest, Norway attracts countless tourists eager to see the festival celebrating one of the world’s favorite rappers. Did I get that right? I’m not sure I got that right.

There’s also fierce competition for the title of northernmost town: nearby Honningsvåg Norway also wants that claim, but with a population of only a couple thousand, they’ve got some humping to do before they can really say it with a straight face. (I went there on the cruise too. To Honningsvåg. Not to the humping.)

Anyhoo, the ship pulled in on a gray morning and I took your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:07 Aksel: How to explain the situation where the newly created index makes the query plan more optimal, but the given index is not displayed in the execution plan graph; If I use the blitzindex command then index usage is 0. I can see the new index name in execution.xml OptimizerStatsUsage
02:48 Icerbergo: sp_Blitz is warning my friend that he has 46k total plans in the cache and 91% created in the past hour. sp_BlitzCache is warning him that he is caching 160k plans. Are there two kind of plans or why are those numbers different?
03:49 RollbackIsSingleThreaded: Hi Brent! I have run out of ideas to blog about SQL Server. How can I proceed?
05:53 Richard Lestrange: Did you see any ship wrecked children at the blue lagoon?
06:33 Junior NotYetDBA: Hi Brent, my old dev colleague created a clustered index char(30) starting with 5 initials from app user name like “BRENTxxxxxxx…” With millions of rows should we expect performance problems? I rather use a sequential ID
07:41 Berglind I.: What is your opinion of the following Microsoft SQL Assessment API recommendations (SQL 2019 enterprise bare metal): Disable ‘Automatically manage paging file size for all drives’ option Defragment volume ‘C:\’ with physical fragmentation
08:38 Bill: Can you create and manipulate temporary tables in queries to a read-only secondary?
09:52 Spike Jones: Which is the better cloud environment for running SQL VM and why? AWS or Azure
12:25 Eh? Aye: My original question got garbled in the middle! Annoying.. Should read: Should we start with AI ‘as a concept’ fundamentals, or should we take an “AI for DBAs” approach? E.g. what data types are used for AI data? How is AI related data queried / searched? Ta Brent, top man.
13:40 bsquidwrd: At some point you had discussed your favorite cruise lines, but I can’t find the vod. Do you mind going over your thoughts on cruise lines again? Seeing how much fun you have on your trips is making me want to finally utilize my PTO
15:25 TeeJay: What’s your current thinking on whether SQL Server 2022 is ready for production use (Standard Edition, single server, no HA/DR)? Has your thinking changed since the CU4 (simple queries returning incorrectly sorted results) debacle?

Well, I’m Not Doing SQLBits After All.

Last Updated October 10, 2023

Brent Ozar

SQLBits

I was so excited when SQLBits announced their 2024 dates & location, and I told y’all that I was blocking out my calendar and I’d be there.

Unfortunately, not gonna happen.

This year, SQLBits dramatically cut payments for speakers. We used to get paid per attendee at our workshops, but that’s done. Now you get paid per tier of attendees, and the top tier of £5000 is about the same as my airfare expenses, let alone hotel time in the UK before or after the conference, or any cool restaurants.

I love the event, but if I’m working, I have to get paid fairly. “For free, we’ll let you spend 13 hours in an airplane, work for us, and then fly straight back home” isn’t a fair payment, especially given the work that goes into writing, rehearsing, delivering, and promoting a workshop.

It’s not for me, but I’m sure they’ll find speakers. There are plenty of Europeans who have short, inexpensive train rides or flights to get to Bits. There are also plenty of people who work for Microsoft, or for vendors who get paid to fly to events like this and promote their products. The £5000 top tier payment will be a pot of gold for them.

I’m not upset, and Bits is still GREAT. It’s just… not an option for me anymore. I understand that Bits has to make tough decisions to stay financially sustainable because heck, so do I!

If you’re in Europe or Bits is an easy/cheap destination for you, you should consider submitting sessions for this year’s event because your chances of getting accepted as a speaker are better than ever. It’s an excellent event, and I’ll be watching with jealousy from afar.

[Video] Office Hours at the Arctic Circle

Last Updated September 20, 2023

Brent Ozar

Videos

Just after passing the Arctic Circle marker and celebrating with a glass of champagne, I took your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:13 TheEveryDayDBA: Does it matter on what column you implement a table partition for a very large table? It has 28 billion rows, with no datetime column to segregate the data out. Will a query with multiple joins take longer to lookup data before / after the table partition is implemented?
02:07 Oli-the-dba: In your previous iterations where you worked as a frontline dba, did you ever regret becoming a dba ?
03:11 pete: My statistics on a huge table are horrible. How can I update statistics on the latest 10% of the table where the majority of the searches take place
04:08 Jessica: Hi Brent, I recently had a Logical Consistency based io error: Incorrect Checksum. CHECKDB returned no errors on any database on the server (runs weekly, manually ran when I got the alert as well). Is it possible to have false positives for Error 824 or am I missing something?
05:18 Hany Helmy: Hi Brent, my company is asking for a receipt or invoice for the Fundamentals & Mastering Bundle training as they don`t accept online payments as per the company policy, how to achieve that?
06:06 Morrise Van Quible: I took your Mastering Indexes class, but don’t remember covering this. My friend says that if I have queries that sometimes have where clauses with Col A and other times Col A and B, that I should have 2 indexes. If I have 1 index on A & B, it has to read more data. True?
08:31 Izzy G.: When running sp_whoisactive, we sometimes see a non-null sqlcmd / sqltext but the query plan column is null. What are the common causes for this scenario?
09:09 Ozan: Hi Brent, in your Senior DBA Class you say with database mirroring we have either HA or DR, but not both. What if I set up sync mirroring to a different data center. Why do I still not have HA and DR? Thanks.
10:20 how-did-I-get-here: Is there a new version of the SQL Server Setup PDF? The one I got in the FR Kit dates from SS2012. Also, is your advice still to have separate drives for dbf, logs, and tempdb even in a VM environment where the different ‘drives’ are partitions on a huge shared drive array?

[Video] Office Hours Off the Coast of Norway

Last Updated September 20, 2023

Brent Ozar

Videos

Come join me in my cabin aboard the Hurtigruten Nordlys, sailing up the coast of Norway, and we’ll cover your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered today:

00:00 Start
00:57 Accidental DBA: What are the best options you have seen for database deployment rollbacks with releases (if issues were encountered)? My understanding is that restoring to a pre-deployment backup or snapshot would break the transaction log chain so wondering if there’s better options.
02:30 Hera H: Which VM technologies should the SQL DBA know about for optimizing SQL VM performance in Azure?
02:51 END TRY BEGIN CRY: Hi Brent. For large text columns, like Notes, that are not going to be indexed, is it better to have a VARCHAR(MAX) so that the data is stored “off page” or is it still better to have it as a VARCHAR(2000) for example, or does it make no difference. Thanks
04:37 Ryan: We admin a CRM server and from time to time we see high cpu and a high wait on resource semaphore. Where should we start? We feel like we have tried everything.
05:30 Thomas Franz: My msdb on SQL 2022 Enterprise is in FULL recovery mode (because of job_history etc.). When I install a new CU it will always be set to SIMPLE recovery. Any idea why? Are there known problems with msdb and FULL recovery or may this be a Microsoft bug?
07:45 Tony F: Did many people wear masks on the plane? Did you feel safe?
09:34 Robert McTables: You wrote a great post a few years ago (How Much Memory is “Normal” for SQL Servers?), do you have any new data trends that you could share about newer SQL versions? Did the trend continue of starving older versions?
10:07 Wicket: Hi Brent, why does SQL Server use the clustered index key in non-clustered indexes instead of the RID? A clustered key could be many wide columns, presumably there are benefits this way but why not just always use a RID and mitigate large keys bloating indexes?
10:54 Red Utley: What is your opinion of using Windows S2D for hosting Azure SQL VM storage?
11:26 Ozan: Hi Brent, from security perspective, would you always enable TDE, since otherwise data can be read in plain text via hex editor as you already demonstrated? Or is there another way to encrypt the data(file)? Thanks
14:04 About Hurtigruten cruises