Product has been added to your cart.

Blog

Microsoft Build 2023 Keynote Live Blog: Introducing Fabric

Brent Ozar
Conferences and Classes
2 Comments

Goooood morning, party people! Today is the opening day of the annual Microsoft Build conference, an event focused on people like developers and power users who build things with Microsoft tools.

I’ve never attended Build in person before because the data part of the event tends to be fairly thin, and the releases for Azure SQL DB and SQL Server aren’t usually tied to Build’s dates. This year, it’s a hybrid event, both in-person in Seattle and online.

I’m at home in Vegas, attending virtually, and I’ll live-blog the keynote. Refresh this page starting at 9AM Pacific, noon Eastern, to see my thoughts on news as it comes out.

The first bits of news are already starting to trickle out: this morning, the SSMS release notes were updated to mention Microsoft Fabric SQL Endpoint and Fabric Data Warehouse. Yes, those would be new products, and yes, Microsoft already has something called Fabric, but this is different. If you’re bored before the keynote, you can go through this morning’s Github checkin for the Build event.

You can join me, but to do it, you’ll need a free registration for Build, so head over there now before the keynote starts at 9AM Pacific.

8:45AM: Based on this morning’s check-ins, looks like they’ll be announcing Hyperscale databases in elastic pools. Each Hyperscale elastic pool supports up to 25 databases on standard series hardware, max 100TB data in the pool. Still stuck at 100 MB/sec throughput on the log file though, and even worse, it maxes out at 130MB/sec across the entire pool.

8:49AM: From the new documentation: Fabric Data Warehouse “provides two distinct data warehousing experiences. Each Lakehouse automatically includes a SQL Endpoint to enable data engineers to access a relational layer on top of physical data in the Lakehouse, thanks to automatic schema discovery. A Synapse Data Warehouse or Fabric Warehouse provides a ‘traditional’ data warehouse and supports the full transactional T-SQL capabilities you would expect from an enterprise data warehouse. Either data warehousing experience exposes data to analysis and reporting tools using T-SQL/TDS end-point.”

8:54AM: From the documentation update list: “Optimized locking available in Hyperscale – Optimized locking is a new Database Engine capability that offers an improved locking mechanism that reduces lock memory consumption and blocking amongst concurrent transactions. This fundamentally improves concurrency and lowers lock memory. Optimized locking is now available in all DTU and vCore service tiers, including provisioned and serverless.”

8:58AM: Analysis thoughts on reading the Github checkins so far: this looks like yet another iteration of Microsoft’s data warehousing strategy that just can’t maintain focus for 3 years straight. From DATAllegro to Parallel Data Warehouse to Hadoop to Analytics Platform System to Azure SQL Data Warehouse to Azure Synapse Analytics to Big Data Clusters, there’s something broken about the leadership vision here. I feel sorry for folks who have to sell Microsoft data warehousing with a straight face: before the deployment finishes, the product’s already been “reinvented” again.

At the same time, I’m also so happy to be working in the relational database space. The language is stable, the product is stable, and I don’t have to tell clients to keep changing the way they access the database. Thank goodness for that.

9:05AM: Hmm, I thought the keynote started at 9, but they’re still running promo videos. Hmm.

9:09AM: Okay, I think this is actually supposed to be the keynote – they’re showing videos of people interacting with AI.

9:10AM: Satya Nadella took the stage and talked about his first Microsoft developer conference. He flashed back through big moments in computer history like The Mother of All Demos, the PC, client/server computing, etc. “All of this has been one continuous journey.” And a hell of a ride it’s been so far.

9:13AM: Satya called ChatGPT’s launch the Mosaic moment of this generation. I think that’s fair, but I had to chuckle – few people remember Mosaic. It was an early thing that’s long since been discarded by the wayside. If that happens to OpenAI, Microsoft is gonna be pissed about their multi-billion investment.

9:15AM: “We’re gonna have 50+ announcements, but I want to highlight 5 of them.”

  1. Bringing Bing to ChatGPT. (No claps.) Satya: “You can clap.” (Claps, awkward)
  2. Windows Copilot. I don’t think Cortana ever did that well on Windows desktops – at least, I never see anybody using it – so it makes sense to throw something else at it instead. For corporate PCs with security lockdown, this gives Microsoft another O365 revenue stream, because I’m sure they’ll offer a “secure” Copilot that doesn’t use your documents for training.
  3. Copilot stack. So other folks can build Copilot for their own infrastructure using Microsoft’s models and AI infrastructure. Totally makes sense given Microsoft’s developer focus – if they can make this easy in Visual Studio, then it stands a chance. I was just horrified by the demo, though: using Copilot in Office, taking legal advice from ChatGPT in Word. I can’t imagine how that might backfire. (Who the hell thought this was a good idea for a demo?!?)
  4. Azure AI Safety. Testing, provenance, and deployment.
  5. Microsoft Fabric. “The biggest data product announcement since SQL Server.” Unified storage and compute, unified experience, unified governance, and unified business model.

9:32AM: Microsoft Fabric looks like a data lake where you have a team who governs what goes in & out, regulates the schema and security, tracks the data lineage, and curates the data model. So, uh, a data warehouse?

9:35AM: Satya’s doing human storytelling, so I’ll focus on Fabric for a second here. Fabric is a story about what happens when your data is well-controlled. That was the story of the data warehouse 20 years ago: it solved exactly the same pain points. Data warehouses fell out of favor because there was too much data, changing too quickly, and the tools changed too quickly.

Data lakes became popular because people wanted to just dump the data somewhere and figure things out later. Over time, that ran into the same problems that we used to have before data warehouses: the data wasn’t reliable, we didn’t know where it came from, the changes kept breaking reports, etc. So now, Microsoft Fabric is fixing the same problem with data lakes that data warehouses fixed with scattered relational databases.

Will it catch on? Maybe – data warehouses did – but you can fast forward and see what’s going to happen when Fabric is popular. Users will say, “I have this extra data that I need to join to my reports right now, and I don’t have the time to wait for the Microsoft Fabric admins to bring it in, so I’m just going to put it in this one place for now…”

And we’re right back where we started. Okay. If your company couldn’t fix the data warehouse’s problem, and they added data lakes, and they couldn’t fix those problems, so now they’re implementing Microsoft Fabric… I’m just gonna say maybe the problem isn’t the product you’re using.

Does that mean Fabric is a bad product? Not at all – it might be great – but it’s definitely not something I’m going to pursue.

9:40AM: Kevin Scott, CTO & EVP of AI at Microsoft, took the stage to talk about the era of the AI copilot for the next half-hour. That’s a great topic, but it’s not really my jam, so I’m going to stop the live blog here. Right now, Build’s session catalog doesn’t have any Fabric sessions, but I wouldn’t be surprised if sessions got added over the next hour or two. I’m not going to dig more deeply into there either.

Update: Optimistic Afternoon Thoughts

When I walked away from the computer and emptied the dishwasher (true story), I realized I wasn’t being completely fair to Fabric. There are companies who:

  1. Successfully implemented a secure, well-documented, rigid data warehouse, and
  2. Who also implemented Azure Data Lakes later, and
  3. Now want to control those lakes the same way they control their data warehouse

And for companies like that, Fabric makes a lot of sense. I don’t have a sense for how big or small that market is today, but I’m sure it’s out there – it’s the kind of thing Microsoft BI consultants would facilitate.

I think this also plays to Microsoft’s strengths: they control the cloud, the most common relational databases, the reporting tools, and the development tools. You could make an argument that Fabric stitches those pieces together in a way that Amazon and Google won’t be able to do for years, if ever. (Sure, AWS has Redshift, but that’s just a persistence layer – Microsoft is trying to argue that Fabric is a cohesive unit that brings it all together.)

Paul Turley’s a BI pro who specializes in the kind of market that Fabric services, and he has a quick summary here, plus a list of learning resources. Note the number of different tools involved in his posts and the links – Fabric isn’t just “one thing”, it’s a brand name for a whole bunch of moving parts that have been flying under different brand names over the last few years. Fabric feels like the latest brand name and vision – and that’s where I get nervous, seeing how Microsoft keeps reassembling these parts into different things.

[Video] Office Hours: Azure & SQL Server Q&A

Brent Ozar
Videos
0

Y’all post questions at https://pollgab.com/room/brento, and I go through the top-voted ones on my Twitch channel streams.

 

