Product has been added to your cart.

Blog

Updated First Responder Kit and Consultant Toolkit for June 2023

Brent Ozar
First Responder Kit Updates
2 Comments

This one’s a pretty quiet release: just bug fixes in sp_Blitz, sp_BlitzLock, and sp_DatabaseRestore.

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

  • Fix: update unsupported SQL Server versions list. Time marches on, SQL Server 2016 SP2. (#3274, thanks Michel Zehnder and sm8680.)
  • Fix: if you ran sp_Blitz in databases other than master, we weren’t showing the alerts on TDE certificates that haven’t been backed up recently. (#3278, thanks ghauan.)

sp_BlitzLock Changes

  • Enhancement: compatibility with Azure Managed Instances. (#3279, thanks Erik Darling.)
  • Fix: convert existing output tables to larger data types. (#3277, thanks Erik Darling.)
  • Fix: don’t send output to client when writing it to table. (#3276, thanks Erik Darling.)

sp_DatabaseRestore Changes

  • Improvement: new @FixOrphanUsers parameter. When 1, once restore is complete, sets database_principals.principal_id to the value of server_principals.principal_id where database_principals.name = server_principals.name. (#3267, thanks Rebecca Lewis.)
  • Fix: better handling of last log files for split backups when using @StopAt. (#3269, thanks Rebecca Lewis.)
  • Fix: corrected regression introduced in 8.11 that caused non-striped backups to no longer be deleted. (#3262, thanks Steve the DBA.)

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.

The Best Career Advice I Ever Got (#TSQL2sday)

Brent Ozar
Professional Development
23 Comments
Steve Jobs. Photo by Matthew Yohe.

I have been absurdly lucky to be around so many amazing and inspirational managers. Almost everything I know was taught to me by someone else. Over my life, I’ve learned so much about careers, marketing, business, communications, teaching, databases, you name it.

For this month’s T-SQL Tuesday, Gethyn Ellis asked us to share the best career advice we’ve ever received. That was a seriously tough call: I’ve gotten so many gems over the years.

But I thought about what’s the most important thing to pass on to you, dear reader – something you might not hear from other people who are answering this month’s call.

Steve Jobs said, “Real artists ship.”

He meant it didn’t matter how many great ideas you had, or how badly you wanted to do something, or whether your work matched the quality of the design in your head. At the end of the day, you have to make a plan, build your thing, and then get that thing out the door. Plans don’t count.

That quote drives a lot of things I do – like publishing this blog post, hahaha. Is this post perfect? Not even close. Could I do more? Yep, absolutely. But there’s a related quote often attributed to Teddy Roosevelt: “Do what you can, with what you have, where you are.”

So many years ago, when I first published my Blitz script – back before it was a stored procedure – I had so many reservations. The code quality wasn’t great. I didn’t see myself as an expert. I thought people would pick my work apart and tell me I was an idiot. But I had to start somewhere, so I sucked it up and put it out there.

I know when you read this blog post, you think, “Everything’s already been done. What could I possibly contribute?” You couldn’t be more wrong. In today’s rapidly changing data landscape, there are new back ends, services, and tools launching constantly. There’s a bigger need than ever for people to share what they learn, as they go. You’ve gotta start that blog, write that script, record that video, and let it out into the arena. The next phase in your career is right out there for you to take.

Ship.

Why Are Y’all Changing the Compat Levels on Master?

Brent Ozar
SQL ConstantCare
22 Comments

One of the things I love about SQL ConstantCare® (which happens to be free this month, by the way) is that I can keep in touch with what y’all are actually doing on your production servers.

Today’s lesson was a complete shocker to me: some of y’all are changing the compatibility level on the master database.

Normally, when you install SQL Server, the master database gets a compatibility level that matches your SQL Server version. If you’re on SQL Server 2019, for example, the master database will be compat level 150, which is 2019.

But there are a surprising number of users – like, over 20% of the population overall – who have changed the master database to be an older compatibility level. For example, we’ve got one user with several SQL Server 2022 instances, but they’ve chosen to set the master database to compat level 100 – that’s SQL Server 2008!

So I’m curious: if you’re one of those people who set the master database’s compat level to an older version … why? Are you running end user workloads in the master database, perhaps? Let me know in the comments, and it’s a judgment-free zone. I’m sure there are good reasons, I just … don’t know what they are.

Update 2023-06-16: Sounds like it’s in-place upgrades. I’m so saddened by this because I’ve told y’all not to do that, but in retrospect, I don’t think I’ve said it enough. I wrote that 8 years ago, and there are obviously a lot of new readers that have come in since then, and we should talk about it again. Noted.

[Video] Office Hours: Almost Back in San Francisco

Brent Ozar
Videos
1 Comment

It’s back to lighter fog-friendly clothes as the ship pulls into San Francisco and I answer your questions from https://pollgab.com/room/brento.

Here’s what we discussed:

  • 00:00 Start
  • 01:16 Pozzolan: Hey Brent, My companies budget for SQL licensing is small. In such situations, does it make sense to switch or start all new development on something like PostgreSQL? What suggestions would you make to a company that has a small SQL licensing budget?
  • 02:59 I watched Titanic on a cruise ship: Hi Gregory, what are your thoughts on doing index maintenance on indexes with fewer than 1.000 pages? Did it happend to bring you across the finish line?
  • 03:36 Parminder: What is the best way to profile / performance tune a stored proc with thousands of queries due to heavy use of multiple cursors in a single sproc?
  • 05:16 Olga: Can we look forward to an “Ozar office hours – hot tub time machine” twitch edition in the near future?
  • 05:39 Jim Ignatowski: What’s your opinion of keeping the cloud based DR SQL 2019 VM undersized on cores / memory and upsizing it right before it is needed? This would be for cost reasons.
  • 07:05 Magdalena: Do you think chat gpt will some day lead to the demise of stack overflow and DBA stack exchange?
  • 08:52 Anatoli: Should monitoring for slow queries be a DBA responsibility or a developer responsibility (via app perf monitoring)?
  • 10:53 Anatoli: For DBA type sql agent jobs (i.e. checkdb), it is better to email on success or failure?
  • 11:24 Pozzolan: Yo Brent, Does your training cover Table Partitioning and sliding window partitioning? Or Partitioning in general?
  • 12:43 ThanYou: Hey Brent. Have you thought about an appendix to your courses? I need information from several of your videos again and sometimes it is difficult to find the subjects I am looking for.
  • 13:55 Eduardo: What is your opinion of A.G. auto page repair? Can it mask more serious hardware problems?
  • 15:21 Janus: What are your thoughts about setting NOCOUNT, XACT_ABORT to ON at the start of each SP?
  • 16:13 A fan: What is the diff between the following “joins” A – Select x from T1 inner join T2 … B – Select x from T1, (select … from T2 …) C – Select x from T1 where T1.a = (select … from T2 …) D – CTE With respect to exec plan & performance?
  • 16:55 Pozzolan: Hey Brent, Can you recommend any other SQL Guru’s doing this sort of QA content or Youtube videos?
  • 17:32 ThanYou: Hey Brent. When you tune queries, do you also look on how / when they are executed? If they are executed by a nightly job, they may not be as important to tune as if they are run by the application during work hours.
  • 18:52 Rufus: Have a sproc where we need to build HTML for the the email body used by msdb.dbo.sp_send_dbmail. Do you know of any good TSQL libraries for building HTML?
  • 20:45 Mary: Do you ever see a clustered index of a DateTime followed by an identity column? Do you see any good use cases for this potential clustered index?
  • 22:23 Larry: Is PostgreSQL as difficult as SQL Server to pull the actual (not compiled) query params from a captured query?
  • 22:47 ALSO_I_ALSO_WANT_A_FERRARI: Hi Brent! Do you think working with SQL Server is suitable with having a side-hustle?
  • 22:52 Somebody’s watching me – Rockwell: Do you think Microsoft shares the Azure SQL DMVs docs with SQL monitoring vendors or do they just figure it own their own?
  • 24:05 Sea chub: Where do you draw the line in “teaching men to fish” with regards to helping users with their query performance?
  • 25:45 ALSO_I_ALSO_WANT_A_FERRARI: Hi Brent! Have you had cases throughout work when something is bogging you so much you think you’re going crazy? Even when trying a very simple thing, it would give you unexpected results? Thanks!
  • 27:42 Shawn_M: Hi Brent! My company uses Azure SQL DB and Data Dogg for monitoring. Neither of those have any built-in SQL Server error alerting. Are the days of SQL Agent raising errors over? I’m honestly not sure that there is value in error alerting in SQL DB. Is up/down monitoring enough?
  • 28:46 Donnie Darko: Is it safe to invoke sp_send_dbmail directly, or should we abstract it and call a proxy sp to protect against breaking changes in future SQL Server upgrades?

The SQL Server Advice You Don’t Wanna Hear

Brent Ozar
SQL ConstantCare
22 Comments

One of the big principles of our SQL ConstantCare® monitoring product – which happens to be free this month – is that we only wanna tell you things you’re actually gonna take action on.

Anytime we email you advice, there a few “Mute” links that you can click on to never hear this advice. You can mute the entire rule, or mute it for specific servers or databases.

Here are the top 10 things you’re muting, and the percentage of the audience that’s muting ’em. Keep in mind that they may not be muting the recommendation for everything – might just be for specific servers or databases.

  • #10: Missing Indexes: 15% – I actually thought more than 15% of the audience would mute these recommendations because I figured a lot of y’all were managing some third party databases that you didn’t want advice on. Maybe this number’s low because we’re not just repeating what Clippy says here. We’re actually analyzing the indexes, merging things together, and skipping dumb ideas. I’m excited that 85% of y’all want to hear the index advice!
  • #9: Databases Offline or Restoring: 16% – We warn you if a database flips into an unusual status, but that’s to be expected if you’re playing around with log shipping, or flipping AG databases back & forth.
  • #8: Fill Factor Too Low: 17% – There are still a lot of folks out there that believe sub-80% fill factor is smart, when in reality they’re wasting >20% of their memory, making backups take 20% longer, etc. I like to hope that the fill factor was set by vendors, but … you can override that easily, and the vendor would probably never even notice.
  • #7: No Recent Full Backups: 19% – Okay, now, this is a bummer because we’re talking about a week between backups, and only databases that are actively online (not restored recently, not offline, etc.) I know you think your dev databases aren’t that important, but … at least grab a backup weekly. Sooner or later, you’re gonna wish you did.
  • #6: Transaction Log File Too Large: 22% – As in, larger than the data, and larger than 5GB. I’m totally okay with people muting this as long as they understand the implications for longer restore times.
  • #5: Cumulative Update Available: 23% – This one really surprises me because we only alert people once a week.
  • #4: No Recent Corruption Checks: 24% – I want to believe that y’all are offloading corruption checks elsewhere.
  • #3: One Serious Query to Tune: 29% – This one pops up when the majority of your SQL Server’s workload is due to one specific query, which means you can make a difference quickly by tuning just that one. I totally understand people muting this because you might have taken your best shot at tuning it, and decided it’s as good as it gets. You might also be okay with the performance of that server overall.
  • #2: Stored Procedure With Recompile: 34% – Recompile hints are a totally valid solution to some performance issues, and I understand why folks mute this advice. As long as you’re aware that the code won’t show up well in monitoring tools, that’s okay, and besides, we can still catch it with Query Store. Which brings us to…
  • #1: Consider Enabling Query Store: 53% – Holy smokes! Look at the jump between #2 (34%) and #1 (53%), and it’s even more eye-opening. Most of y’all really do not want to hear about Query Store.

So I gotta ask the question, and it’s only targeted at people in that last category: why are y’all so dead set against using Query Store? Let me know in the comments. (If you’re a Query Store fan, that’s great, and I’m with you, but stay out of the comments to let others speak. If you feel like leaving a comment about how great Query Store is, this video may help you cope.)

And if you’re curious about what you’re missing, you can get a free trial of SQL ConstantCare®.

Poll Results: Are You Going Back to In-Person Events?

Brent Ozar
Conferences and Classes
14 Comments

Last week, I asked if y’all had been to in-person regional events before, and whether you were going back.

I wanted to know because Data Saturdays and SQL Saturdays are starting to happen again, but … attendance seems way down from pre-COVID numbers. I wondered, are people just not going to return to conferences for another year?

I ran a poll here on the blog:

And ran another poll on LinkedIn:

The results tells me:

  • Most readers have been to events before
  • Most of those past attendees do plan to return, but 1/4-1/3 of them don’t
  • Of the readers who’ve never been to an event before, half of them don’t plan on going

So, why aren’t people going back now that the big wave of the pandemic has passed? I don’t think it’s just one thing – I think there are a lot of good reasons:

People are still cautious. Some folks have health issues, immunity risks, etc., and that’s all perfectly valid. (Someone’s going to want to say, “People have kids now,” but that’s not a new issue. News flash: people had kids before the pandemic, too. Ask your mom, and tell her I said hi.)

People got used to online & hybrid events. Perhaps they’re comfy at home, and they don’t want to leave the comforts of home anymore in order to learn. They might have also gotten used to using other ways to network and learn, like social media and recorded training.

People got burned out on online & hybrid events. I feel this one firsthand: I got used to those events, but then over time, I sat through a whole lot of really terrible Zoom calls. Me personally, I’d rather take one of those Fear Factor eating challenges than sit through another non-interactive Zoom call. I’d rather get my learning via other methods.

Companies stopped budgeting for in-person events. Several of my clients have told me, “Our staff told us they could work just as well from home as they do in the office. Okay, great – but then you can’t claim in-person conferences are better than remote, so now your in-person conference budget is over.”

People stopped budgeting for them too. With the roller-coaster economy, widely publicized layoffs, and higher interest rates, some attendees who used to pay for conferences out of their own pocket are no longer doing so.

Senior people moved on to other technologies. Whenever I ask questions like this on social media, inevitably I get replies from people I met at SQL Saturdays a decade ago. They say, “I’m just not working with SQL Server anymore – I’ve moved on to X.” Sometimes those technologies are non-database, sometimes they’re so new that there aren’t conferences covering ’em yet, sometimes it’s management.

Junior people don’t know about conferences. If you’re a data professional with only 3-4 years of experience, you never got the chance to attend a conference, and you’re probably not asking for the opportunity or money. Plus, you haven’t seen these events on your social media feed for the last few years, so either you don’t know about them at all, or you don’t have evidence about their benefits.

Speakers don’t travel as much anymore. There used to be a huge crew of speakers who would jump at the chance to drive or take a cheap flight to present anywhere that would accept ’em. That lifestyle has dropped dramatically, and when I see regional events, it’s a new crew of speakers. That has an interesting side effect: I’ve talked to both speakers and attendees who say, “I don’t wanna go to a conference if my old friends aren’t speaking.”

Speakers don’t evangelize the events as much anymore. A lot of the new generation of local/regional speakers don’t have a large following on their blog, mailing list, YouTube, or social media. As a result, promotion of these events is left to the local organizers, who don’t have a large following either. That means it’s tougher to drive signups.

I’ve written a lot above about why I think some people aren’t going, but just so we’re clear: I’m still really bullish on in-person networking. I think if you make a plan and put work into it, it can be really positive for your career. That’s not to say it’s the only way to get career growth – it’s certainly not – but I’m a believer in them as part of an overall strategy. Having said that, I’ve cut back too. I’m only going to 3 more events in 2023: Data Saturday Gothenburg, Data Saturday Oslo, and SQL Saturday Boston. Hope to catch up with you at one of those!

[Video] Office Hours at Sea: Leaving Juneau

Brent Ozar
Videos
0

I stopped piloting the ship long enough to answer your top-voted database questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Start
  • 00:42 Dirty_Pages: You mentioned recently how much you swear, what is your favorite swear word(s) and why? Any favorite insults?
  • 02:38 SQLCircus.Clown: What is your go to/default SQL datatype for storing a run of the mill date and time data? DateTime2(3), DateTime2(7), or DateTimeOffset? DateTimeOffset make sense if the date/time will always be in UTC? Does the good old datetime datatype still have a place?
  • 03:27 Pixma: Hi Brent! I have a legacy database that now needs to cater for international/unicode data. It has hundreds of tables with char/varchar columns of which just some will need to handle unicode data. Do you have any experience or tips or gotchas for dealing with this kind of change?
  • 05:53 Ramon Wolfer: Hi Brent, how about a home tour? You mentioned in one session your home theater. I would really appreciate to see that or your garage / car collection. Thanks for sharing your knowledge in such an entertaining way 🙂
  • 07:09 I’ll be BacH: Hey Brent, If I want to work with Big Data, then which type of company should I work for?
  • 08:40 Persephone: What are the pros/cons of hosting multiple tenants in separate databases in Azure SQL DB? Is plan cache a concern in this scenario?
  • 09:33 Heimdall: For Entity Framework / SQL shops, should the DBA also know E.F. in order to troubleshoot / tune SQL queries?

[Video] Office Hours in Glacier Bay, Alaska

Brent Ozar
Videos
2 Comments

Thankfully, we didn’t hit any icebergs or glacier droppings, so I put my comfiest coat on to answer your top-voted questions from https://pollgab.com/room/brento.

Here’s what we discussed:

  • 00:00 Start
  • 01:29 gb DBA: Hi Brent, I have over 200 threads with BROKER_RECEIVE_WAITFOR type when I execute sp_whoisactive and some of trx have high numbers on reads and CPU. Should I be concerned about these waits or they can be ignored?
  • 02:17 NotCloseEnoughToRetirementToStopLearning: Hi Brent…love your content thank you for all you do. You mention HackerNews is one of your sources for tech news. How do you curate the articles to what interests you?
  • 03:10 TheCuriousOne: Hi Brent! On a client system with strict access restrictions, which permissions do you absolutely need for effectively being able to do some performance tuning research?
  • 04:47 TJ: Hi Brent, what are your troubleshooting steps for when a query runs slow in one environment with higher specs and runs fast in another environment with lesser specs?
  • 05:57 Manoj: What is your opinion of hierarchyid data type in SQL Server? Do you see frequent performance issues with it?
  • 06:20 Efraim: What do you think about SQL 2022 Intel QAT backup support? Is this a game changer for multi-TB DB backups?
  • 07:08 Parminder: Do you see more SQL host machine failure rates in the cloud or on-prem? What are the top failures you see?
  • 08:18 Sigríður: Do you have a recommended way to find the top queries using TempDB the most?
  • 09:07 DBA_Mufasa: Salut Brent! What is your best advices for running Check DB on a larger database (~1TB) that is part of a 2 node AG with a non readable replica. Should i run the full check DB on both the primary and secondary around the same time or just run it on one and use WITH PHYSICAL_ONLY
  • 10:46 neil: Can a company have both a prod DBA and a dev DBA? Would they be on the same team or different teams? What if there was only one, who would hire the other one?
  • 12:35 Piotr: Who is the Brent Ozar for linked server query tuning?
  • 13:38 I’ll be BacH: Hey Brent, My company is thinking about enabling Row or Page level compression to save storage space. Is there a way to find out how many additional CPU cores are required to offset the compression workload? I’d like to compare cost of extra storage vs extra core licenses.
  • 15:38 Petey: Why would a linked server’s Data Access property change from true to false? This is happening occasionally (for no reason that I have been to find yet), and is causing a vendor-supplied job that relies on it to fail. What can I look for to catch the change in the act?
  • 18:11 TheyBlameMe: Hi Brent. Have you ever managed to combine your love of cars and SQL Server via your consulting work?
  • 18:56 ProochingMan: For documenting architecture and/or data lineage, we try to use Visio. But manually creating/managing that is very time consuming and prone to human error and not updating. Have you seen any good tools being used out there amongst all your customers?

AWS Aurora Cut Our Database Costs for SQL ConstantCare® – Again.

Brent Ozar
Amazon Web Services (AWS), SQL ConstantCare
3 Comments

In May, Amazon brought out a new Aurora I/O Optimized Serverless instance type. By switching to it, we cut our database costs by 43% overnight, saving us about $1,200 per month.

No strings attached. Just lower prices.

So what’s the magic? Well, customers of our SQL ConstantCare® service send us diagnostic data every day for thousands of SQL Servers around the world. We import that data, process it, analyze it, and then send emails with specific actions we want the customers to take on their servers.

It’s a lot of data shuffling around, which means a lot of IO. We bring in so much new data every day, and we only keep 30 days of it online. We can’t just tune queries to cut IO: it’s legitimately new data going into the system, and that’s gotta make it to disk. (We’ve even tried cutting the data we import, too.)

When we broke out costs per day, the top cost was IO:

That’s the magic of AWS’s newest serverless price offering: it’s specifically designed for people who do a lot of IO. Amazon’s press release said it would offer “up to 40% cost savings for I/O intensive applications where I/O charges exceed 25% of the total Aurora database spend.” That’s us, alright!

If you’re using AWS Aurora, and your StorageIOUsage costs are like ours, you owe it to yourself to flip the switch over to the new instance type. Go into the portal, modify your cluster, and check out the storage configuration options:

You can switch over to I/O optimized with just mouse clicks, no cluster changes or app changes required. If you find out the cost structure doesn’t work out in your favor, you can just switch right back. (AWS does have guardrails in place to make sure you don’t flip back & forth repeatedly for busy periods.)

This new change helped us confidently run free trials for SQL ConstantCare® this month, too. Why not try it and see what you learn about your SQL Servers?

Poll: Are You Going Back to In-Person Events?

Brent Ozar
Conferences and Classes
14 Comments

Two short yes/no questions about regional & national conferences:

(If you can’t see the yes/no questions or the results, click here to view the blog post.)

I’ll close the poll after a week and write up my thoughts about the results.

[Video] Office Hours at Sea: SQL Server Q&A

Brent Ozar
Videos
0

En route from San Francisco to Juneau aboard the Ruby Princess, I stopped to hit a few of your questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Start
  • 00:46 DBACAT: Hi Brent, what statistics impact occurs when a transaction is rolled back? The query’s exec plan didn’t change but duration and CPU jumped 10X. Stats were updated at the time of the transaction. Does SQL roll stats back? It doesn’t seem so. Seems like SQL got all confused.
  • 04:33 Whiny App Developer: Hi Brent a well-intentioned index has been added to our app’s database. My friend can see it has caused one query to slow down, and the team aren’t sure when it was added or what it might have sped up. What’s your opinion on DBAs making changes without informing the app’s team?
  • 06:11 RaduDBA: I don’t necessarily have a question, but I need some encouragement and emotional support since my company still uses SQL 2000 (not a joke, not a type) and they don’t want to migrate since the app still works fine. It’s a government institution, somewhere in the Midwest.
  • 07:17 Whiny DBA: Hi Brent, what is your opinion of developers that want total control of non-clustered index creation/modification/deletion on production SQL servers?
  • 08:45 Eduardo: Do you recommend any specific MS Excel skills for the production SQL DBAs?
  • 08:54 Mars: Hi Brent, what are your predictions for DBA in a next 10 years? And how do you feel about the chatgpt etc. Is it a threath for DBAs? And if yes what we should do to save our jobs? Bit shout Out to you and what are you doing for sql community. Thanks
  • 11:07 Arlo Fuller: Hi Brent, what realistic options are available for implementing multi-master replication between multiple Always On Availability Groups? I had looked at peer to peer replication but not sure if it supports AAG’s.
  • 12:44 Ildjarn: Have you ever considered using Powershell for sp_Blitz, only querying the data that you need with e.g. dbatools, and then do the parsing in Powershell? The advantage would be that you offload the parse stuff to a management system, leaving CPU free for SQL Server’s actual job

Breaking News: SQL Server 2022 Keeps Getting Worse.

Brent Ozar
SQL Server
79 Comments

<sigh> Okay, so, the last few Cumulative Updates have had known issues around broken remote queries using the generic ODBC connector and errors with contained availability groups, but I couldn’t really care less about those. If you use those features, I give you bombastic side eye anyway.

However, in the last few days, two more known issues have surfaced.

The first problem is that Cumulative Update 4 can give you incorrect query results when all of these are true:

  • Your index explicitly specifies the sort order, like DESC or ASC (see update below)
  • Your query has a WHERE filter on that sorted column using an IN list or multiple equality searches
  • Your query has an ORDER BY with the sort order as the index (which is, after all, why you created the index)

So for example, this can give me incorrect query results:

To work around that problem, the CU4 documentation suggests you uninstall CU4 or enable trace flag 13166 and free the plan cache.

Update: in the comments, Paul White points out that trace flag 13166 skips a logic step when building query plans, but it only applies to descending index keys. That means the CU4 documentation might be wrong, and this bug might only apply to indexes with a descending key specified.

The second problem is memory dumps every 15 minutes if you have both Query Store and Parameter-Sensitive Plan Optimization (PSPO) turned on. Microsoft says they’re working on this issue, but for now, the workaround is to disable Query Store or PSPO, or continuously delete PSPO plans from Query Store yourself.

Should you do new installations of SQL Server 2022 today? I’m not going to give you the answer, dear reader – instead, I wanna hear your opinion in the comments. If you were deploying a mission-critical production server in June, which SQL Server version would you pick?

Who’s Hiring in the Microsoft Data Platform Community? May/June 2023 Edition

Brent Ozar
Who's Hiring
3 Comments

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

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