Product has been added to your cart.

Free Azure Networking Training This Month!

Brent Ozar
Microsoft Azure
3 Comments

Raise your hand if you’ve ever blamed the network.

You’re responsible for the health, security, and uptime of your company’s data services in Azure. You’ve provisioned a few services, but every now and then, you run into problems making your services reachable and reliable from different users and app servers.

You want to understand:

  • Your company’s cloud network topology
  • How your services live in that topology
  • What a private link is, and why you probably need it
  • If your services are available publicly or privately (and how to tell the difference)
  • Where to look if someone can’t connect

You’re ready for our latest class, Fundamentals of Azure Networking for the Data Professional. It’s taught by Drew Furgiuele, former Senior Cloud Solution Architect at Microsoft, and now a Senior Solutions Architect at Databricks. You might recognize him from his popular Fundamentals of PowerShell class, too!

To celebrate the launch this month, use coupon code CheckTheDNS for 50% off the $89 price. If you can block out time on your calendar, one module of the class will be free each weekday:

Block out some time per weekday on your calendar now because on each weekday in July, a different video will be live – but just for one day only! You gotta keep up if you wanna learn for free. (I’ll be making the videos public manually, so it won’t be at an exact time – just rest assured that if you log in at the same time every day, you’ll always have a fresh video to watch. If you log in at different times each day, well … you might not. Sorry about that.)

It’s also part of my Recorded Class Season Pass: Fundamentals for $395. Folks who already have the RCSP or one of my big bundles can hop into their account and start learning immediately.

FOR XML PATH Changed The Way I Think About T-SQL #TSQL2sday

Brent Ozar
T-SQL
4 Comments

The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.

And that same thing probably happened the next several times, too.

But eventually, I took a deep breath and read the code more closely to understand what it was doing. I don’t know whose example I originally found, but I’m going to point you to Dave Valentine’s post on how to do it because he’s got a clear example that even has a database diagram! Way to go, Dave.

The basic idea behind FOR XML is that you can take a regular query’s results, and export them to XML format.

Normally, XML looks kinda like this:

With FOR XML PATH, you can define how SQL Server formats the output. For example, if you wanted a list of just the names, and you wanted them in a comma-delimited format, you might want:

Nothing about that is even remotely close to valid XML, but the FOR XML PATH technique lets you abuse the intended usage of the feature and get something completely different out of it.

To learn how to do it, check out Dave’s post, which includes this example:

The a-ha moment for me was realizing that instead of building a result set, FOR XML PATH is building a result string, and it just so happens that in this case, the string doesn’t have line endings. You could build a multi-line result set by appending carriage returns and line feeds at the appropriate grouping points.

For more a-ha moments, check out Erik Darling’s call to action for today’s T-SQL Tuesday, and there will be fun comments in that post.

[Video] Office Hours: DBA & Developer Relationships

Brent Ozar
Videos
1 Comment

Y’all posted & upvoted questions at https://pollgab.com/room/brento, and we finished up the session talking about the relationships between developers and database administrators.