Here’s what we covered today:

  • 00:00 Start
  • 01:56 Manoj: Where do you see artificial intelligence having the most impact on DBA’s?
  • 03:25 It’s ‘a me: Hi Brent, is it still “best practice” to have databases split into multiple mdf files in the days of SSDs? Especially since the files live on the same SSD
  • 04:57 Mr. SqlSeeks: Is it an accurate assumption that as SQL Server cardinality estimates improve, the query engine gets more aggressive in using those estimates to build plans, which is why some queries are awful once you change the compat level? Were the previous engine versions more forgiving?
  • 08:03 StillLearning: Hi Brent, I’m trying to understand why so many people think that using partitions with SQL Server will improve the performance of their queries. I don’t know much about Oracle, but It seems that Oracle partitions can improve some queries. Could this belief come from there?
  • 09:50 Eduardo: Should you purchase third party monitoring software for Azure SQL DB?
  • 12:24 Malmo: What’s your opinion of simultaneously using both bitlocker and SQL TDE to protect SQL data?
  • 13:23 marcus-the-german: Hi Brent, let’s say I have a AG, which is setup correctly to read the data from the secondary (the routing is correct, not the network routing ;-)), the app is configured to read from the secondary. If so, should I see the select statements in the secondary’s activity monitor?
  • 14:41 Stimpy: What are the top challenges that versionless SQL server poses to the first responder kit?
  • 16:48 Janus: See lots of videos for SQL on linux but does this feature really get used all that much?
  • 17:26 Leif: On a really wide table where users search on every column, will it be best to use column store index? Or will tables with a lot of rows gain the most from CS index?
  • 19:52 Going_Parallel: Brent, How do you best prevent yourself from “burning out”, and recognizing when you need to take a step back? Relevant for all job, but certainly in our field.
  • 23:25 Nortzi: Hi Brent. I’ve heard from you and from others that it’s a really bad idea to shrink your data files. Other than causing index fragmentation are there other compelling reasons not to do this?
  • 24:30 David Singleton: Hi Brent, thanks for sharing, your recorded classes are a bargain and have saved my butt many times now. Is there some way in an SSMS query to tell it which SQL server to connect to? Something like the SERVER equivalent of how USE works to tell it which DATABASE to use.
  • 28:07 m2devdotnet: Random, non-SQL related question – do you have any pictures of your office layout? We’re moving into our new house and I like your setup and curious what the overall layout is
  • 30:53 Y?mò: How should DBAs describe what they do for a living to non technical peeps?

[Video] Office Hours: Professional Development & Training Questions

Brent Ozar
Videos
0

A lot of today’s questions from https://pollgab.com/room/brento have a common theme, but it’s just a coincidence:

Here’s what we covered:

  • 00:00 Start
  • 07:40 Maciej: Hi Brent, when you are working on the course/training how do you know that the current version is “good enough”? I am asking because my friend has a tendency to spend an enormous amount of time (or even worse – not finishing it) when he is working to prepare a presentation?
  • 10:22 Alex: I need to upgrade SQL VMs (SQL16 Compt Lvl 14). I want to upgrade to SQL22 and keep CL as is. My manager doesn’t want to make too many changes at once (stay on 16), upgrade is complex and I think that if we don’t move to 2022 now we will never do. Does it worth the fight?
  • 12:07 gringomalbec: Hi Brent, In my opinion SQL Server really shines when it comes to Columnstore Indexes. And this is not my friend’s opinion – you know what I mean 🙂 I’m familiar with your Fundamentals as well as Nico blog site. But is there any chance that you write more blog posts about it ?
  • 13:47 Champaign DBA: The dev team has created new pages in an app where every column of a display can be filtered/sorted. There are only 10 columns, but that’s 1024 index combos. Other than guesses based on selectivity and using the DEATH method post release, any tips on indexing this nightmare?
  • 17:05 GuaroSQL: I have a db with 1.5 Tb of data, 1.2 Tb of the size is just one table, it is possible store that table in another file group and try to restart that table? I can’t delete data from there
  • 18:18 Ron Howe: Is there any way to live-debug a SQL Server that is 100% CPU throttled due to bad query plan (MAXDOP) such that you can’t get a connection to it?
  • 19:16 ALSO_I_ALSO_WANT_A_FERRARI: Hello, I’m from Eastern Europe and work as a SQL Developer. I am looking for ways to get a job in either US or UK, where a junior’s salary is double to what a mid-level developer is getting here. I would be happy to get advice on where to look, and how to get such a job. Thanks!
  • 21:53 Mark M: I recently started at a new place (6 months ago) been working digging thru their DB’s ,, and have found they have like 10 views that just pull from openrowset. Seems like static data, but I am worried about security,, what are your thoughts?
  • 22:42 Piotr: You mentioned Qwest Toad for SQL Server. Besides Service Broker configuration, what things do you feel it does better than SSMS?
  • 23:13 Miguel: Is manager bias against remote DBA’s a legit concern? If so, how should you deal with it?
  • 25:04 Anatoli: What are the pros / cons of running SQL business logic in a SQL Agent Job vs a scheduled Windows task? Who should have access to create new agent jobs?
  • 28:04 Magnús: Enjoy your intro music, what genre is that? Where do you get it?
  • 29:09 Janus: Which blog engines have you used? What is your favorite?
  • 29:19 Mr. M: Hi, Will Azure SQL put DBAs out of jobs?
  • 31:16 The Pink Poodle: What white boarding tool do you like to use with remote clients?
  • 31:53 gserdijn: Hello Brent, can you tell our friends and me what the most important differences between running a database in SQL Server 2022 with Compatlevel 2019 and running a database in SQL Server 2019 are? We want to upgrade from 2017 but are not comfortable with 2022 yet.
  • 33:21 gotqn: Any risk of using master database objects like [master]..[spt_values] in production routines?
  • 34:03 Sean C: Hi Brent, after my friend broke up a query into two using a union all (accounting for them dang NULLs) so it is more sargable, they are seeing a distinct sort operator before the last concatenation and select. There is no order by and no distinct in the query. Any insight?
  • 37:15 Eduardo: Do you have any recommended tools for comparing two SQL Server instances (not DB’s) to quickly show where they are not configured identically (configuration drift)?
  • 38:08 Dean: If someone wanted to become the Brent Ozar for Postgresql what advice would you give them.

[Video] Office Hours: 45 Minutes of SQL Server Q&A

Brent Ozar
Videos
3 Comments

It’s a long one, folks! I went through almost 30 of your questions from https://pollgab.com/room/brento to get your learn on:

 

Here’s what we covered:

  • 00:00 Start
  • 00:30 Jr Wannabe DBA: Hi Brent, recently you talked about linters; do you recommend any for T-SQL? I tried a few randomly from Google search, they are useless.
  • 04:55 Rajiv: What is the best way to read JSON in sql?
  • 06:07 not Kevin Mitnick: (I ask all my colleagues do your courses) What percentage of your clients use TDE vs Always Encrypted vs Nothing? Except your survey, there seems to be no public data on usage. I have seen hacks that demonstrate how to crack TDE. Always Encrypted seems the right way to go.
  • 06:56 ProochingMan: Is your Office Hours podcast still available anywhere? I used to enjoy listening to you, Kendra, and others discuss SQL Server – including older episodes. I haven’t been able to stream/download it with me podcast aggregator for a while, and I am not finding them on your site.
  • 07:46 Benji: What is your opinion of using Azure SMB File Shares to host SQL Server data files?
  • 09:02 Don’t Be Afraid: Hi Brent, maybe a loaded question that you will rip into me for. But how bad is spilling to tempdb for sorts? Not having any issues, just looking to understand! Thank you!
  • 11:36 Eduardo: Is the cost / complexity of SQL DAG vs AG ever worth it? Do you get to work on DAG with your clients?
  • 16:10 marcus-the-german: Hi Brent, I have a table A with a nvarchar(255) column. It’s filled with data. If I query the data type I see a max_length of 510 bytes in the sys.columns table for this column in my table A. What’s the big deal about it?
  • 16:45 Dollop: What’s a good book to learn query tuning ?
  • 18:02 carpediem: Hello, do you have some book or link recommendations for implementing Hit-Highlighting with fulltext search?
  • 19:08 Vishnu: What is your opinion of agreeing to automatically send SQL mini dumps to Microsoft?
  • 20:26 Piotr: Is it ok to patch SQL Cumulative Updates and Windows Updates at the same time or should they be done separately?
  • 21:41 reporting_developer_dba: Can modifying indexes and using date as a first column will be advantages so we can use between to pull data in range vs reading pages by ID’s?
  • 22:20 Benji: What is your opinion of third party HADR solutions for SQL Server such as SIOS DataKeeper?
  • 24:23 Mariángel: Have you ever seen Windows filter drivers cause data corruption in SQL Server? What are your tips for preventing data corruption from filter drivers?
  • 25:07 Boris: It is easy / hard working in both SQL Server and PostgreSQL?
  • 27:10 TJ: Hi Brent, how would you go about troubleshooting a query that runs forever and you can’t get its actual execution plan?
  • 29:35 TY: Hi, do you think that performance in SQL Server can be faster for one-time executions: If the engine takes time to write logs, executions plans or any other fancy stuff – is there a way to turn OFF
  • 31:35 Wren: Really hard question to google… If you have an AG-enabled server with multiple dbs on it in a WFC, do all of the dbs have to be in the AG to failover for patching, etc? Is there any reason to not put a db into an AG on an AG-enabled server?
  • 33:20 accidental_dba: what happens if run 2 instance on a 256gb RAM sever with standard edition. Does each instance get its own 128gb under 1 license and divide the cores between those
  • 34:08 SQLrage: I took your advice and tested this and found that updating all statistics with full scan alone on all tables referenced in a proc did not cause the plan to be regenerated.
  • 36:18 hammered_by_manager_to_work_on_sata_drives: if 2 similar procedures or queries run from 2 different databases, will SQL server keep plans per database or as global?
  • 37:30 Vishnu: What is your opinion of SQL ledger tables? Is this another service broker from M$ft?
  • 39:15 Sasan: Hi Brent, In your mastering class you say that it does not really matter for an equality search which leading key is indexed first. While I have found this to be true in terms of the number of reads, it could produce plans with different estimates. Any thoughts?
  • 39:45 Red Dog: What are your memories / experiences of SQL Data Services from 2008?
  • 40:30 AK: Hi Brent, When is the next sale for your courses besides the black friday one? Just fyi I did google it but I did not find the answer.
  • 42:02 Magnús: We see forward fetches from a sproc that makes heavy use of tempdb. it inserts into and update a local temp temp table before finally returning the reporting results. Is there a forward fetches threshold at which we should be concerned about performance (thousands, millions)?
  • 42:54 Programmer: What’s a reasonable progression for deploying a production database for a new project (assume negligible budget to start) as it grows for a team that shies away from managed/closed services but doesn’t have expertise in managing databases?
  • 44:07 Gabriele: if my friend had a work proposal for working some days (5-10) a month on a 24/7 on call ready schedule, what suggestion will you give him?

It’s Been 6 Months. SQL Server 2022 Still Isn’t Ready Yet. (Updated)

Brent Ozar
SQL Server 2022
32 Comments

Six months ago today, Microsoft announced that SQL Server 2022 was ready.

Except it wasn’t.

And it still isn’t ready.

See, look at the very first hero in their list of 2022’s new features:

The very first one is “Business continuity through Azure – Bidirectional DR to Azure SQL.” However, buried way down in the footnotes, Microsoft admitted:

The bidirectional disaster recovery capability of the Link feature for Azure SQL Managed Instance is available in limited public preview. Sign up for early access. General availability will occur at a future date.

Well, it’s been 6 months now, and that feature is still only in limited “public” preview, and by “public” I mean you have to apply through a form so the product group can onboard you. The form has some interesting questions that insinuate this thing still needs some baking, like asking how much time per week that you can work with the product group, and asks you to assess your skills:

And at the end of the form:

I applied on April 26th out of curiosity. I haven’t heard a word back.

You might say, “Well, that’s just one of the features – the rest are ready, right?” I’d disagree – the second hero feature was Azure Synapse Link, and the list of current limitations for that is horrific.

  • The table can’t use CDC, Always Encrypted, columnstore, etc
  • The database can’t have transactional replication
  • The database can’t be involved in Azure SQL Managed Instance Link – meaning the very first two hero features can’t even work together

Should you install SQL Server 2022? I think as long as you stay clear of the new features, you’re fine. The new version gets you query performance improvements (especially for Enterprise Edition users) and longer supportability.

But if you want to use these new features, SQL Server 2022 just still isn’t ready.

So next week at Microsoft Build, when you hear folks making grand announcements of upcoming features, just remember that they still haven’t delivered on last year’s “released” features! Take announcements with a grain of salt: until it’s actually in your hands, it’s vaporware. It bums me out to have to say that out loud, but here we are.

Update 2022-05-17: another not-done feature

A reader pointed out to me that Query Store on readable replicas was supposed to be a SQL Server 2022 feature, and that one’s still not done yet after 6 months, either, as the documentation points out:

Update 2023-11-18: still not ready.

At the Microsoft Ignite and PASS Data Community Summit conferences, Microsoft proudly announced that the online disaster recovery between SQL Server 2022 and Managed Instances is getting closer – but… it’s still only in preview. It’s amazing to me that even a year after its release, SQL Server 2022’s flagship feature still isn’t ready yet.

Needless to say, Microsoft didn’t unveil anything about vNext at those conferences – because they can’t. They’re still working on getting SQL Server 2022 out the door.

Here’s to 2024 being the year that SQL Server 2022 is finally ready.

How to Configure Ola Hallengren’s Database Maintenance Scripts for Backups

Brent Ozar
Backup and Recovery
17 Comments

Someone told you that you should be using Ola Hallengren’s Maintenance Scripts. You’ve downloaded his Maintenance Solution.sql, you ran it in the master database, and … now you’re not sure what’s supposed to happen next. In this post, we’ll cover how to configure backups.

First, configure the Agent jobs.

When MaintenanceSolution.sql ran, it created a few Agent jobs. You can see them in SSMS or Azure Data Studio under SQL Server Agent, Jobs:

Ola created those jobs, but didn’t configure or schedule them, because they need information specific to your environment. In this post, we’re only going to focus on the DatabaseBackup jobs.

We’ll start with DatabaseBackup – SYSTEM_DATABASES – FULL. This job backs up your master, model, and msdb databases, which sounds really simple, but it’s a good place to start because even a job this simple needs configuration. Right-click on the job, click Properties, Steps, then click on the only step in the job and click Edit.

Here’s what the job does by default:

A couple of those parameters need explanation and configuration.

@Directory = NULL means that Ola uses the default backup path for your server. To see what that is, right-click on your server in SSMS or ADS, go into Properties, Database Settings, and it’s the “Database default locations” here:

That backup path may not be what you want, so change it at the server level in this screenshot, not in Ola’s jobs. Just change it once at the server level, and it’ll take effect everywhere in any script that doesn’t specify where the backups should go.

Side note – I prefer writing backups to a UNC path, like \\backuptarget\foldername, rather than local storage, and here’s why.

While you’re in that screen, look up just a little, and you’ll see two other checkboxes:

  • Compress Backup – check that so your backups run faster, and
  • Backup Checksum – check that so SQL Server does a little bit of corruption checking while it’s doing the backups (this won’t slow your backups down)

Once you’ve configured those server-level settings, let’s go back to that Ola job step. Leave the @Directory parameter null, and it’ll inherit the server-level backup path. Next parameter…

@Verify = ‘Y’ means that after the database backup finishes, SQL Server will do a test restore to make sure the backup file is valid. That sounds good, but it makes your backup jobs take way longer, and it hurts performance of other stuff while it runs. If you want to verify your backups, you’re much better off restoring them onto a different server. Me, I recommend changing this to @Verify = ‘N’. You can edit it right there onscreen.

@CleanupTime = NULL means that this job won’t delete old backup files. If you would like old backups deleted, replace NULL with the number of hours for older files to be deleted. (3 days is 72 hours, 1 week is 168 hours. You’re welcome. I suck at math too.)

We’re done configuring the step. Click OK, then click Notifications, and it’ll look like this:

I dunno about you, but I wanna know when things go bump in the night, so click the Email checkbox and choose the group that you want to be notified when the job fails. If that dropdown is empty, here’s how to configure database mail. Note that you’ll need to do that, then restart SQL Server Agent, before you can come back to finish configuring these scripts.

Last up, click Schedules. You’ll notice there’s no schedule:

It’s up to you to define the schedules for each job. Click New. If you want them backed up every night at 8PM, for example, here’s how you’d set up that screen:

Don’t schedule all backups simultaneously.

Folks seem to default all of their severs to run backups at midnight.

That’s a terrible idea.

Because then at 23:59:59, all of your servers are staring at their Apple Watches, waiting for the exact moment to simultaneously bum-rush the storage and the network. The lights in the datacenter will suddenly dim, fans will scream, and all of the servers will do a distributed denial of service attack on themselves.

Instead, stagger your schedules out as much as you can. If you’re reading this post to learn this topic, then you have four jobs that likely want to run every night: system database backups, user database backups, index optimization, and integrity checks. Those four jobs, across all your servers, should be spread out as much as possible.

The good news is that the system database full backups will be super quick because the system databases are small. Just pick a time when user activity has tapered off for the day. Odds are, you’re never going to need to restore a system database, so I’m less concerned with the exact time of day on these. (Plus, we can’t do transaction log backups on these.) The biggest reason I back these databases up is that people accidentally create objects in the system databases, and then ask me to restore ’em.

After configuring the job’s schedule, click OK, and we’re done configuring the system backups!

OMG, that was a lot of work.
Next up, the user database backups.

The user database jobs are a little more complex. There are separate jobs and schedules for full, differential, and transaction log backups. I’m not going to cover the differences between those here, but I am going to talk about the most common parameters that you’ll want to set on each of the jobs. This is above and beyond the parameters we discussed above, which also need to be set on user database jobs.

Ola’s DatabaseBackup stored proc has a lot of parameters that don’t show up in the default jobs, but I think you should set them.

@ChangeBackupType = ‘Y’ should be added to your list of parameters. What this does is if a brand new database gets added to your server midday, and your transaction log backup job runs, the log backup job will say, “Hey, I can’t do a log backup because there hasn’t been a full backup of this brand new database yet – so I’m going to change the backup type to full for this database.” This gets you coverage right from the get-go when new databases are created in full recovery model.

@NumberOfFiles = 4 should be added for performance on the full backup job. SQL Server backups go faster when they’re striped across multiple files due to SQL Server’s own internal limitations, nothing to do with your storage. If you want to spend time performance tuning, you may find that 6 or 8 backup files might even improve performance more, but for starters, let’s at least do 4 files. It doesn’t require any more work on your part, even at restore time, because you’re going to use DatabaseRestore, an automated tool, for your restores.

@MinBackupSizeForMultipleFiles = 10000 should be added so that we only mess with multiple backup files when the database is 10GB or larger. Smaller than that, I’m not really concerned about backup performance – 1 file should be fine.

There are many, many more backup parameters you can set for things like encryption, third party backup software, parallel backup jobs, and performance tuning, but those 3 are the most important, and the ones I usually set everywhere. So here’s what my “full” step looks like, for example:

Schedule these backup jobs as well – typically once a day for the full backup job, and every 5 minutes (or every minute, really) for the log backup job.

Finally, test the backups by running a restore.

After the backup jobs run, you’ll end up with files organized in this folder structure:
\ServerName\DatabaseName\FULL
\ServerName\DatabaseName\DIFF
\ServerName\DatabaseName\LOG

That might seem awkward if you’re used to using the GUI to point-and-click your way through database restores. Fortunately, there’s a really easy solution for that: sp_DatabaseRestore, a free stored procedure in our open source First Responder Kit. To restore a database to the most recent point-in-time, you can run:

That’ll automatically pull the most recent full backup from that folder, plus all the transaction logs since, and restore them in order. Easy, right? Super helpful when you want to keep a development or QA server up to date. There are more parameters for things like differential backups, moving data files, running CHECKDB, and more – for those, hit the documentation.

One of the many reasons why you’ll want to back up directly to a network path is so that you can run restores from other servers without slowing down production. Test your restores on other environments, like your DR or dev/QA/test environments, and you’ll know with confidence that your newly created backup jobs are working well.

For support questions on how the scripts work, visit the #SQLhelp channel in the SQL Server community Slack – invites available here – or post questions on DBA.StackExchange.com or the SQLServerCentral forums. Do not leave support questions here in the comments – I’ll simply ignore them since you didn’t read the post through to the end. However, if you’ve got questions about the parameters or process above, feel free to leave those and I’ll work through ’em.

[Video] Office Hours: Testing the First Responder Kit

Brent Ozar
Videos
0

I tested the April release of the FRK, then answered your questions from https://pollgab.com/room/brento.

 

Here’s what we covered:

  • 00:00 Start
  • 28:37 Make_a_car_and_call_it_a_Brentley: Hi Brent, thanks for everything you do for the community. I was wondering on your opinion on using RegEx within a SQLquery and it’s performance. We have queries where we analyse about 2 million rows with usually a good 30-50 %something% values to search for.
  • 30:18 Koritt: Hi Brent. DBCC CHECKDB runs CHECKALLOC, CHECKTABLE, and CHECKCATALOG against a DB. Would running each call separately (eg: to spread CHECKTABLE over time for large DBs) give the same level of validation as CHECKDB, or does CHECKDB do additional checks the individual calls do not?
  • 31:42 Piotr: For patching purposes, is it safe to simply reboot a SQL Server FCI after patching or should you always shut down the SQL Server agent and SQL Server instance prior to rebooting? Hoping to avoid data corruption.
  • 32:44 Fellow Early Riser: Does your early to rise cause early to bed? Have you always been an early riser?
  • 33:20 Tom: Hi Brent, Have you ever encountered a database that hasn’t been designed properly and wasn’t normalized? If you have, how did you deal / react to the situation?
  • 35:00 Carnac: Enjoyed watching you evaluate each DBA’s top SQL wait stats. Can we look forward to a future episode of this?
  • 35:33 Benji: Is there consideration for bringing constant care to PostgreSQL? What is the hassle / reward ratio for this?
  • 37:16 TJ: We’re experiencing constant deadlocks with xp_userlock calls within sp_getApplock. Do you have recommendations for how to improve the deadlocks?
  • 38:59 Jon: How was Iceland?

Free Webcast Next Week! What’s New (and Actually Good) in SQL Server 2022

Brent Ozar
SQL Server 2022
7 Comments

It’s May 2023, and believe it or not, some of the flagship features in Microsoft’s latest version still aren’t ready for prime time yet. Good news, though: there are a few things that genuinely make your life easier with minimal work required on your part.

Forget rewriting code, though – in this session, I’ll show what to turn on to determine:

  • When to migrate to SQL Server 2022
  • What metrics to watch
  • Which dials to turn to make SQL Server faster

The first 200 folks to register and attend will get a $10 gift card for lunch, and participants who attend and participate will be entered into a random drawing for a JBL Clip speaker. The webcast will also be recorded, and registrants will be emailed the link to the recording.

Register here.

[Video] Office Hours: 25 Pretty Good Questions

Brent Ozar
Videos
1 Comment

You post questions at https://pollgab.com/room/brento and upvote the ones you’d like to see, and my job is to come up with accurate answers on the fly. Let’s see how I did.

Here’s what we discussed:

  • 00:00 Start
  • 03:25 SickOf: Brent is there a backup product you can recommend?
  • 04:38 OneEyebrowRaised: I’m noticing three significant shortfalls in Always On: 1) Login synchronization is manual, 2) Scheduled Jobs synchronization is all manual, 3) stored procedures put in the system databases aren’t shared across nodes. Do you know of any tools to address these shortcomings?
  • 06:11 Sean C: Hi Brent, TIA for roasting me lol. We use dynamic SQL to loop through 200+ cols to validate against a set of specs, resulting in a lot of plans being painted when tuning. Is there a way to suppress benign exec plans like looping through commands, etc to reduce bloat?
  • 07:47 I’ll be BacH: Is Data Modeler still a career path? Or has that merged into Database Developer? Do you see any sub-specialties in the Database Developer career field?
  • 10:05 Doug: Do folks ask easily Googled questions intentionally?
  • 11:11 Isaac Wahnon: You answered Rojo about using Distributed AGs for version upgrades, saying “No because it’s so much work to set up.”We want to upgrade from 2019 to 22,What upgrade procedure can reduce risk and downtime for a system with AGs and Distributed AGs? In-place or replace.
  • 12:27 macfergusson: Hey Brent, in one of your courses you mention using GUID PKs and clustered indexes, they aren’t nearly the bad choice that a lot of DBAs make it out to be. If you do go this route, are there any different best practices that you recommend? Fillfactor, extra memory?
  • 13:11 JediMindGorilla: Hi Brent. I always tell people “I am not the guy that can code a cup of coffee from 0, I am the guy that makes it taste better”… is it common to have people looking for SQL jobs that may not have as much “coding” experience (code from scratch), but are good at performance?
  • 15:15 Sammy: An architect I know likes to default every Primary Key INT with -2,147,483,648 or BIGINT and its lowest to avoid resizing. Brilliant or needlessly clever?
  • 15:57 Accidental_dba: Can we run multiple backups on a single server as we have 100+ databases? Currently we use olaha scripts. Currently backups starts at 12am and finishes around 7am which is when all our stores open? Trying to make backups finishes by 5am
  • 18:18 Just_Winging_It: Brent, In your experience, when you rebuild indexes and fragmentation is still present, what are the usual suspects to check? Please feel free to roast me.
  • 19:14 gjocarroll (George): Hi Brent, have you encountered any new entry into your Top 5 Waitstats/issues in the last few years/SQL Server versions?
  • 20:35 Universe For Rent: I’m often tasked to review SQL code that’s about to be pushed to production.
  • 22:04 ArchibeaR: Been working with SQL Server since 6.5…. I know i’m old. Working with a new team building Azure setup from the beginning. Wondering if you have some suggestions on reading to get upto speed.
  • 22:46 Developer Who Cosplays As a DBA: I recently ran into compilation timeouts in a prod database that caused web pages to time out, and I’d love to put together a demo of comp. timeouts for educational purposes. Do you have any tips for how you would go about intentionally writing a query with a long compile time?
  • 23:45 Ive_Got_Heaps: Hypothetical: Brent decides to sell all of his cars (see hypothetical) to fund a new database engine. What are your dream bells and whistles? Sub question, when can I invest?
  • 25:18 Piotr: What’s the largest server you have seen log shipped? Any issues with shipping large servers?
  • 27:15 core: hey Brent! Is there any easy way to detect SQL Server Agent missed jobs? For example, if the SQL Server Agent is stopped due to an issue with the service, server, or planned maintenance. thanks!
  • 27:14 Guilty DBA: Hi Brent, is there any way to configure the querystore (QS) to handle identical Pans more efficiently ?
  • 29:21 Yevgeny: How far back in SQL versions does office hours go back? Has the format / content / hairstyle of office hours changed much since then?
  • 30:54 Haydar: Is there a good way to programmatically obtain the query hash / query plan hash of a query after execution for auditing purposes?
  • 31:40 Eduardo: How do you recommend implementing sproc debug logging when the sproc could be running on AG primary or AG readonly secondary node?
  • 32:40 Wren: Hi Brent! Do you think the noted 2019 query/performance slowdown might patched any time “soon” (next year or so)? Or is it just too “baked in” to the version?
  • 34:25 Jessica: Hey Brent, I’m going to be in Vegas for a tech conference in about a month. What’s a good local restaurant or coffee shop that are just amazing to go to that are away from the “tourist” areas
  • 35:58 GP Geek: How do you get more than 8k or 4k into an NVARCHAR or VARCHAR variable? I’ve had a recent case where the limit was reached without the user noticing it and missing data

Happy 21st Anniversary, BrentOzar.com. What’s Coming Next?

Brent Ozar
Company News
18 Comments

It’s pretty cool to look at a blog post and see, “Last updated: 21 years ago.”

It’s pretty fun to see old pictures of me in the media library, too.

When I first started blogging over two decades ago, it wasn’t a business. I just did it because I enjoyed writing, sharing, and being part of an online community. It was a fun outlet, a way to contribute something in my spare time, and a method to record my life in a way that might enable me to look back later and see what I’d been doing years ago.

For example, 20 years ago:

By 15 years ago, I had already started to focus mostly on SQL Server:

By 10 years ago, BrentOzar.com was a full-fledged business. The blog posts not only targeted tech exclusively, but the post titles were now aware of the importance of search engine optimization:

By 5 years ago, we were deeply focused on technical SQL Server issues, but also covered technologies relevant to the DBA space:

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

Over the last few years, during the pandemic and up to today, I focused a lot of the blog content on live streams & recorded videos.

I wanted to give people a community where they could see a friendly face – even when the world wasn’t open for business, and faces were covered with masks. Database work is often lonely because so many of us are the only person in our company who does what we do, and the pandemic and remote work only made those things tougher. Many of the data professionals I know are struggling with burnout and loneliness.

Those of us who’ve been lucky enough to be around other people again, whether it’s work-related stuff or friends, have started to recover. Free regional in-person events like SQL Saturdays and Data Saturdays are starting to come back to life, and big ones like SQLBits and the PASS Summit offer hope that we’ll be able to do family reunions on a more regular basis again.

However, the data community is like a river: you can never step in the same river twice. Not only have the places and ways we meet up changed, but the members of the community have changed, too. Over a decade, many of us transition to different adjacent technologies, different lines of work, or switch to management.

I took December-February off to step back and think about what I personally wanted to do next, too. Was it time for me to transition? Had I done everything there was to do in Microsoft’s relational database engine, and was it time to move on? (I certainly don’t know everything in Microsoft’s data stack altogether – the product list is huge, as is the depth of each product.)

I came to the conclusion that it was time to go back to the start, and take a fresh look at Azure SQL DB and SQL Server. The products are still widely used, and every day, more people start using them for the first time. I’m not aiming to teach new things to folks who’ve read the blog for the last 20 years – but rather, teach things in fun, friendly new ways, helping people solve database problems faster.

Updated First Responder Kit and Consultant Toolkit for April 2023

Brent Ozar
First Responder Kit Updates
3 Comments

This month’s big changes are performance tuning in sp_BlitzFirst & sp_BlitzLock.

Part of the benefits of using the open source FRK is that when any of us work with *really* big/fast/ugly servers, we tune the FRK procs to work better in those environments – which means it’ll likely work better in yours, too. For example, this month I was working with a server doing 30k-35k queries/sec and hitting threadpool issues, and I wanted sp_BlitzFirst to return more quickly in that kind of environment, so I tuned it.

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

To get the new version:

Consultant Toolkit Changes

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

sp_Blitz Changes