Here’s what we covered:

  • 00:00 Start
  • 02:35 Nortzi: Hi Brent. Is there a way to optimize sorting a result using a column from a different table? Using POC index pattern (partition,order,covering) works great to eliminate sort operations on columns from the same table but what if I have a column from another table in the order by?
  • 04:25 Gustav: Who is winning the serverless cloud SQL race? In addition to price, what are the big differentiators?
  • 05:38 Pool_Party: You wrote on your blog that SHRINKDATABASE is not good because creates fragmentation and therefore the problem is not solved. But I’m in an Azure SQL Elastic Pool scenario and I need to claim more space otherwise databases will not fit. What to do?
  • 07:29 SD: Hi Brent, I have Non-Prod AGs that I want to upgrade from SQL 2016\2017 to SQL 2022 (And downgrade the Edition from Enterprise to Developer). Can I do this by adding SQL 2022 Developer Edition Nodes to the AG and doing a failover (is this supported \ allowed \ known issues)?
  • 08:56 Deepak: Do you think generative AI would be a good addition to Redgate SQL Prompt (i.e. help write queries)?
  • 11:13 Jahid Ajmeri: Hi Brent, I am DBA. I want to use transactional replication with updatable subscription. There are multiple services insert/update data every 5 second into database. I want to make this write operation on both pub & sub database without conflicts. What you suggest.
  • 14:01 Adam: Hi Brent, I’m finding on occasional that my msdb is going into Suspect mode around the same time in the evening. I have run DBCC and VM disk checks and nothing has been found. Have you ever come across this before and if so, how did you solve it?
  • 15:27 Dwayne: Which games do you enjoy on the PC? Which games do you enjoy on the PS5?
  • 16:56 TomInYorks: I’m exploring solutions to locking/blocking seen in an application (multi-tenant, DB per customer but customisable reporting on live data) – was looking at RCSI or Snapshot Isolation. Can you recommend a good place to start when analysing the suitability of either of those?
  • 18:31 Vishnu: Is Apple Worldwide Developers Conference or Microsoft Bing the more influential tech conference?
  • 18:57 Vineeth: What is your opinion of the new Json native data type in Azure SQL DB?
  • 20:06 Deepak: Automated weekly Qualys cyber scans attempts to penetrate the SQL Server and reports findings back to corporate. One of the test threads attempts to connect to the DAC. Do you see any risks in this?
  • 20:57 Edwardo: Is there such thing as a “memory deadlock”? I recently observed multiple parent queries snagging 25% memory apiece and then calling a linked server loopback connection to spawn some child queries under a different SPID, and the child queries were waiting on RESOURCE_SEMAPHORE.
  • 22:21 Dom: Hi Brent, You mentionned a couple of time about “san backup” for SQL large databases. I google it and didn’t find much information about how that “work”. I’m guessing it’s backing up the mdf and ldf (it then cannot take log backup right?) Does it mean DB needs to be in simple ?
  • 23:04 Halldora: For sp_BlitzCache @MinutesBack argument, is there good way to tell how many minutes back we can go before data is no longer present?
  • 23:58 Yitzhar: Do you have any good use cases for the SSMS option : “Discard results after execution”?
  • 24:41 toepoke: Hey, I use lookup tables in my db, typically with “Id, Code, …” columns, with a FK onto “Id” from the parent table. “Code” should be unique. “StatusCode” has a unique nonclustered index to avoid dupes. Is this more icky than dropping “Id” & using “Code” as the identity col?
  • 25:31 Dwayne: What MSExcel skills (if any) do you recommend having for the SQL DBA?
  • 27:14 dave: Some developers say that DBA stands for “Don’t Be Asking” – How do you feel about Dev-DBA relationships?

[Video] Office Hours: Lots of Good SQL Server Questions

Brent Ozar
Videos
5 Comments

At https://pollgab.com/room/brento, post the questions you’d like to get my opinion on, and I’ll take a break every now and then at the office and go through ’em.

Here’s what we covered in this episode:

  • 00:00 Start
  • 01:08
  • #ARRRRRGH: Hi Brent. Have you seen anybody put good visualisations on top of sp_Blitz? Im trying to build something in Power BI to look at our entire estate and looking for inspiration
  • 02:33 Pradeep: Hi Brent, I am a Dev DBA. I use of SET STATISTICS IO ON to identify heavy logical reads and tune indexes to minimize it. I got better results also. Production DBA says reducing physical reads only will add value and logical reads not. Your thoughts please.
  • 04:01 MancDBA: Hi Brent, how dedicated were/are you when it came to getting better knowledge about SQL Server. Did you ever study at the weekends/evenings or did you get enough knowledge throughout the working week? Cheers.
  • 06:03 The Net Demoter: When should you not enable Query Store for SQL Server?
  • 06:44 Venkat: What is your opinion of the new query_antipattern extended event in SQL 2022?
  • 10:57 Lakshmi: Do you have any good use cases for temporary SQL stored procs?
  • 12:23 Deepak: Do you think ChatGPT will result in the re-birth of Microsoft English Query for SQL Server?
  • 14:17 CKI: What tool do you recommend to check database for sql injection vulnerability?
  • 14:54 Bruce Un-Mighty: Does SSMS execution time in the lower right include or exclude the time required to render the results grid?
  • 16:20 Dipesh: How do you know when SSMS plan operator times are individual or cumulative?
  • 17:28 Maksim Bondarenko: How do You deal with “Transaction Log Larger than Data File” situation when a database in AlwaysON Cluster with Syncronous replica?
  • 18:42 Parminder: Is there value in learning PowerBI for the SQL DBA?
  • 20:24 Deepak: What Azure SQL DB features do you think we can expect to flow down next to traditional SQL Server?
  • 23:48 Dr. Zaius: Hi Brent, have you ever used Adam Machanic’s TOP (max bigint) technique as an alternative to the X-acto method of using temp tables to phase query executions? Any rules of thumb on which technique to use in a given situation?
  • 24:35 Yousef: What is your opinion of OPTIMIZE_FOR_SEQUENTIAL_KEY in SQL 2019?
  • 26:11 Eli: Hi Brent, do you have a rule of thumb or training that teaches about the smallest table size you’ll index? I’ve got some ~8k tables that are slowing down a query with millions of scans, but my hunch is that I should be focusing on reducing the number of scans instead of indexes.
  • 27:32 Mars: Hi Brent, would you reccomend using firstresponderkit for benchmarking?
  • 29:04 neil: Is there a way to MAXDOP someone else’s troublesome query (third party application dashboard hogs all the cpu cores). Like MAXDOP a specific sproc or something like that?
  • 31:09 GuaroSQL: Hey Brent! how are you? Is First Responder Kit working fine in SQL server 2022? we are thinking of migrating, but we are using a lot First Responder Kit and we want to keep using it.
  • 32:22 Eduardo: When running sp_blitzfirst, should it be run on the DAC connection?

[Video] How to Use ChatGPT to Write WordPress Blog Posts

Brent Ozar
Blogging, Videos
5 Comments

Today’s live stream was a little different: I demonstrated using the Aiomatic WordPress plugin, ChatGPT, and Azure Open AI to write blog posts. I showed the kinds of content it writes, the kinds of blog post content it doesn’t include, and taught you how to identify meaningless word salad blog posts written by people who don’t actually know what they’re doing.

I finish up by talking about the kinds of places where it actually is useful, and how I use it to wireframe out new work.

[Video] Office Hours: As They Pour My Patio Concrete Edition

Brent Ozar
Videos
4 Comments

I can finally see the finish line in my backyard renovation, now that the concrete’s going in. I took a break from watching the construction folks to go through your highly upvoted questions at https://pollgab.com/room/brento:

Here’s what we covered:

  • 00:00 Start
  • 02:03 Fabricator: Hi Brent, what is your take on the recent lunch of Microsoft Fabric as a one product that can handle all your analytics needs? wasn’t that what Azure Synapse is doing? And where does sql server fit in there?
  • 03:37 GuaroSQL: My company has a lot of replications that use CDC and each time I run sp_blitzIndex there is a lot of recomendations to create index on those cdc tables (with a lot of benefis per days), by creating those indexes, the CDC process could get broken?
  • 04:55 gserdijn: I recently started using SQL Constant Care – should definitely have done so earlier. CC recommended to create a few indexes. Within 5 minutes after receiving the mail, I ran sp_blitzindex which did not show the recommendations as missing indexes. Can you explain this?
  • 05:53 Maksim Bondarenko: Hi Brent. How do You deal with periodical timouts in database (for instance, if they occures once a week, on Sunday nigth between 4 and 6 a.m.) ?
  • 08:08 END TRY BEGIN CRY: Hi Brent. Is there a way to have the CATCH block return multiple errors.. Trying it out ERROR_MESSAGE() will only return the last error raised and can sometimes be the lesser informative of the two (or more). Google only has posts from 10+years ago saying no, has anything changed
  • 09:52 ConfusedDBA: Hi Brent, my friend has odd way to declare dictionary statuses in Db. Instead of values: 1, 2… he uses 100, 200… The reasoning is as follows: in case of additional status he can add in between existing statuses, e.g. 150. What do you think about it? Have you seen it before?
  • 12:03 Slonik: What are your favorite extensions for PostgreSQL?
  • 13:07 Tim: Hi Brent. Msg 8623, Level 16, State 1, The query processor ran out of internal resources and could not produce a query plan. Do you talk about 8623 in your classes? We re-coded the sp to avoid the error but the geek in me would like to dig further into the cause of the error.
  • 15:08 Jr Wannabe DBA: In your view, where is the separation between SRE and DBA work and responsibilities? How do you see the landscape of having SRE, Devops Engineer, Dev DBA and Prod DBA in one organization?
  • 16:52 Chris May: Hi Brent, inside an actual execution plan it has both estimated and actual execution mode (row or batch), can these ever be different to each other and in what situations?
  • 17:44 A fan: How to use local temp tables particularly in an application using connection pooling, with respect to Erik Darling post “The Ghosts of Temp Tables Past”?
  • 19:53 Deshaun: Any Antarctica cruise plans?
  • 22:23 Curious DBA: Hi Brent. I’m sure you’ve built a strong nest egg over the years. Aside from generating additional sources of income (Consulting, Courses, etc), what is your investing approach? Have you had any costly mistakes or big wins?
  • 23:36 Efraim: What database technology should a new startup company use?
  • 25:00 Logar The Barbarian: Presuming the business has no performance concerns, at what point would you be concerned about a PRD application database running on Express Edition (2016+)? I am building out a plan and have discussed it with my manager given the resource and backup drawbacks I have found.
  • 27:23 Vishnu: Is a clustered index on a temp table ever a wise idea? How do you make this determination?
  • 28:24 Divya: How do you determine how many pages are being used for a single row in a table? Is 1:1 an ideal ratio?
  • 30:19 SleepyDBA: Hi Brent, Thank you for your excellent community support. How can we monitor the insert operation’s progress on a temp table? Is there a dmv in SQL 2022 that supports this?
  • 32:46 BobbyCC : My Friend’s TVF uses less resources inner joining a view simply concatenating a filtered View of 2 tables into a TVF filtering data within a 3rd table, than the same query does when using the view do the leg work but it works faster. How would I determine which is better?

How to Make Database Changes Without Breaking Everything

Brent Ozar
Development
8 Comments

You’ve got an existing application with a database back end. You’re thinking about changing the database, and you don’t wanna break stuff.

The most important thing to understand is difference between constructive and destructive changes, also known as additive and destructive changes, or non-breaking and breaking changes.

Constructive change examples: adding a new table or view, adding a new nullable column to an existing table, adding a new optional parameter to an existing stored procedure or function. These changes should never be made in a way that breaks anything that queries the database.

Destructive change examples: dropping a table or view, removing or modifying an existing column, changing an existing parameter. These often break apps that query the database.

Constructive changes give your code new options.
Destructive changes give your code new requirements.

Let’s start with the Stack Overflow Users table.

The public data dump version of the table has a column for Age, and let’s pretend that’s the current state of our application. Our application requirements have changed, and now we want to:

  • Store Birthdate instead of Age so users don’t have to log in and change it all the time
  • Calculate Age on the fly in the front end
  • Default everyone to a Jan 1 birthdate that would match to their current Age
  • Force them to set their correct birthdate on the next login

A destructive set of database changes would be to:

  1. Add a new non-nullable Birthdate column, with the YYYY/01/01 birthdate that works for their current Age.
  2. Drop the Age column.
  3. Require the Birthdate column to be entered whenever users edit their account, because our architect has mandated that the column is mandatory.
  4. Change the front end user-profile-edit screen to have a Birthdate field instead of Age.