sp_BlitzFirst Changes

  • Enhancement: new @OutputResultSets parameter lets you return less result sets when you’re in a hurry. (#3255)
  • Enhancement: performance tuning for faster response time on systems with thousands of simultaneous active queries. (#3257)
  • Fix: no more arithmetic overflow on queries with horrific row estimates. (#3241, thanks SQLLambert.)
  • Fix: remove @@ROWCOUNT to avoid problems with In-Memory OLTP. (#3237)
  • Fix: only alert on bad cardinality estimations for queries that run > 5 seconds. (#3253)

sp_BlitzLock Changes

sp_BlitzWho Changes

  • Fix: no more date errors when a request’s start date is 1900. (#3243, thanks Jeff Mosu.)

sp_DatabaseRestore Changes

  • Enhancement: new @FileExtensionDiff parameter for folks who want to name their differential backup extensions different than Ola’s defaults. (#3234, thanks Will Spurgeon.)

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.

How to Go Live on SQL Server 2022

Brent Ozar
SQL Server 2022
7 Comments

You’re planning to migrate to SQL Server 2022, and you want your databases to be faster after the migration.

This is NOT a blog post about how to migrate – that’s the same as it’s been for a long time. Go build the new 2022 servers, and use log shipping or database mirroring to sync the old and new servers. (I’m not a fan of using Distributed Availability Groups to do version upgrades. You can, but it is a heck of a lot of moving parts to set up for a one-time migration.)

This is a blog post about what to do after you migrate:

  1. Turn on Query Store, but make no other changes to your databases (including compatibility levels)
  2. Gather a 1-2 week baseline of performance metrics and query plans
  3. Switch some (but not all) databases to SQL Server 2022 compatibility level
  4. If users are unhappy with performance, use Query Store to identify plans that got worse
  5. Get relief for that query quickly, possibly with 2022’s new ways to fix performance without changing the query

Let’s go into details on each one of those.

Stage 1: Turn on Query Store.

This new-in-2016 feature is like a black box recorder for your query plans. It has a lot of weaknesses, but it’s still better than your game plan that did not include Query Store.

The goal here is to start capturing query plans as they are today, before all hell breaks loose. Compatibility levels change execution plans, and SQL Server 2022 has a lot of ways that it might change your plans. If performance gets worse, we’re going to want to see what the query plan looked like before our change.

If you’re migrating from SQL Server 2014 or earlier, you don’t have access to Query Store yet. That’s okay: you can still do the migration, but just make sure that Query Store is the first thing you turn on after you go live on 2022. (You might have heard that Query Store is on by default in 2022 – that’s misleading. It’s only on for new databases that you create from scratch – it’s not on for existing databases you migrate into 2022.)

If you’re migrating from SQL Server 2016, 2017, or 2019, turn on Query Store sooner rather than later – long before you actually do the migration project. This way, you’ve got the historical data when you’re actually running on the current version, before the database is moved to 2022.

To learn how to configure Query Store, watch this video from Erin Stellato:

In either case, once you’ve moved to 2022, don’t make any other changes to your databases at first – especially not compatibility level. SQL Server 2022 has options for compatibility level going all the way back to SQL Server 2008, so you should be able to forklift your databases over as-is, and just leave them there for a week or two.

Stage 2: After going live on 2022, gather a baseline.

We want to get a crisp, clear picture of how the old compatibility level is working out for us on SQL Server 2022.

Whenever you change anything about an application – whether it’s a code deployment, new indexes, or a SQL Server version change – people are going to always say, “Hey, things are slower than they used to be.” They’re probably lying. They’re just taking advantage of the opportunity to pin the blame on you, Default Blame Acceptor, and that’s why I want you changing as few things as possible when you do the migration.

Things may actually have gotten worse for their specific query, or for the workload overall. Maybe we messed up a SQL Server configuration, missed a trace flag, or the server hardware isn’t quite what we thought it was. (True story: a recent client’s new server was accidentally provisioned on the slowest possible storage instead of the fastest, so of course their upgrade went poorly.)

Do your normal investigative troubleshooting, and you can even use Erin’s tips in her video on how to use Query Store to track down query plan changes. However, during this stage, do not upgrade the database’s Compatibility Level to fix one query’s performance. Doing so will change the performance of many other queries, some for the better and some for the worse, and you’re not prepared to troubleshoot that right now. If people are complaining about one query, troubleshoot that query. If people are complaining about the whole server, troubleshoot the whole server – but leave compat level where it is for 1-2 weeks.

Stage 3: Change databases to 2022 compat level one at a time.

Compatibility level is a database-level setting. You can see it by right-clicking on a database and going into its options, or by looking at the compatibility_level column in sys.databases.

It’s database-level because it’s possible that some of your databases will perform better on newer compatibility levels – but some of them may not. That means you should take the angriest users, the ones who are the most pissed off about slow performance, and try setting just their databases over to 2022 compat level.

It’s a simple one-line change to change to , as the documentation illustrates:

“Wait – that’s two lines, not one,” you say, pointing your Cheeto-dust-encrusted finger at the screen. Well, the change is one line, but I want you to note the prior compatibility level and the date/time that you changed it – because you might need to roll back. Don’t worry, rolling back is as easy as running that same ALTER DATABASE command, but with the old compat level instead of the new one.

You can make this change whenever you want, without taking the database offline, but there’s a catch: it clears the plan cache for that database. That means you’re temporarily susceptible to parameter sniffing issues as SQL Server suddenly builds new query plans for this database.

Stage 4: Troubleshoot reports of slow performance.

Most of your queries are probably going to go faster after the change. But what’s most? 90%? 99%? 99.9%? Even if just 0.1% of your queries slow down, that’s still a heck of a lot of queries to suddenly have to troubleshoot – especially when users often don’t even know which query they’re talking about. They’ll put in frantic help desk tickets that say things like “The customer screen is slow!!1!” and “The import process is down!!one!” and “My keyboard is filled with Cheetos dust!”

If tons of reports of slowness come in quickly, don’t be afraid to change the compatibility level back to the prior one. It’s a safe, quick, easy way to make the screaming stop. And… just stop there. If users are happy enough on the old compatibility level, leave it there.

However, if the slow query complaints come in at a rate that you can handle, and you’re seeing performance improvements that you wanna keep in other areas of the app, then it’s time to roll up your sleeves and do troubleshooting on the slow queries.

One of the easiest ways is to ask Query Store, “What query plans have gotten worse?” In SSMS, go into the database, Query Store, and then run the Regressed Queries report.

As soon as the report opens, you’re going to need to change the configuration, because the defaults are wrong. At the top right of the report, click Configure. Here’s what the defaults look like:

By default, it’s showing total duration – so queries that ran more often during a time window will show artificially higher on the graph – when the real root cause is that they’re running more often. Me, I like changing “Based On” to “Avg”.

Then, down at the bottom of the window, change “Minimum number of query plans” from 1 to 2. We’re looking for queries whose plan actually changed (perhaps due to the new compat level), not just slowed down. Click OK, and view the report.

The top left window lists the regressed queries, with biggest impact to smallest. As you click on each query, the top right window will update to show the performance of various plans for that query. Remember how I asked you to save the prior compatibility level, and the date/time that you changed it? We’re concerned about queries whose plan changed after the time that you changed the compatibility level. That indicates a query whose performance may have been adversely affected by the new compat level.

Stage 5: Get relief for that slow query.

I’m going to list these from easiest to most time-consuming:

Option A: forcing an older plan. While you’re in Query Store’s Regressed Queries report, one of the easiest ways to get temporarily relief is to click on the query plan you used to get with the prior version of SQL Server, then click the Force Plan button on it.

I said it was easy. I didn’t say it was good.

When you force a plan, that doesn’t mean it’s going to perform well with all possible parameters for that query. This is especially true for queries that keep changing plans for valid reasons, like the example at right. That query’s got 5 different plans, and genuinely needs them. If I force a single plan for it, but my data has outlier parameters, I’m probably gonna get performance complaints from those users. We cover better ways to fix those in my Mastering Parameter Sniffing class, but that’s outside of the scope of this blog post.

Forcing a plan also means if a better option comes along later, like En Vogue’s lovin’, you’re never gonna get it. The whole reason you upgraded compat levels was to get better performance, but that query plan is stuck in the past. You can revisit them later by going into Query Store’s Queries with Forced Plans report – and I’d recommend going back in there every couple/few Cumulative Updates. Microsoft improves query plan behavior over time, so it’s possible that by unforcing a plan later, you’ll get better plan options.

Besides, you’re going to want to go into that Queries with Forced Plans report anyway because query plan forcing can fail, plus the query may change over time. As the query changes, the forced plan will no longer be relevant (because it’s for a query that no longer exists.)

I do still like forcing plans – and I wanna tell you about it because it’s quick and easy – it just has drawbacks, so we need to keep going with more options.

Option 2: give SQL Server hints for the plan. Let’s say that in SQL Server 2022 compat level, SQL Server decided to do something in the query plan that made performance worse instead of better. Let’s say it used batch mode processing on a rowstore index. If you want to disable that behavior, you can add a hint to the query without touching the query itself.

This is called Query Store hints, and David Pless has a tutorial on it. It’s not as easy as forcing a plan in the Query Store GUI. You’re going to have to get the query’s ID from Query Store, then apply the query hint you want using sp_query_store_set_hints.

It does involve work on your part, but … it’s still faster than fixing/tuning the query. As soon as you start changing the query itself, you’re probably dealing with getting approval from different folks in the company, getting it into source control, testing it, getting it deployed, etc. Query Store hints are instant, presto, in production. I can’t imagine how that could go wrong.

Option III: fix/tune the query. This takes the most work, but I’ll be honest: as a consultant, it’s the one I do the most often. Usually when I pop open a query that people are complaining about, I say, “Okay, here’s why it’s not performing well, and if I change this, this, and this about the query, it’ll go dramatically faster than it used to, and it’ll get a better plan overall.”

This is the option that doesn’t just bring performance back to its prior levels – it makes performance better, and after all, isn’t that what we want? (Honestly: no, a lot of people just want it back to the way it was, and that’s why this is the last option.)

SUM(MARY)

When you’re preparing to migrate to SQL Server 2022:

  1. Turn on Query Store, but make no other changes to your databases (including compatibility levels)
  2. Gather a 1-2 week baseline of performance metrics and query plans
  3. Switch some (but not all) databases to SQL Server 2022 compatibility level
  4. If users are unhappy with performance, use Query Store to identify plans that got worse
  5. Get relief for that query quickly, possibly with 2022’s new ways to fix performance without changing the query

But while you’re in those final two stages, pay particular attention to the bold words. If users aren’t complaining about performance, move on to the servers where they are complaining about performance. Every server has queries that need to be tuned, and you need to focus on the ones that users will actually appreciate.

[Video] Office Hours: Live Q&A

Brent Ozar
Videos
2 Comments

This one’s broken up into two parts because I took a bio break mid-stream:

  • 00:00 Start
  • 03:04 Clippy: Hey Brent, you are the best!!! Can you share us more about your roots? What’s the origin of your family name and where does it comes from?
  • 04:15 I_ALSO_WANT_A_FERRARI: Hi Brent, first of all thank you for all your good advices. During a batch I can see a heavy CPU load, in the perf monitor the
  • #compiles are +/- 80% compared to the
  • #batch requests. Too much. Most of the compiles are coming from TVP as param for a SP. Is this a bad practice?
  • 06:52 Sid V: Is their better value for the production DBA to go deep (knowledge) in their field or go wide (knowledge) in their field? What are the common examples you see of going deep and wide?
  • 10:38 Curious DBA: Hi Brent! Do you recommend manually configuring the pagefile.sys size and drive(s) it resides on when configuring new SQL Servers, or is that something you let Windows handle? If you do configure it, how do you determine how much size to allocate to it?
  • 11:33 Jaime Sommers: Is there a good way to determine what percent of queries are single threaded vs multi threaded for the purposes of knowing if we have too many cores / licensing.
  • 13:07 Q-Ent: Hi Brent . How do you imagine your life at your retirement. I assume for us, your followers will be “Brent Ozar Unlimited Last Update 2 years ago” :D. Do you prepare any successor for this empire !!!! Love your Job and your life perspective.
  • 14:03 gserdijn: Hello Mr Brent, Microsoft documentation on SET ARITHABORT for all versions states: “Setting ARITHABORT to OFF can negatively impact query optimization, leading to performance issues.” Why is that? Is the Cardinality Estimation so fragile?
  • 15:51 Yusuf: What are the best resources for learning PostgreSQL performance tuning?
  • 18:09 The Dyslexic DAB: Hi, We used to have a ‘special’ SQL license which meant that it matter if we installed Enterprise or Standard. Our licensing has changed and we now have dozens of Enterprise Editions that we can’t afford! Any advice or gotchas for downgrading from Enterprise to Standard? Thanks

And part two:

  • 00:00 Start, Twitch ad discussions
  • 00:40 DumbQuestionsRUs: Would you still recommend formatting drives to 64k?
  • 00:54 Shefatyah: What interesting stuff in the query plan XML do you wish was visible in the SSMS UI query plan?
  • 01:43 DantheSQLMan: Do you have any advice on working out of the country with SQL consulting?
  • 03:10 i_love_you_brent: Good morning Brent! We have 250 db on 1 instance.we use SIOS for failover on secondary. All dbs are on different versions with creepy procs for etl and etl data is stored in the same db.job runs every 15 to summarize data. Manager wants to separate 250 etl DBs on different machin
  • 04:38 Shefatyah: Sometimes running “EXEC sp_BlitzFirst @Seconds =10, @ExpertMode = 1” just runs forever but if we turn off expert mode and re-run it, it returns results instantly. Is this due to tempdb contention? Any troubleshooting tips? SQL 2019 Enterprise thanks
  • 05:40 NoobDBA: Hi Sir! Can you share with us, your biggest challenge on any consulting job that have you been into. Thanks!
  • 07:40 How do you eat rice with chopsticks?
  • 08:04 Yusuf: Is there a recommended way to run a SSMS query and then immediately run sp_blitzcache to analyze the most recent run for that query?
  • 08:53 Cara Dune: Which is better for office hour streaming? Youtube or Twitch? Why?
  • 10:02 ChopstickWizard: Probably a very silly question. When I write Select A.* from A INNER JOIN B On A.T = B.T and then someone else writes Select A.1 From A, B Where A.T = B.T Whose is better? Performance wise. so sorry if this is a very basic question
  • 12:26 EngineHorror: Have you ever run into the Halloween problem in any of the DMLs? Can one say that the recent versions of SQL Server don’t have it?
  • 13:48 i_love_you_brent: We have 250 dbs on 1 instance with etl and oltp together. is it worth spending time on separating those to 500 dbs 1 for etl and 1 for oltp. reports are timing out sometimes.
  • 14:40 ExtramileDBA: I have done tons of projects on SQL server and recently moved to a shop that is pro-MYSQL with SQL server only supporting ISV products. Do you know of any awesome MySQL conferences similar to the likes of Group By, SQL bits and Pass.
  • 16:08 Aleksey Vitsko: Hi Brent! With announced “failover from SQL Server 2022 to SQL Managed Instance” feature still being in private preview, do you think Microsoft will make this feature publicly available with one of future CU for SQL 2022 ? Just install CU and feature becomes available ?

I’m doing live streaming on my Twitch channel on Wednesdays & Thursdays this summer, and the recordings will go live later on my YouTube channel. I’ll take questions from PollGab, do live coding, work on the First Responder Kit, and write blog posts.

The stream will start at 8:15AM Pacific, 11:15AM Eastern, and you can see the time in your local time zone here. I’ll stream for around 45 minutes.

To get notifications when I go live, follow me on Twitch, or Google Calendar users can use this invite. See you in Twitch!

What Does Setting the SQL Server Compatibility Level Do?

Brent Ozar
Configuration Settings, Videos
16 Comments

If you right-click on a database in SQL Server Management Studio, you get an option to set Compatibility Level at the database level:

When you upgrade SQL Server or you want to improve performance, which option should you choose? Should you always go with the newest compatibility level? If you have an old application, can you upgrade SQL Server but still keep the old compat level without the vendor knowing? Let’s hit the common questions.

What does compatibility level do?

When Microsoft brings out a new version of SQL Server, they tend to keep the newest features only available in the newest compatibility levels. For example, SQL Server 2022’s Parameter Sensitive Plan Optimization (PSPO) is only available in databases running under SQL Server 2022 compatibility level.

That means if you’re taking a database that used to live on an older SQL Server, and you want to host it in SQL Server 2022, and you want it to have the same behavior that it’s always been used to, you should keep it on the compatibility level that it’s currently on. For example, if you’re hosting it in SQL Server 2016, and the database is currently at SQL Server 2016 compatibility level, then you could move the database to a 2022 server, but keep compat level on 2016, and the users shouldn’t notice the difference.

In reality, though, there are things inside SQL Server itself, at the server level, that will change no matter what your compatibility level is. For example, if Microsoft deprecates a feature and removes it altogether, that feature isn’t available even if you’re on older compat levels. (Hello, Big Data Clusters.)

Should I change compatibility level?

If there’s a specific feature that you need that’s only available in some compat levels, then yes.

However, if you’re happy with performance, then no. Hear me out: changing your compatibility level can make performance worse instead of better. Sure, in some cases, it makes performance GREAT – but because all change = risk, then changing compat level when you’re already happy is dangerous.

How does compatibility level affect performance?

If you migrated from SQL Server 2019 to 2022, here are ways that changing a database’s compatibility level can make things better or worse:

In each version of SQL Server, different features are enabled under newer compatibility levels. Before changing your compat level, you should review what features are added when you change from your current compatibility level to the new one. This is a good starting point.

What should I measure before changing compatibility level?

In theory, you should have a performance baseline of things like:

  • CPU, memory, and storage metrics
  • Your top wait types so you know what SQL Server is bottlenecked on
  • Which queries are using the most resources
  • Query plans of well-performing queries (because things might get worse, and you’ll wanna know what they used to look like back in the good old days)

Then, when people suddenly complain about performance, you can check your baseline to see whether things actually got worse, or whether your users had taken up eating shrooms. You could also track down which queries were NOW at the top of your resource-consuming query list, look at what their query plans USED to look like, and then figure out how to get back to the good old days.

Common ways to accomplish this are third party monitoring products, Query Store, or the First Responder Kit.

In reality, you’re not gonna do any of this ahead of time. So, when you change compatibility levels on the fly, and performance gets worse, you’re not going to have any answers.

Does that mean I shouldn’t touch compatibility level?

No, not at all! You can change compatibility levels whenever you want, one database at a time. You can also change back instantly as well. You just need to be aware of when you made the change, what you changed, and communicate it to the rest of the team so they can roll your change back if necessary.

What compatibility levels are available?

The screenshot at the top of the blog post was taken in SQL Server 2022, and even in this recent release, Microsoft supports compatibility levels going all the way back to SQL Server 2008. That’s kinda awesome, because it means that Microsoft is trying to keep old databases working great in newer versions of SQL Server.

In theory, that means you can take an old vendor application that was once certified on SQL Server 2008, and keep moving it to newer and newer versions of SQL Server. In theory, that means it’ll keep working the exact same way as long as you keep the same compatibility level – and hey, it might even get faster if you change to newer compatibility levels.

So, can I actually do that?

Well, no. I mean you could, but you might get caught.

Here’s the thing: the vendor might be relying on a feature that’s no longer available in newer versions of SQL Server. I gotta be honest, that’s extremely unlikely, but it is possible. And if they are, and their application suddenly breaks, you can’t restore a newer SQL Server database to an older version of SQL Server.

So if you take your SQL Server 2008 server, back up the databases, restore them onto SQL Server 2022, and then start using the app – and people start complaining – you can’t restore those 2022 backups down onto SQL Server 2008, even if they’re still in the same 2008 compat level. You can only restore to newer versions of SQL Server, not older.

Therefore, you’re taking a risk when you move databases onto newer versions of SQL Server. Make sure the vendor actually supports the newer version of SQL Server, because you don’t wanna be the person that the vendor blames for their application not working successfully.

Want to watch me write this blog post?

I streamed this blog post live if you want to get a rough idea of what’s involved with writing a post like this:

[Video] Office Hours Speed Round: 21 Answers in 15 Minutes

Brent Ozar
Videos
2 Comments

HONK HONK! It’s time for a fast round of answers to concise questions y’all posted and upvoted at https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Start
  • 00:19 TheyBlameMe: Hi Brent. What is your view on using Dacpac diffs vs “old style” manual update scripts for deploying DB schema changes in dev-test-prod?
  • 00:52 Alex: Hi boss, I have an app that creates a new DB for each customer. All DBs (3K~) have the same structure. Is there a best strategy or an article you can point to consolidate all DBs into one huge DB. Is it a good idea? I’m trying to save on maintaining hundreds of databases. Thanks
  • 01:42 Perplexed: Had a vender application running a stored proc and it wasn’t working. Ended up using Profiler to capture error message and passing that to vendor to fix. Is there a better way to find error messages that procs are kicking out, but hidden by error handling in proc?
  • 02:31 Piotr: Is there anything comparable to first responder kit for PostgreSQL that you like to use when performance tuning in on that side of the fence? How hard would it be to write a first responder kit for PostgreSQL?
  • 02:56 HashMatch: Hey, Brent! I have a work superior who prefers to use several UPDATE statements instead of joins, to “keep track of row counts”. How do I best demonstrate this isn’t a good idea for performance?
  • 03:39 Steven: Hi Brent, in a nighlty ETL my friend has 2 sprocs updating 1 table in parallel. A page-level deadlock occurs randomly (1 in a 100 runs). Any tips or ressources on how to fix a deadlock at page-level while keeping the sprocs parallel? Thank you
  • 04:34 Piotr: Do you have any recommended tools for diff’ing two SQL tables for the purpose of showing the index differences between the two tables (DEV – PROD)?
  • 04:55 Björgvin: Do you ever see any issues with using windows mount points and SQL Server?
  • 05:29 TY: Hi Brent, in my job we often use the ROLLBACK of a transaction for testing purposes. Is there an easy way to rollback after two or more days, when the TRAN has been already COMMITED? Like a checkpoint where you can return to, but only for a single table or a database?
  • 06:04 Piotr: Do you think we will ever see FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION in SQL2019 again? Is this feature worth upgrading from SQL 2019 to SQL 2022?
  • 07:01 Mickey: Hi Brent, I have a reporting query that runs under 10 seconds on other environments but runs for hours without finishing on this one environment. I’ve verified stats are up-to-date and the proper indexes are the same across all environments. Any other recommendations? Thanks!
  • 07:44 Gromit: Do you have a good way to fix high VLF count that doesn’t break log shipping?
  • 08:04 Nick Smith: Hi. Why might performance degradation of truncate tables in tempdb be seen after some time working in SQL Server 2019? There are no schema locks. I make look wait types trace in the current session, then there is nothing but SOS_SCHEDULER_YIELD and SOS_WORK_DISPATCHER
  • 08:37 LostInSpace: I have a developer using WAITFOR DELAY (10 – 15 minutes) to pause the iterations of his code instead of doing it in c#. I noticed them using sp_WhoIsActive with parms: @find_block_leaders = 1, @get_locks = 1, @get_additional_info = 1. It leaves suspended connections. good/bad?
  • 09:28 WorkinForDaMan: I’m the DBA for a city with a hybrid environment (on-prem and Azure VMs). While I was on leave, IT pushed 2016 AZC+GDR build instead of, SP3+GDR. I’m comparing both for diffs but wonder if you’d suggest reverting to SP3+GDR since we don’t have managed instances. Thank you, sir!
  • 10:07 Haydar: Do you have any recommended books / courses for sizing azure vm’s for lift and shift of SQL Server?
  • 10:53 Doc: Do you encounter peeps that live on the cruise ship in retirement? Is this an option for you?
  • 11:30 Ron Howe: What diagnostics would you recommend for a SQL Server that is fully 100% CPU throttled during query execution due to a “bad” query plan and you can’t get a SQL connection as such and a hard reboot seems the only solution?
  • 12:03 Nortzi: Hi Brent, recently a SQL statement with a begin tran and commit tran was executed from ssms and returned a results message. We had a blocking issue the next day. Turns out this query was still technically running and never finished. What do you think could have caused this?
  • 12:56 TY: Hi Brent, it seems like you know everything about SQL Server, or at least it seems like it. Can you do a short session with something that you don’t know much about and lead us through the process of learning it. It would be very beneficial to see how you build your knowledge. Ty
  • 13:56 marcus-the-german: Hi Brent, do you recommend that the sql server instance collation is the same like the user databases. If yes, how should we deal with databases which have a different collation?

Office Hours Speed Round, Easy Text Answers Edition

Brent Ozar
Production Database Administration
2 Comments

Some of the questions y’all post at https://pollgab.com/room/brento have easy one-line answers. Let’s knock ’em out:

George: Hi Brent, what recently has been the most challenging/surprising/new-to-you performance issue you have encountered?

SQL Server 2019’s slowdowns. I spent days working on that.

RoJo: Have you used Distributed AG as a way to upgrade major versions of SQL server without downtime? Seems like a nice way to try it out on a second site before a switch. Any concerns? Maybe jump from 2016 to 2019, or 2022 to big a jump? Cheers

No because it’s so much work to set up.

ConsultantWannabe: I’m a generalist trying to make the jump into the consultant role, I don’t want to be “a jack of all trades”. How should I start finding that niche (or that “expensive” thing to stand next to, apart from SS)? Do you think just asking around to the guys in suits is a good idea?

Ask executives what technology problem they can’t solve with their current staff.

Ive_Got_Heaps: Hey Brent, Our DB is loaded with heaps as our ERP system doesn’t utilize primary keys (begins crying). My plan is to create clustered indexes on existing columns where possible, or create an Id column for tables where no existing column can be used. Is this a sound approach?

Ask the ERP vendor. If it’s an in-house app, watch this.

Yevgeny: What are the top causes of data file corruption for SQL Server on a Windows cloud VM and how do you avoid them?

In the cloud, you don’t get root cause analysis from your vendor. Do backups and high availability (like AGs for automatic page repair.)

SQLrage: In 2019, can statistics updates on a table cause an execution plan to be recreated for a parameterized proc that hits the table but does not use that updated statistic in particular? Trying to better understand why plans regenerate automatically.

Read this and do a test.

Bart: Is there any harm in deleting Extended Properties of a table column? I inherited a database that’s been converted from MS Access into MS SQL several years ago and I think the extended properties are a result of that conversion.

What’s the benefit in deleting them? Why risk it? Who cares?

My latest toy, heading out for engine work first

Piotr: What file system folder convention do you like to use when locating data files / log files for a new SQL DB?

\MSSQL\DATA

Chetan: Which nice car did you buy recently? What do you drive now?

A 1964 Porsche 356 coupe, which is getting its engine checked out first before I take it on any road trips. Until it’s done, and because it’s springtime, I mostly drive my Speedster replica.

MacAries: I had an On Prem 3 CTE then join for result query that ran subsecond, but coming from Azure Function that sent multiple and crippled the on-prem server to a 20 result /minute nevermind the lock and batch waits is their some basic translation that azure needs to get the query run?

Read this or watch this.

Will Marshall: Do you run into any common performance issues with SQL always encrypted?

I’ve never had a client use it.

Haydar: What is the best way to copy a few tables from SQL Server to PostgreSQL?

I would ask this guy.

CB: Hi Brent – It seems SQL functionality isn’t supported in SQL task editor. Statement: Declare @sql nVARCHAR(max) Error: The Declare SQL construct or statement is not supported. Is there a solution to that?

I don’t know what the “task editor” is.

Will Marshall: What are the best courses / books for learning SQL Always ON?

I haven’t seen any that were updated with what’s new in SQL Server 2019, 2022, or Managed Instances Link.

Bocephus: For network perf testing between two windows nodes, what tools do you like to use?

Copy a large file like a backup.

Hal Jordan: What should we look at when OS pages per second paging rate is high for the bare metal SQL Server 2019 instance?

Attend this class.

Mirza: Discussion happening in the company about automating SQL patching on clusters using Powershell and SCCM. Both PS script and SCCM are owned by the server team. Does the DBA team lose control and does it matter? What is your experience/opinion regarding automating SQL patching?

Read this.

Kyle: Hi Brent! What are the best practices for restarting your SQL service on an HA system? Is there any way to do it with causing any downtime?

No, all restarts will cause downtime, even for cluster and AG failovers, so for minimal downtime, use clusters and AGs.

SQL_Developer_Admin: If there is left outer join, then why there is right outer join as well, if we could just swap the sides of the tables. Any scenario you know where only left join can be used or right join can be used.

Sometimes it’s nice to have multiple tools to approach the same problem from different angles.

Eyvindur: You mentioned caching as a possible solution to lessen the load on SQL Server. Are triggers are good solution for cache invalidation?

The idea of caching is to lessen load. Do triggers add or lessen load on the database?

Eduardo: Do you see any RDBMS disruptors threatening to steal Microsoft / Oracle market share in the near future?

If by “near” future you mean 5-10 years, yes, Postgres and AWS Aurora.

ChopstickWizard: Been mulling this over for sometime. There are instances where I want to recommend a product to a team, like example : Cockroach db, mage.ai etc. But the problem is, they seem “non enterprisey” just by their name in case of “cockroach” or mage.ai’s tag line. Have you faced this?

You mean names like ChopstickWizard?

[Video] Office Hours: Ask Me Anything About Azure SQL DB & MSSQL

Brent Ozar
Videos
2 Comments

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

Here’s what we discussed today:

  • 00:00 Start
  • 01:40 prasad: Hi Brent, I want to become a full fledged database architect. I have been reading and practising lot of stuffs here and there, but no certain path. I also subscribed once to ur master class bundle. can you guide me on a proper path for the same? Thanks in advance
  • 04:57 ExcitingAndNew: Hi Brent, what is your opinion on the practice of inserting SQL comments into SQL requests as tags to allow DBAs to track the requests in the server ? (I’m talking about comments for instance just after SELECT/INSERT/UPDATE/DELETE to force SQL Server to keep them everywhere)
  • 05:40 DGW in OKC: What is your opinion on the practice of a manager who consistently assigns DBA tasks to an employee who is marginally proficient at DBA work and is not really that interested in this discipline anyway?
  • 08:08 Fjola : sp_BlitzFirst shows the top expensive query of type : statement. It’s unclear which app/sp is generating this query. What is the best way to track down the app / sp generating this query / statement?
  • 10:05 Chris: Have you ever had manually created statistics either be a root cause or the final push needed to cross the line?
  • 10:53 CKI: How to get history of most recent queries executed with username in SQL? Auditing is not an option. Thank you!
  • 11:50 Piotr: Do you have a recommended method for adding notes to a given NC index (i.e. why this index is needed, which app uses it)?
  • 12:38 Perplexed: What are your thoughts on using PERSIST_SAMPLE_PERCENT to force all future UPDATE STATS to use a specific sampling? I just started using this on a very large table that was not getting stats right after updating the stats.
  • 14:02 UncleFester: When running Select */Count(*) SQL was using an index, returning only 47 mil rows of 95 mil in the table. Rebuilding the index/statistics was no help. Dropping/recreating the index solved it. Can I really trust Select * or Select Count(*) to return all of the rows in the table?
  • 14:56 RoJo: Debate rages here on what login to use for security: AD/Windows or SQL direct. Is either more secure? or if equal, do you prefer one and why? Thanks dude

SQL ConstantCare® Population Report: Spring 2023

Brent Ozar
SQL Server Population Report
2 Comments

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 spring 2023 version of our SQL ConstantCare® population report.

Out of 3,002 monitored servers, here’s the version adoption rate:

The big 3 versions are all within 1% of the last quarter’s numbers:

  • SQL Server 2019: 33%
  • SQL Server 2017: 19%
  • SQL Server 2016: 28%

On the other extreme:

  • SQL Server 2022: 2% – which is right on track with 2019’s adoption rates after it came out. It’s not low at all – it’s right in the same ballpark.
  • Azure SQL DB: 1%
  • Azure SQL DB Managed Instances: 2%

Just 13% of the population are running unsupported major versions (2014 & prior).

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

In other news, we do actually have 6 folks now running SQL Server on Linux! It’s a total of 32 SQL Servers, a mix of 2017 and 2019, running on a mix of Ubuntu and Amazon Linux. That’s about a 1% adoption rate. Only 4 of those are Dev or Express Editions, too. I’m still feeling pretty comfortable with my 2018 prediction that in 2026, <5% of SQL Servers would be running on Linux.

Other interesting tidbits:

  • 8% of SQL Servers are in a failover cluster
  • 23% of SQL Servers have Availability Groups enabled (not necessarily using it, it’s just enabled)
  • 6% of servers have Filestream enabled (not necessarily using it, it’s just enabled)
  • 34% are fully patched to the latest possible Cumulative Update (that’s amazing!)

34% of servers are fully patched to the latest possible Cumulative Update. That’s awesome! Nice work, y’all. My first thought was, “Oh, I bet the old versions like 2012 are fully patched, but the new ones aren’t patched because the patches keep coming out.” Nope, it’s actually the opposite: the most-currently-patched folks are on 2016 & 2017. The least-patched are the unsupported versions that haven’t had patches in forever. Disappointing.

Only 7% of SQL Servers are on unsupported versions or builds. That’s awesome too! Keep up the good work on patching, y’all.

Announcing Live Office Hours on Wednesdays & Thursdays

Brent Ozar
Company News
3 Comments

Office Hours - ask me anything.It’s time for summer school!

I’m doing live streaming on my Twitch channel on Wednesdays & Thursdays this summer, and the recordings will go live later on my YouTube channel. I’ll take questions from PollGab, do live coding, work on the First Responder Kit, and write blog posts.

The stream will start at 8:15AM Pacific, 11:15AM Eastern, and you can see the time in your local time zone here. I’ll stream for around 45 minutes.

To get notifications when I go live, follow me on Twitch, or Google Calendar users can use this invite. See you in Twitch!

Brent Ozar

Hi! I’m Brent Ozar.

I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I’m based out of Las Vegas. He/him. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.

Want to advertise here and reach my savvy readers?