All of that would have to be coordinated to happen at the same time, which means we would probably need an outage. That kind of choreographed dance is painful: sometimes it’s just not easy to change the database AND the app at exactly the same time.

Instead, a constructive set of changes might be:

1. Add a new nullable Birthdate column, and add a trigger to populate it. When someone inserts or updates a User row:

  • If a newly changed row’s Birthdate is set to null, set it to a YYYY/01/01 birthdate that works for their chosen Age.
  • If the Birthdate is not set to null – meaning, the app has been modified to pass in a valid Birthdate – then set the Age column’s value based on the Birthdate. Discard whatever Age was passed in – we’re going to be calculating it live down the road anyway.

The advantage of a constructive change like this is that from this moment forward, the application code can be changed at any time to either update Birthdate or Age. Just make sure you write your trigger to handle multiple rows.

2. Change the application to populate the Birthdate column instead of Age. Note that we could do either step 2 or 3 first, or even simultaneously. There are no dependencies between these two.

3. Backfill existing user Birthdate values. In step 1, we allowed the column to be nullable because that way, adding the column was a super-quick operation that didn’t require writing a ton of data to the clustered index. SQL Server can add nullable columns with only a brief schema lock. Now, let’s go back and run an UPDATE statement to populate null Birthdates with the YYYY/01/01 value. If the table was too large and workload too continuous to allow a blocking operation, we can nibble through the rows 1,000 at a time to avoid lock escalation.

4. Change the Age column to be computed based on their Birthdate. After #3 completes, all of the Users have a Birthdate. It might not be their actual birthdate – over time, hopefully folks will log in and change their birthdate to be accurate. (But they might not.) From this point forward, we need to start automatically calculating their Age. There are a few ways we could do this, but my favorite would probably be:

  • Rename the Age column to be Age_Deprecated. This way, we don’t have to rewrite the table’s clustered index to remove the old Age column and its data, which would be disk-intensive.
  • Add a new non-persisted computed column named Age. Because it’s non-persisted, this is also a super-quick, not-size-of-data operation.

At this point, any apps that still read the Age column can keep right on truckin’ with no changes. Any apps that try to write the Age column should simply have their proposed changes discarded by the trigger – the insert/update should still work, but just not affect the Age column’s contents directly.

Yes, constructive changes are more work for you.

The YOLO development pattern would be easier: just type BEGIN TRAN, change your tables, and send a Slack message to say, “Yo, sure hope the app handles that change I just made.” In the beginning of your career, working with small apps where you’re the only developer, the YOLO pattern is just fine. As your career and applications grow, though, you find the need for constructive changes only.

I’m only scratching the surface of the work involved. Things get more complex when you’re changing columns, like say breaking a UserName column into separate FirstName and LastName components. (Which, frankly, is a bad idea anyway.) To learn more about constructive changes:

It’s Friday. I’m Not Answering These 14 Office Hours Questions.

Brent Ozar
Production Database Administration
30 Comments

Sometimes, I don’t care if questions get a lot of upvotes at https://pollgab.com/room/brento – I just don’t wanna answer ’em. They’re not necessarily bad questions, but I’m just not interested in them, or I’ve answered them repeatedly, or I don’t have a good answer.

Maybe y’all do, though, so I’ve numbered these. If you want to leave your answer in the comments, prefix it with the question number you’re answering.

  1. Neil: A friend has a table where the primary key is a guid and clustered index, which is leading to index fragmentation and slow inserts. Any advice on how to reduce the index fragmentation? The guid is created application side, and can’t use sequentialuids. Thanks.
  2. Aussie Greg: Hi Brent, Thought i’d take a splash and ask a question (*new pool humour) In management studio, how can I limit the database list to those a user has access to? I can see the DENY VIEW ANY DATABASE but don’t want to make users DBOs
  3. Heather: What is your opinion of SQL Test app (https://www.sqltest.org) vs SQL Query Stress app?
  4. Erzurum: What should Microsoft do if they want lure SQL Server on AWS users over to Azure?
  5. Arslan: What’s your opinion of entering confidential info in chat gpt? Will we see AI therapist chat bots?
  6. Jack: In SSMS, when viewing the index “Rebuild All” screen to see the index frag percentage, is that percentage “internal” or “external” fragmentation? Corollary question: what about the avg_frag columns from the sys.dm_db_index_physical_stats. Thank you for your time.
  7. The Kevin: I’m starting to look at “Stolen Memory” statistics, I took your classes years back and don’t recall this discussion. EG: I have an instance with about 20 databases and 50Gb of RAM. I see the bufferpool using 40GB and stolen Memory at 9GB. should I beg Paul White? <ducks>
  8. gotqn: In terms of security, is it OK to expose your database to tools like GitHub Copilot in Azure Data Studio? Someone will know that your email address column is not encrypted or a stored procedure is not parsing its input parameters when dynamic T-SQL is built.
  9. Dwayne: What is your opinion of the QUERY_OPTIMIZER_HOTFIXES = ON DB setting?
  10. Chuck: Does running a lengthy (days) D.W. export query using NOLOCK impact the transaction log less than without NOLOCK?
  11. Harrison: Sorry if this is too low-level- I am mostly wondering what you would look for on an internship application that would make a candidate stand out. (Types of projects, experience, etc)
  12. Venkat: What is the best way to get/print/log entire TSQL stack trace in a stored proc to identify the root sproc that initiated an sp invocation?
  13. Inquisitive App Developer: Why are wait types all in UPPPER_CASE?
  14. dbant: We have a large transaction table with 99% fragmentation that needs index rebuilding without facing any block and performance issues.There is a brief period when we do ONLINE rebuild where blocking will occur which we are not allowed. Are there any other way to accomplish this?

[Video] Office Hours: Github Copilot in Azure Data Studio

Brent Ozar
Videos
1 Comment

I start today’s Office Hours by playing with the very underwhelming Github Copilot in ADS, then take your questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Start
  • 04:06 Github Copilot in Azure Data Studio
  • 24:36 Byron: Seeing plan cache queries for a long since retired table. What is best way to track down the app querying this table?
  • 25:57 Eduardo: What is your opinion of ApexSQL SQL Plan viewer? Feels like a competitor to SQL Sentry Plan explorer.
  • 26:33 SQLrage: Are their any tips or tricks (that aren’t out there regarding patching or page lock in memory) to prevent Non Yielding Scheduler errors related to IO issues? Seeing it a lot in a high transaction system and MS comes back stating IO problem is the root cause. Thanks brotha!
  • 27:23 GP Geek: I’ve a case where Index REBUILD runs every week, but the DB tables keep showing a fragmentation of over 98% every day.. what’s wrong ?
  • 28:05 Nicolai: What is your opinion of the Microsoft MVP award?
  • 32:37 Mr. SqlSeeks: When using a UNION operator to join the results of multiple queries, does the order of the queries affect how SQL Server gets the data?

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
21 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.

Announcing 30-Day Free Trials for SQL ConstantCare®. Check Your SQL Servers Now.

Brent Ozar
SQL ConstantCare
3 Comments

You’re responsible for the health and performance of your company’s SQL Servers.

You can’t afford most monitoring products because they’re priced per-server. Your manager just can’t justify spending tens of thousands of dollars.

Besides, monitoring tools just spam you. You end up with an Outlook rule that dumps all their emails into a folder, and what’s the point of having a monitoring system at that point?

Meet SQL ConstantCare®. For one low price, we send you just one email per server per day, only when there are specific tasks you need to perform on that server.

Over 300 companies rely on it every day to monitor over 2,000 SQL Servers.

This month only, we’re offering a free 30-day trial: sign up and we won’t charge your card for 30 days. Don’t like it? Don’t feel like you learned enough about your servers? Just cancel anytime before 30 days is up, and you won’t pay a thing.

Wanna learn more? Check out the quick start instructions and the frequently asked questions.

Let’s see what you learn about your SQL Servers!

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?