Product has been added to your cart.

Blog

  1. Home
  3. Blog

Office Hours: Questions That Didn’t Make the Cut

Brent Ozar
Production Database Administration
15 Comments

Sometimes, y’all post questions at https://pollgab.com/room/brento and they don’t get a lot of upvotes, and reading through ’em, I bet it’s because other people don’t quite understand what you’re asking. I think there might actually be a good question at the heart of it, but … I’m just not sure what it is, and it needs to be rephrased.

Here’s a rundown of some of ’em that came in recently. If you recognize one of these as yours, you’re welcome to re-submit it at PollGab, but with clarification.

MonkeySQLDBA: Recently watched one of your video on fragmentation, great stuff. quick question, if your fill factor is set to 80, will the internal fragmentation get to 70% faster than leaving fill factor to 0?

Zoom out: what problem are you trying to solve? What’s the action that you would take based on this knowledge? Think about those, and then re-post the question.

Piotr: If SQL Server and PostgreSQL were aircraft which aircraft models would they be? F14 vs SU57?

I love the question, but I don’t know enough about aircraft to answer it. I don’t even know if those two airplanes are good, bad, or the same, hahaha.

Keith: Hey Brent! Is it safe to upgrade my Azure SQL database from compatibility level 140 to 160 just to be able to run the GENERATE_SERIES function?

Is it hard for you to create a numbers table? Like, really? I’m not being sarcastic, but if you need a list of numbers, why wouldn’t you just create one?

I Cannot Do This Alone: I’m sure it has always been hard to get skilled help, BUT… have you ever heard of “non-tech” companies sharing there tech talent in like a pool? I know I’m on the hook for a trashing here, but better by you than my CIO 😉

I think you’re describing outsourcing – having a group of full time tech employees that you can call on whenever you need them, and just pay for what you use.

Brent Reading Book
“Can you show us your feet?” Wat?!?

Henry: I am planning on an in place SQL Server upgrade from SQL Server 2012 to 2016 on a Windows Failover Cluster. All of my databases are in Full mode with Transaction logs taken every 5min. Should i put the databases in Simple during the upgrade?

Don’t upgrade in place, period.

Government Cheese: How do you like to measure IOPs for SQL Server storage (bare metal and cloud VM)?

First, I don’t, but even if I did – are you talking about measuring how many they already consume, or how much a new server provides? If you’re asking how many they already consume, how is that useful? You don’t know if the users are happy or not, or if you need to reduce storage throughput or increase it.

ConsultantWannabe: Hey Brent, you teach we should stand next to expensive things (like SS or Oracle) as contractors/consultants, any advice to identify our own expensive thing to stand next to? Obviously apart from standing nex to SS. Thanks

Are you … asking me … how to find out how much things cost? I’m confused. Why wouldn’t you just … ask management what the most expensive thing in the shop is?

Jeremiah Daigle: I have a server that has 2 8Core CPUs, and only have 8 enterprise core licenses. I was planning to just remove one of the CPUs, but ran into issues not having the blank to put back in. Is there anything to be concerned about by turning down each CPU to 4 cores in bios instead?

Having a blank to put in? I’m not sure what you mean – you shouldn’t need a “blank” CPU. I think someone’s pulling your leg, like they’re telling you to go get blinker fluid.

Maksimilian: What’s the best technique for a SQL sproc to self audit the params it was called with?

Self audit? Have the proc log them to a table. (I think I might be misunderstanding the question because it seems so obvious, but if you want to log something, and you’re in a database, well, uh, put it in a table.)

Eduardo: Linked-in provides automated public notification of completed course training. How should DBAs notify potential employers of completed Ozar training?

You mean … how do you add things to your resume in LinkedIn? I’m genuinely confused – are you asking how to edit your resume? I’m guessing you just click Edit on your profile, right? Put in whatever text you want there. If you’re saying that you have a problem because you need automation every time you complete any of my courses, and I have so doggone many of them, then stop putting each one – just put Fundamentals and Mastering.

Hangman: When is sp_whoisactive context_switches a useful metric for performance troubleshooting?

Ask whoever told you to look at that metric. Otherwise, don’t walk into the airplane cockpit, point at a gauge, and ask the pilot, “Hey, what’s that dial mean?” That’s not an effective use of anyone’s time. SQL Server is way worse than an airplane cockpit: there are precisely 1.21 gigawatts of metrics out there, and most of ’em just aren’t useful.

Eduardo: Is it good idea to start identity integer cols for new fast growing tables at the max negative value for a big int? Do you see this much in the field?

It’s fine. I almost never see it.

Tony: Will you be purchasing TSQL fundamentals 4th edition?

No. I’m sure it’s good, and I’m sure you doubt my T-SQL abilities, fair enough, but I’ve moved on to learning other stuff.

Isaac: How do you find all the queries that are using the kitchen sync query pattern (Col1 = @Col1Val or Col1 IS NULL) AND (Col2 = @Col2Val or Col2 IS NULL)? How do you find the worst of the worst?

Instead of looking for anti-patterns, ask, “What are the 10 worst-performing queries that I need to tune, and what are the anti-patterns in those?” That’s what sp_BlitzCache does.

TheCuriousOne: Hi Brent! From your perspective, is there any open problem/issue preventing a problem free upgrade from SQL Server 2019 to 2022 and if so, what are the gotchas to look out for?

Microsoft used to publish detailed upgrade guides for each version of SQL Server, but they stopped doing it. Check out the most recent one from 2014 (PDF) and that’ll give you a rough idea of how complex it is to migrate an existing environment.

Eduardo: What is your favorite graph database and why?

I don’t use any myself, so I’m not qualified to answer that.

Wasn’t_Me: We are thinking about switching from Azure to AWS. On docs.aws.amazon.com I find this phrase: “When you set up an Amazon RDS DB instance for Microsoft SQL Server, the software license is included.” What?? Does it means that on AWS I don’t have to pay SSRS, SSIS, SSAS?

Licensing is included in the hourly rate, yes. Amazon also offers bring-your-own-licensing. Keep in mind that you said RDS, and RDS doesn’t have SSRS, SSIS, and SSAS – you’ve got a lot more reading to do. Fortunately, I’ve got a training class to help.

Marian: Hi Brent! Have you even been to Romania? Would you consider attending some big tech event in Romania in the nearby future?

No, and since the pandemic, I’ve cut back a lot on my conference schedule. I’m sure Romania is nice, but I did a quick Google search and didn’t see any SQL Server conferences in Romania. I’m not really interested in non-SQL-Server conferences – when I want to learn other technologies, I tend to use cheaper/easier methods rather than traveling.

Neil: I set up all my SQL servers with TCP/IP enabled only. A developer is trying to connect with named pipes. Should I enable named pipes or force them to use TCP/IP?

I don’t have any opinion on this one whatsoever. (I don’t think I’ve ever disabled named pipes.) Why did you disable it?

[Video] Office Hours in Cartagena, Colombia

Brent Ozar
Videos
1 Comment

Before heading out to Old Town for sightseeing, I went through your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Start
  • 00:44 Haydar: Is there a good way to suppress part of TSQL batch from showing the query plan in SSMS but having the rest of the batch show the query plan?
  • 02:13 Mike: Hi Brent, so simple blocking on a busy server, can cause a failover ? Is it because of exhaustion of thread workers, resulting in THREADPOOL waits? But I can’t understand the mechanism, how this can lead to failover – could you please explain ?
  • 03:03 Greef Karga: Please describe the most strict / locked down environment you have worked in and the challenges it posed.
  • 06:11 Q-Ent: Hi Brent. Have you ever used buffer pool extension as an option for better performance?
  • 07:29 Hangman: When is sp_whoisactive context_switches a useful metric for performance troubleshooting?
  • 08:36 AFriendOfMineAsk: Howdy Sir! Our CTO wants a monthly dashboard of all Production SQL Servers, can you please give some advice or any tips on what KPIs should include or should i have to start looking for a new job? Thanks and have a great day Sir!
  • 09:57 Jester: What tool do you like to use to find the worst performing queries with implicit conversions?
  • 10:55 Yaakov: What is the best way to print the line number currently executing inside a TSQL Sproc? 11:45 AM, no I’m not a morning person.: using where X in (1,2,3,4,5, etc) for selecting random sets of items. Is there any performance reason not to do this and instead use parameters or put them into a temp table first? Is it right to assume this won’t be able to be a cached query?
  • 12:59 Mike: Since 2005, new version of SQL Server had been released every 2, maximum 3 years. What is your opinion on when the next version (after 2022) will come out ? Will it be late 2024, sometime in 2025, or later, and why ?

It’s Monday. Do You Know Where Your PowerShell Training Is?

Brent Ozar
PowerShell
0

You’re over the hump! Just two weeks left to go.

All month long, I’m giving away our newest class, Fundamentals of PowerShell for DBAs. Here’s what you’ll be learning this week:

In case you didn’t know before now, go head over to the class and watching the Before the Class modules that explain how to set up your workstation to follow along.

Not patient? Can’t wait for the rest of the videos to go live? Recorded Class Season Pass Fundamentals holders can jump in now – it’s included with your existing membership. Enjoy!

Slow Startup, Failover, and Restore Times with In-Memory OLTP

Brent Ozar
In-Memory OLTP (Hekaton)
14 Comments

When In-Memory OLTP came out, I worked with it briefly, and I remember coming away thinking, “Who in their right mind would actually use this?” I was so horrified that I wrote a presentation about it and gave it at a couple of conferences:

Many laughs were had, but obviously that didn’t make me any friends at Microsoft, ha ha, ho ho. I figured I was done with Hekaton, and I wouldn’t ever have to see it again because y’all all saw the video, and you’d never be crazy enough to implement that feature.

<sigh>

Well, here it is in 2023, and recently I’ve talked to a couple of architects who wish they could go back in time and watch that video. In both cases, they suffered from the same issue.

The short story is that the more data you put into durable In-Memory OLTP tables – and even just 5GB of data can hit this issue – the more your startups, failovers, and restores turn into long stories, to the point where other databases on your SQL Server are practically unusable.

Setting up the 5GB Votes table

To demonstrate the problem, we’ll start with a large copy of the Stack Overflow database. I’ll drop my nonclustered indexes just to make the next screenshot more clear, and then I’ll list the tables in the database to find a good candidate for the demo.

The highlighted table, dbo.Votes, has about 150 million rows and takes up about 5GB of space. That’s not big data by any means, and we can easily fit it in memory on our 8-core, 60GB RAM server. Let’s migrate it into In-Memory OLTP in order to make performance go faster:

After migrating that 5GB table to In-Memory OLTP, it’s taking up 5GB RAM, right? Well, no:

Try 28GB RAM. That’s why we have a sp_Blitz check to warn about high memory usage for In-Memory OLTP:

As to why an uncompressed 5GB rowstore table takes up 28GB RAM, that’s a topic for another blog post. (Sometimes, I imagine rowstore tables being introduced today – people would think they’re the most amazing thing ever.)

What happens when SQL Server restarts?

For most of us, when we restart SQL Server, our biggest concerns are things like dropping connections, losing performance metrics, and starting over again with a fresh buffer pool. In-Memory OLTP users have a bigger problem: when the SQL Server restarts, CPU goes straight to 100%, and stays there:

Ouch. Why is CPU so high? Because with In-Memory OLTP, SQL Server starts up the database by reading the In-Memory OLTP tables up into something we call “memory.” It’s using less memory than before, thank goodness – it’s “only” 13GB – but 13GB of data still takes a long time to pull from disk and reconstruct in memory. (I’m not going to go into technical terms like crash recovery phases here because it’s not necessary to convey the overall problem.)

You can see exactly how long it takes in the SQL Server logs. In this case, the StackOverflow database recovery took 50 seconds – and in this case, because our server’s got really fast storage, the storage was able to deliver data so quickly that it pegged CPU at 100% for all 50 seconds.

During startup, wait stats show a lot of wait times on XTP_PREEMPTIVE_TASK and SLEEP_DB_STARTUP:

There’s also blocking, which is kinda funny – anything that needs to query sys.databases gets blocked, like IntelliSense queries running in the background:

This happens during failovers, restores, and attaches, too.

Startups, failover clustered instance failovers, and restores all exhibit the same problem: they need to start up the database, and doing that means reconstructing the In-Memory OLTP data from scratch.

This is particularly problematic for shops that use Always On Availability Groups. Say you want to add a new database into an existing cluster, and the new database happens to use In-Memory OLTP. You restore it on the primary, and … boom, the server hits 100% CPU usage for an extended period of time, which affected all the running queries on existing databases on that server.

Here’s what CPU looks like at the end of a database restore:

CPU was relatively low while the restore was processing, but once SQL Server was done writing the files, CPU went straight to 100%. Why? Because it needed to bring the database online, which meant reading the In-Memory OLTP data from disk and reconstructing the table.

Even if you just attach an In-Memory OLTP database – a process that is normally near-instantaneous – you bring SQL Server to its knees while it reads through that data and populates memory.

In my example, I don’t have indexes on the In-Memory OLTP table, but if I did, the situation would be even worse. Indexes on these tables are only kept in memory, not on disk, so they’re reconstructed from scratch at startup, failover, restore, and attach time.

Bottom line: the more In-Memory OLTP durable data you have,
and the more databases you have that use it,
the worse this gets.

Trace flags 3408 and 3459 don’t help on SQL Server 2022, at least.

Among other sources, Konstantin Taranov’s excellent trace flag list reports that trace flag 3408 forces all databases to use just one thread when starting up. I don’t think I’ve ever needed to use that trace flag, but it doesn’t appear to help here. In my lab, I set up 3408 in the startup config, restarted the SQL Server, and CPU still went straight to 100%:

Furthermore, 3408 doesn’t appear to have the desired effect on SQL Server 2022 (and I didn’t bother checking on other versions, since it wouldn’t have helped my client, as they had a lot of databases with In-Memory OLTP.) Databases are still starting up with multiple threads, which would drive CPU to 100%:

Same problem with trace flag 3459, which is supposed to disable parallel redo for AGs, but that’s unrelated to database startup, as we can see by the flames:

And just because someone’s going to ask, no, setting MAXDOP to 1 has no effect on system processes like bringing databases online.

So how do we get faster startups, failovers, and restores?

In theory, you could add CPUs. In practice, that’s a really expensive way to solve this problem, and it doesn’t work when you have several (or heaven forbid, dozens) of databases that use In-Memory OLTP. SQL Server doesn’t just start up one database at a time – it starts them in groups, which means you can saturate lots of CPUs on restart. If you need to dive into the internals of this, here are a couple of resources:

In theory, you could use slower storage. Bear with me for a second: if your primary concern was that other databases on the SQL Server were unusable while In-Memory OLTP databases came online, you could actually put the In-Memory OLTP filegroup on slower storage. In this 5GB Votes table example, that does actually lower CPU usage during most of the process, only driving it to 100% near the end of the process:

(Yes, I actually tested that for one particular client, who was curious.) However, that also makes the In-Memory OLTP databases take even longer to come online! In my example with a 5GB table, the database took ~80 seconds to come online instead of ~50 – making your RTO goals tougher to meet.

In practice, minimize what you keep in In-Memory OLTP durable tables. A few ways to do that:

  • Make them schema-only instead, and don’t keep the data around. Yes, you lose all data when the SQL Server goes down, but if you’ve been using this feature for temporary data anyway, like session state, reconsider whether you need to keep it.
  • Sweep older data out to conventional tables. One of Microsoft’s design patterns for this feature is to use In-Memory OLTP tables only for ingestion, but then after the data’s been absorbed into the database, archive it.
  • Use regular tables instead, not In-Memory OLTP. Conventional tables don’t interrupt startup.

[Video] Office Hours at Sea: Heading to Jamaica

Brent Ozar
Videos
0

I’m on a boat! I’m on a 10-day Panama Canal cruise, and I stopped (well, not the boat) off the coast of Florida to answer questions y’all posted at https://pollgab.com/room/brento.

  • 00:00 Start
  • 00:56 DBe: In several places I’ve worked, it’s been “policy” to automatically restart heavy-use SQL servers off-hours on a regular cadence. Usually monthly but in a couple cases weekly. Is the scheduled restarting of SQL servers a common, viable business practice?
  • 03:53 BBDD: Hey Brent, do you know why a heap with one nvarchar100 column and 1row can be with size 2 GB and all used.What might have happened to the poor table. I tried to reproduce it creating the same table with inserting, deleting truncating but the size when it was back to 1 row was 1mb
  • 04:52 Brandon: Changing order of joins made a BIG difference for me recently, I suppose b/c it helped SQL start in a better place on the search for a good plan before time ran out. This was on 2008. Have you run into this much & do you think it’s less of an issue after 2008?
  • 06:04 Gigiwig: Hi Brent, a friend of mine has a server instance with a sql_… collation. One of the dbs is from a vendor and insists on a different collation. Can that cause problems concerning joining to system tables, using tempdb? How did you handle collation mismatches in the past? Thx
  • 07:43 Pamela Anderson: Has the problem introduced by PSPO implementation in SQL Server 2022 CTP – impossibility to tell which statement relates to which batch (or SP?), that you blogged about last year – been actually fixed in 2022 when it came out ? Or we have monitoring broken ?
  • 08:35 Runyan Millworthy: What are the top signs that a shop needs more SQL DBA’s?
  • 11:22 Kajimial: Hugs Brent, watched your video for optimizing checkdb? Isn’t with physical_only supposed to be faster? I ran it on 8TB db and completed for a little bit over a day and just checkdb completes for 6h? How can this be possible? DB with no load and running it with maxdop 0 with Ola
  • 13:27 Craig Gardner: I’m doing a penetration test on a server and have come across a SQL Server. Build number is 12.0.937.0 and version is 2014 (although I doubt this). I can’t find any information on that build number. Do Microsoft have a list of build numbers for Azure Managed Instances?

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

Brent Ozar
Who's Hiring
12 Comments

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

I’m Coming to Boston for SQLSaturday!

Brent Ozar
Conferences and Classes
0

I’m coming back to Boston for SQLSaturday this October 14th!

On Friday the 13th (muhaha), before the event, I’m teaching a one-day pre-conference workshop on Mastering Query Tuning.

Mastering Query Tuning

You need to speed up a SQL Server app, and you’re allowed to change the queries and indexes – but not the server hardware or settings. Good news – I’ll teach you how in a day of learning and fun. Join me, Brent Ozar, as I explain how to make your SQL Server apps go faster.

We’ll cover:

  • How SQL Server builds query plans
  • How to choose between CTEs, temp tables, and APPLY
  • How to tune for SELECT * and lots of rows
  • How to write dynamic SQL that scales
  • How to avoid pitfalls like deadlocks and bad batching

Save $50 on early bird registration now. The class will be held at the Microsoft Technology Center at 5 Wayside Road, Burlington MA 01803. Seating is limited to 114 folks. I will be there in person, and it will not be recorded or broadcast online.

Other helpful links:

I’m Coming to Gothenburg and Oslo This Fall!

Brent Ozar
Conferences and Classes
2 Comments

Back in 2020 before the wheels came off the world, I’d scheduled visits to Gothenburg and Oslo for their annual SQL Server events. Now that things are back to normal, it’s back on!

Data Saturday Gothenburg, Aug 26 & 28, 2023 – Monday Post-Con: Mastering Server Tuning – You’re constantly facing new performance challenges on different servers. You need to quickly diagnose a server’s bottleneck, and learn the most common ways to fix each bottleneck. Let’s tackle it together in a fast-paced 1-day version of my full 3-day class, and because we can’t cover everything, attendees get a year’s access to the full 3-day recorded class too. Learn more and register for the workshop now.

Data Saturday Oslo, Sept 1-2, 2023: Friday Pre-Con: Mastering Query Tuning – You need to speed up an existing app, and you’re allowed to change both queries & indexes, but you can’t throw hardware at it. Let’s tackle it together in a fast-paced 1-day version of my full 3-day class, and because we can’t cover everything, attendees get a year’s access to the full 3-day recorded class too. Learn more and register for the workshop now.

Can You Nest Transactions in SQL Server?

Brent Ozar
T-SQL
4 Comments

To find out, let’s set up a simple status log table:

And then let’s try a two-part transaction:

Right now, SQL Server shows that I have 2 open transactions:

What Happens If I Roll Back?

But what does “2 open transactions” mean, really? If I do a rollback, what gets rolled back? Let’s find out:

The results:

Both of our transactions were rolled back, and there’s nothing left in the table.

Let’s Try It Again, but Commit This Time

Roll through the same setup code, but then commit:

The results are a little weird:

Only one transaction is shown as open – and right now, both rows are in the results table. If we roll back now, what happens?

Even though we said we “committed” our inner transaction, it doesn’t matter – BOTH of our transactions got rolled back.

You Can’t Really Nest Transactions Like This.

Think of @@TRANCOUNT as the number of times remaining that you either need to commit or roll back. If you open a bunch of nested transactions in a row, it’s up to you to commit every single one of ’em. If ANY of them are rolled back, EVERYTHING is rolled back.

This is particularly troublesome if you try to break up locking by using a bunch of little transactions wrapped in one big outer transaction. I recently had a client who thought they could:

  1. Start an outer transaction for a business process
  2. Start an inner transaction, acquire locks on OrderHeader, make changes, commit, and release the OrderHeader locks when the transaction committed
  3. Go on to another inner transaction, acquiring other locks on OrderDetails, while other processes were able to work lock-free on the OrderHeader table because step 2’s locks were released

But as you can see here, even when you commit one part of a transaction, SQL Server still isn’t quite done with it. It has to maintain those locks because if any of your open transactions are rolled back, SQL Server’s gonna roll back everything you did – even the parts you thought were finished.

Update: a couple of commenters have pointed out SAVE TRANSACTION, and I gotta say I’m not a fan of that because of the complexity, especially around lock escalation. If you choose to use that feature, read the documentation really carefully, especially around lock escalation. Saved transactions don’t release the locks once they’ve been escalated, and if you’ve been through my Mastering classes, you know how easy it is to hit lock escalation.

Find 40 Problems in This Stored Procedure.

Brent Ozar
T-SQL

Aaron Bertrand posted a challenge:

We’re going to use the AdventureWorks sample database (get your copy here), where the folks in marketing requested a list of users to e-mail a new promotional campaign. The customers need to meet at least one of the following criteria:

  • last placed an order more than a year ago
  • placed 3 or more orders in the past year
  • have ordered from a specific category in the past two weeks

These criteria don’t have to make sense! They just need to make the query a little bit more complex than your average CRUD operations.

First, we’re going to update Sales.SalesOrderHeader to modern times so that dates make sense relative to today. We only care about OrderDate here, but there are check constraints that protect a couple of other columns (as well as a trigger that sometimes fails on db<>fiddle but that I have no energy to troubleshoot):

This stored procedure that someone wrote will now return data (without the update, it would be hard to write predictable queries based on, say, some offset from GETDATE()).

Now, it’s your turn: how many bad practices can you find in that code?

Aaron’s answers are over here. I’ve turned off comments on this blog post because if you have any questions or thoughts, you should post ’em on Aaron’s blog post. The only reason I’m posting this here is that I bet a lot of y’all aren’t subscribed to Simple Talk, and I wanted to break this challenge up into two parts – the code to review, and the answers. Do not look at the answers until you’re done with your code review. Have fun!

Office Hours: Short Text Answers Edition

Brent Ozar
Production Database Administration
1 Comment

Not all of the questions y’all post at https://pollgab.com/room/brento require long-winded responses.

Brandon: Do you seen a rise in json queries to address impedance mismatch between data/objects? I struggled with EF to produce a query that was neither simple nor complicated against a properly designed db (according to 2 experts). Dropped EF; used json query in stored proc; it was magic.

Brent OzarNo.

Rufus: What are the best ways to determine if two large query plans have the same shape?

Put them side by side in SSMS and zoom out.

Stan Redman: What is your opinion of the SQL force encryption setting?

I don’t do security or compliance work, so I have no opinion.

Dance Monkey: Do you think there would be much interest for someone to host a PostgreSQL office hours on Youtube/Twitch?

Yes.

Eduardo: What is highest number of DBs you could safely put in an always on availability group?

Read this.

Marian: Hi Brent! Would you recommend any SQL Server Certification that would be easily recognizable? I know Microsoft retired those on SQL Server, and focus on Azure.

No.

Hondo: What is the top batch requests per second (sustained) that you have seen in the field? What were the specs for the underlying hardware to support that kind of load?

At the moment, 150K sustained, 4 socket, 64 core box with 256GB RAM. Small data set, just lots of tiny well-tuned queries that can’t effectively be cached client side.

Rooster: How do you determine the the optimal Virtual Memory page file size for bare metal Windows Server 2019 running SQL Server 2019 Enterprise?

Read this.

Sigríður: Is there anything we can do to to influence the stat sampler so that a given index key (customer in this case) is included as one of the histogram 200 steps? We know who the top 200 most important customers are by $$$$.

Yes, filtered statistics. They’re just like filtered indexes – put a where clause on ’em.

Tim: Greetings Brent. Are you winging it? Or do you sneak a peek at pollgab before starting the stream, so that you’re prepared? If you are winging it, how long did it take until you were confident in your knowledge and no longer had to prepare?

This one takes a little longer to answer, but I’m putting it in this batch because it’s fun.

Before starting the show, I look at the PollGab queue to remove anything that might be offensive or isn’t a good fit for my show (like if someone asks a MySQL question.) In the process of looking at ’em, I at least see what’s coming. I don’t ever go Google for stuff – either I know the answer, or I’m going to tell people what I’d Google, but that’s it.

How long did it take? Well, Office Hours is a really good simulation of what it’s like to be a consultant. Client staff constantly throw questions at you, and you have to be really comfortable either saying you know the answer, or saying you don’t, but you know where you would look. You can’t feel guilty about saying you don’t know. So I’ve been really comfortable with what I do know for a long time – but it’s just that the scope of that surface area slowly and steadily grew over time.

I still make mistakes! In a recent show, I said I didn’t think unique constraints also created a unique index under the hood. One of the viewers pointed out the mistake. Strangely, I get excited about that because it means I still have stuff to learn!

Updated First Responder Kit and Consultant Toolkit for February 2023

Brent Ozar
First Responder Kit Updates
0

Thanks to this technique to run SQL Server on Apple Silicon chips, I’m now developing exclusively on my Mac! I’ve been using a Mac for over 15 years, but in the past, I’ve always used Windows at some layer somewhere. This time around, it’s all Mac the whole way down, which is kinda nifty. Makes my release process easier.

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_BlitzCache Changes

  • Enhancement: when using @SortOrder = ‘all’, there’s a new pattern column to show which metrics sucked about the query, and it’s included in the table output. (#3172, thanks Adrian Buckman.)
  • Fix: case sensitivity issues on joining to sys.all_columns. (#3233, thanks sm8680.)

sp_BlitzFirst Changes

  • Fix: QRY_PROFILE_LIST_MUTEX lock timeouts on sys.dm_exec_query_statistics_xml. (#3210, thanks sqlslinger.)

sp_BlitzLock Changes

  • Fix: error converting data type nvarchar to bigint. (#3201, thanks Erik Darling.)
  • Fix: arithmetic overflow in wait_time_hms when wait time added up to more than 2147483647. (#3215, thanks Vlad Drumea.)
  • Fix: string or binary data would be truncated in table tempdb.dbo.#deadlock_owner_waiter. (#3206, thanks johnkurtdk.)

sp_BlitzWho Changes

  • Fix: shows procedure definition even if the current statement isn’t in the plan cache. (#3163, thanks Adrian Buckman.)
  • Fix: tempdb allocations did not include internal objects such as worktables and workfiles. (#3174, thanks Adrian Buckman.)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

[Video] Office Hours Brought to You by Quest Software

Brent Ozar
Videos
2 Comments

Today’s episode of Office Hours is brought to you by Quest Software. I went through your top-voted questions from PollGab.com/room/brento, and, uh, kinda looked like I was sponsored by Fendi while doing it, hahaha:

Wow, those logos are bigger than I thought. Here’s what we covered:

  • 00:00 Start
  • 01:20 reluctantly_tolerant : I used FCI instead of AG for server w/500 DBs due to worker thread limit. I used NetApp ONTAP filesystem and was very impressed by performance, and ability to spin up clone of production in under 2min. Now I feel this is the gold standard. Why go back to AGs, even where possible?
  • 02:42 Simon: First off: I know we should not use the NOLOCK hint :] But can NOLOCK queries even cause index corruption on queries that is NOT modifying data? Perhaps if combined with persisted computed columns? (SQL Server 2019, latest CU).
  • 03:58 LogarTheBarbarian: Hello hello Brent! What questions come to mind if you came upon MSSQL instances that modified the Ola DBCC CheckDB job to run CHECKTABLE, CHECKALLOC, and CHECKCATALOG on different days rather than CheckDB in its entirety?
  • 05:15 Dru: When should a unique constraint be used vs a unique index?
  • 06:32 Sigríður: What are your favorite things about living in San Diego and Las Vegas?
  • 08:38 Sigríður: What is your favorite standing desk and why? What are the specs?
  • 09:18 Peter Seale: VAGUE question: we found that 80% of our db data is used by indexes. Is this normal-ish? Any vague tips for reducing our data usage? Most tips focus on data used by tables, and never mention data used by indexes.
  • 09:56 Piotr: What are your thoughts on upgrading from SSRS2014 where we have numerous reports? Best to migrate to SSRS2019 or skip entirely and starting learning how to migrate to PowerBI server? Currently on-prem but planning on moving to Azure.
  • 12:54 Maksim: What do you use for motivation to read tech docs and tech books?
  • 14:05 PartyPerson: Hey Brent, what is the story behind the “California deserves whatever it gets” sign behind you?
  • 15:20 TeeJay: A lot of our reports are computationally expensive and run repeatedly for each subscription. I assume that the solution to this is to pre-aggregate the DW data as much as possible, but suspect that I’m probably re-inventing the wheel. (Budget: £0) What words should I be googling?
  • 16:34 Alex: Hi Guru, I have some small tables on Azure SQL DB with very little use. Once in a while a scheduler runs a SP that performs one insert to a table in 2 concurrent threads. I have a gap in identity column. I added TABLOCKX as suggested by docs but problem still arises.
  • 18:08 depthcharge: Hi Brent, have you ever encountered a scenario where you indexed to remove an eager index spool, and SQL Server ignores the index and continues spooling? Aside from index hints (which help, but we can’t change the code), any other clubs I can hit the optimizer with?
  • 19:13 toepoke.co.uk ;-): Hey Brent, In a recent office hours you spoke of encrypting data on the app side rather than the db side which I found interesting. How would this work from a sorting perspective, eg sort a UI table by Last name,First name, etc. Store the first letter ? Store the hash? Cheers!
  • 20:51 Q-Ent: Hi brent, are you aware of MCR(Maximum consumption Rate ) for CPU sizing? Do you think this is a reliable method ?
  • 22:01 Wren: Hi Brent! Building some reports for our SQL Server environments and I found one of your old (2009) replies on Stack about finding CPU time per database… do you think it’s a worthwhile stat to use to determine “what should move to cloud first”? Any improvements since SQL2008?
  • 23:23 Tim.: Hi Brent. I like the fundamentals of powershell. Will there be a mastering powershell? Will you be working with Drew more in the future?
  • 24:50 Stone Temple Pilot: How do you measure bad page splits for inserts on a poor clustered index?
  • 25:17 Paco: Hello Brent, I have a friend who is facing a server that has both threadpool waits and Non-Yielding Schedulers occurring around the same time, until they cause the AG to fail. Have you seen threadpool waits cause Non-Yielding Schedulers or vice versa?

[Video] Office Hours: Ask Me Anything About SQL Server

Brent Ozar
Videos
0

Y’all post and upvote questions at https://pollgab.com/room/brento, and I stream sessions on my Twitch.tv channel where I answer ’em and chat with the audience.

  • 00:00 Start
  • 02:09 Chad Baldwin: Hey Brent! “a friend told me” MSSQL will use an index’s stats to generate a plan, but not use the index itself.
  • 03:25 Jeremiah Daigle: Hey Brent, I’ve been unable to proceed past Compat mode 140… with recurring battles with “8657 – Could not get the memory grant” Is there a new configuration option?
  • 04:48 PatchesOHoulihan: Oh wise and benevolent Oz(ar), has there been any chatter around the DCOM hardening patches Microsoft started releasing last July, and the final March patch?
  • 05:20 Haydar: What is your opinion of the new optimized locking functionality in Azure SQL DB?
  • 06:43 CompletedFundamentalsAndMastering: sp_blitzIndex shows 3 minutes of lock waits on CX after just 2 days uptime (large, over-indexed table). 1 NCX shows 24s lock waits, but all others are 0, including one with 19 cols!
  • 07:39 Geoff Langdon: Hi Brent, When using AGs with a read only replica, there doesn’t seem to be a way to map a new user to the read only replica database on the replica
  • 08:47 Alex: Hi Brent, I’m conducting interviews for potential candidates in SQL. Currently I’m exposing Azure SQL DB to public IP so that candidates can connect from home to take a test.
  • 09:45 Peter: Hello Brent. To get a steady Plan Cache we set PARAMIZATION = FORCED on several DB’s. After tuning the most resource intensive queries, would you then recommend to go back to SIMPLE?
  • 10:53 Leif: A friend told me that an unused index can reduce performance of a select statement. Is that possible ?
  • 12:01 Boris: Ahoi! Is finnaly the time to migrate my old ETL SSIS packages to Azure Data Factory?
  • 13:56 thevibrantdba: My friend is a novice and is wondering if there are any known links to see at glance what SQL server version brought what feature for prospective interviews.
  • 16:11 Andrew P.: Hi Brent, my three person data team about to merge into a much larger team as part of a merger (an SME merging into an enterprise).
  • 18:14 Patricia Zysk: Using SSIS, SSRS, Agent jobs in daily on-prem work, do you recommend SQL Server in a VM or Azure SQL Managed Instance when moving to the cloud
  • 19:28 MI: Hi Brent, Sooo… how many queries have you had chatgpt optimize yet?
  • 19:40 Rando: Hi Brent! How does one confidently decide on a dump/backup interval for databases.. n times a day, etc.. What’s reasonable
  • 20:34 Clippy 2.0: Do you think ADS will become the better tool for query performance tuning in the long run?
  • 21:46 One_of_the_Party_People: Hi Brent. I work with a highly normalized database with nearly 2k tables. Some of the FK’d data will cascade 10+ levels deep. Is there a resource you can point me to for how to go about archiving data?
  • 23:01 LarrySQL: Hi Brent, I have a proprietary database where all stored procedures are encrypted (WITH ENCRYPTION) and some are slow. I can’t see execution plans in SSMS. I wonder if SQL SERVER can do optimization on this kind of encrypted objects.
  • 23:59 George : Do not upvote:
  • 24:31 Chris: Is there a good argument for continuing to take backups of read only databases, or do you take one last backup, validate it and test restore it then never worry about backups again?
  • 25:29 neil: sp_who shows like 1000 sleeping connections. sp_whoisactive just shows like 20 or 30 queries. sometimes Windows Events throws an error about being unable to reuse a spid. do i need to address this ?
  • 26:24 Q-Ent: Hi brent, Do you have any plans for discount offers other than black Friday for your classes ?
  • 26:43 Jeremiah Daigle: Have you run across scenarios where you had to turn MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF, in order to get around Memory Grant Errors related to Exceeding max config limits
  • 27:15 Champaign DBA: Do you have a blog post about the limitations of Azure features such as automatic index turning compared to the power of the sp_blitz tools?
  • 28:23 alwayslogshipping: From your experience, what is the notable missing link between skill set and the personality shops want to entrust with delivery of business values.
  • 30:13 BrentFan: Hi Brent, If you were tasked to take over SQL code deployment in production from the development team. How would you approach this?
  • 31:51 Maksim Bondarenko: What is the best (with mininum downtime) way to migrate databases between two different 2 node AlwaysOn Clusters (4 different servers)? SQL Server 2016 Enterprise to SQL Server 2019 Enterprise. One AG,few dbs and around 2 TBs of data. Also AG name must be the same after migration
  • 32:27 Nicolas: Hi Brent, a friend of mine wants to move a large column (a few KB) of an existing table to a second separate table to “optimize the logical reads on the Cluster index of the first table when the large column isn’t used”.
  • 33:17 Mr. SqlSeeks: I am researching Always Encrypted, trying to get around the cross-database query limitation. Are you aware of any way to use the same key in multiple databases?

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

Brent Ozar
Who's Hiring
5 Comments

It’s Valentine’s day – let’s do some matchmaking!

Is your company hiring for a database position as of February 2023? Do you wanna work with the kinds of people who read this blog? Let’s set up some rapid networking here. If your company is hiring, leave a comment.

The rules:

  • Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
  • An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
  • Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
  • Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
  • Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
  • Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.

[Video] Office Hours: Quiet Sunday Edition

Brent Ozar
Videos
2 Comments

While waiting for the dry cleaner to open, I went live to stream a quiet session going through a bunch of y’all’s questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Start
  • 01:45 Rollback is single threaded: Hi Brent! In microservices application architecture, Using a database per service or a shared database? I asked this because using a database per service is not straightforward and there are many drawbacks. Thanks!
  • 03:39 fajitapete: Covering indexes, what benefit is derived once you are past 2-3 columns, wouldn’t putting the rest as included be just as good
  • 05:07 ILoveData: SQL 2022 makes it easier to call external endpoints. In your eyes as a DBA, what acceptable use cases (if any) are there for a feature like this? I appreciate the idea, but we all know how this ends up once devs find out it exists…
  • 07:02 Dopinder: What is your opinion of SSMS 19? Anything to get excited about?
  • 07:52 Sajan: Do you have any interesting arguments to explain why you shouldn’t use Managament Studio on an instance with a SQL server? My argument was mainly that the price of the license is conditioned by the amount of CPU (which is wasted by unnecessary applications)
  • 08:47 Mike: My on-prem server has 16 physical cores / 32 logical cores. If we migrate this box to SQL Server on Azure VM, how many vCPUs we need – 16, or 32 ?
  • 09:54 Mike: When you deploy Azure VM with preinstalled SQL Server 2019 EE, it only shows projected VM cost. What will be the additional (hidden) SQL license cost $ per core / month? Is it $274 as in new billing model in SQL 2022 (selected during SQL install), or different amount ?
  • 11:04 DevInHiding: Hi Brent, an older colleague of mine claims that (at least in much older versions of SQL Server) that it is better to query bit data type fields as “WHERE fieldname anything other than 0” than “WHERE fieldname = 1”. Is there anything to that?
  • 12:06 Curious DBA: In what scenarios would you utilize CROSS APPLY instead of INNER JOIN? Is CROSS APPLY an optimal way to get SQL Server to do several backward seeks (1 per row) instead of a large forward seek? (I.E. composite PK (ID, DateTime) and want to return max(DateTime) for multiple IDs)
  • 13:14 zlobnyfar: WhatIsTheBestWayOfLogsGeneratingAbout CRUD interactions (changes in roles or permission) AND AUTHENTICATIONS (login attempts (success/failures) and attempts to elevate privileges (success/failures)) AND executed QUERIES stats AND SA Actions Thanks for comprehensive answer!
  • 15:32 Dont Bother Answering: Hey Brent, why does my query go parallel when it’s cost in sp_BlitzCache (33.7) is lower than cost threshold for parallelism (35)? Just looking to understand why, thank you!
  • 16:40 Trushit: What do you think will be the impact of tools like ChatGPT on SQL developers? Which role do you think will be most impacted : developer, development DBA or production DBA? What skills will remain relevant even when AI learns low level coding?
  • 18:09 BlackFriday-Bundle2: Hi Brent and thanks for the courses. What is your view on PAGE vs ROW compression in SQL Server? Would you consider it a bad fit for multi part NC where latter key parts and included columns are “hot”? Finally, does it ever make sense to change FILLFACTOR if compression is on?
  • 18:57 Fillfactor 1% for the win: Hi Brent, what are your thougths about creating 3 docker container on a physical box, one for dev, one for test and one for live. It’s for a (modern and classic) DWH environment. Live is using all resources during night, and during the day the developers can work on dev/test.
  • 20:22 YouGottaDoWhatYouGottaDo: Hi Brent, what’s your opinion on the new T-SQL snapshot backup functionality in SQL Server 2022? Do you see any hidden problem to be aware of?
  • 21:18 Brandon: Any insights to offer regarding how db design might differ (or if it should) when developing for microservices, and any resources or people to follow for further study? For example, do you find it common to have many DBs where traditionally there would only be one or two?
  • 22:48 RufusStone: Asking for a friend, what is a suitable punishment for someone who creates a database with space in its name?
  • 23:44 i_use_uppercase_for_SELECT: How do you manage expectations at your clients that not all index changes won’t have unexpected consequences? Create and index that helps several queries, but causes another to blow up because of a new query plan.
  • 25:26 Sajawal: Hi Brent, You are doing great for people like me who love to play with SQL. Would you please let us guide what is DOP feedback architecture in SQL Server 2022?

Free Training Coming Up! March is PowerShell Month.

Brent Ozar
PowerShell
29 Comments

You’re a production database administrator responsible for the health, security, and uptime of many database servers.

You’ve been pointing and clicking your way through SSMS for years, scripting out T-SQL to files, but… when you need to do the same task repeatedly across several servers, it’s a bit of a pain.

You’ve told yourself someday you’d learn PowerShell to do repeatable, reliable automation.

That time is March, and it’s gonna be free!

All March long, I’m giving away our newest class, Fundamentals of PowerShell for DBAs. Block out a half-hour per weekday on your calendar now because on each weekday in March, 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.)

Here’s what you’ll be learning:

Get started now by heading over to the class and watching the Before the Class modules that explain how to set up your workstation to follow along.

Not patient? Wanna get started on it right away? Recorded Class Season Pass Fundamentals holders can jump in now – it’s included with your existing membership. Go get your learn on!

[Video] Office Hours: Snowy Michigan Edition

Brent Ozar
Videos
3 Comments

We went up to Michigan to see my dad’s side of the family, and the snow came down just in time for our arrival. Always love the fresh snow look.

So I stood outside and took your top-voted questions from https://pollgab.com/room/brento. Let’s see what y’all came up with today:

Here’s what we covered:

  • 00:00 Start
  • 00:27 York!: Hi Brent! Recently you indicated in your Weekly Links that you aren’t a fan of schemas in a db. Can you elaborate as to why? Thanks!
  • 02:06 Brian: Linked servers; you bash them and yes they’re evil and slow. You’ve said “why not connect directly to the server that has the data”, I agree. But in cases where my friend has data on two servers (can’t consolidate them) and you need to query it together, how do you optimize that?
  • 03:52 Youssef L.: Hi Brent, I’ve been a paid SQL DBA since I was 17(5 years ago), I landed a Senior position and I’m one of 2 DBAs in the company working on a huge migration project from on-prem to Azure(600DBs). Management wants to use MI while I want always on approach.what do you think is best?
  • 04:50 M.: Hi Mr. Brent. Can you tell me why it is bad for performance to write WHERE datecolumn = getdate()? Thank You.
  • 05:36 Dru: Is the Pluralsight business model not long for this world since content creators can make so much more $$$ hosting their own training videos?
  • 05:51 Dance Monkey: Is it ok to simultaneously install Windows updates and SQL cumulative updates at the same time via windows update?
  • 06:49 Frank Drebin: What is the next version of SQL Server that will be deprecated in the first responder kit? When will this take place?
  • 07:46 Peter Riis: Hello Brent. Querying Spatial Data are sometimes really slow. The est. vs. act. number of rows using spatial functions can be way off. I joined your Level 2 Bundle and can’t find any hints on tuning these Queries. Do you have any ideas on tuning Queries on Spatial data?
  • 09:08 Patricia Zysk: Considering the limitations of both, what SQL feature do you recommend for tracking changes AND knowing which user made the change/date. In 2023 seems silly to have to write a trigger for part 2 of this with CDC. Thanks!
  • 10:07 thevibrantdba: In a prior webcast you mentioned Andy leonard for SSIS and my experience with the videos has been amazing. Now, who is the brent ozar for SSRS?
  • 10:49 Lenny: What is your opinion of distributed partitioned views in SQL Server?
  • 11:31 ConsultingMadness: While discussing reporting requirements, a client explained an internal process that sounded like a backdoor to avoid a tax requirement. It sounded a little sketchy. Have you ever encountered something like this, did you keep working with them, or have other advice to share?

[Video] Office Hours: 22 Good Questions and a Stinker

Brent Ozar
Videos
4 Comments

Lots of good questions on today’s broadcast! If you’d like to submit one, go to https://pollgab.com/room/brento and upvote the ones you’d like to see me cover.

Here’s what we covered today:

  • 00:00 Start
  • 01:05 Testing123: When inserting or updating data into a table, from a concurrency perspective, does it make sense to break up the complicated SELECT logic into a staging table and then bulk load/update the data into the destination. i.e. Will SQL only grab exclusive locks at the end of the tran?
  • 03:00 Curious DBA: Hi Brent. What query tuning approach would you take to force SQL Server to do multiple backward seeks instead of one expensive forward seek? I.E. Composite PK on (ID, Datetime). Search for max(datetime) on a single ID does a backwards seek, multiple IDs do 1 expensive fwd seek.
  • 05:11 Dru: What are the pros / cons of setting the SQL Server vm clock to UTC time instead of local time?
  • 06:08 George : Is it OK that when you give a caustic answer I call you “Brentward” in a slightly disapproving tone? (As if it’s your full name) P.S. I’ve gotten a new job with help from your classes. Definitely going to get the full set.
  • 07:55 DianaCarneiro: Hi Brent, I’m currently using an AlwaysOn AG configuration with WFCLs, and it has a few instances running on 2017. I was wondering if I can upgrade just one instance from 2017 to 2019 while keeping the rest on 2017. What do you think? The info about it is nothing concrete.
  • 09:43 Sid: What is the advantage of using sys.partitions over doing a count(*) over a table to get the count? We seem to be having some locking issues since sys.partitions is the same table as opposed to count(*) being separated. maybe a nolock hint?
  • 11:20 Mike: When you deploy Azure SQL Server VM (preinstalled SQL 2019), it only shows VM cost. For Ent. Edition – what will be the additional (hidden) license cost per CPU core ? Is it $274/month, same as in new 2022’s Pay-As-You-Go license billing model, or it is different amount ?
  • 12:08 Stone Tablet Monk: What is the best data warehouse design book for SQL Server?
  • 13:43 depthcharge: Hi Brent, took your Fundamentals of TempDB course and used it to diagnose and correct GAM page contention in TempDB, thanks! Is the guidance for correcting GAM waits the same for user databases? All my googling just turns up articles on TempDB.
  • 15:00 Elad: I have a table with identity column as clustered PK with high fragmentation. only inserts and updates without any deletes. updates is datetime2, decimal and int columns, no string datatype Table size ~500K rows with ~25K updates and ~10K inserts in 24hr. What can be the cuase? 15:54 Stone Tablet Monk: What are your favorite PostgreSQL books?
  • 18:09 GucciRules: Hi Brent, in Azure SQL Managed Instance, backups are automated; however, it doesn’t appear that the system databases are included in these automated backups. Any idea if they are in some form, or whether we need to run our own sysdb backups?
  • 19:10 Steve: We have a consultant who disabled CPU0 because he had a box with 128 cores and CPU0 was experiencing huge contention with VMWare activity. This server has 8 cores, so disabling a core is a big impact on performance. Should I ask to enable the core, or does he have a point?
  • 21:18 Porsh-uhh: I remember hearing vaguely about issues with SQL Server 2022 pre-release which negatively impacted monitoring tools in some way. Are there any issues like that with the release of 2022 and do they affect the First Responder Kit? Anything to really worry about deploying 2022?
  • 22:14 Dru: Is SSMS query plan viewing better with one large monitor or multiple smaller monitors? What is optimal monitor size for this?
  • 23:24 Phineas: What are your thoughts about manually clearing SQL wait stats? When should this be done?
  • 24:12 The Fall Guy: How do you decide when to store data as JSON blobs vs storing data in a well defined schema?
  • 25:15 Tugay Ersoy (Admiralkheir): Hi Ozar, We have enabled CDC in SQL Server 2016 to catch the changes. After a while, the log file got too big and we couldn’t shrink it.When CDC is open, it pulls SQL Log status to REPLICATION and does not allow us to process,so we had to close CDC How can we implement a solution
  • 26:25 Dance Monkey: Is it reasonable to update stats with full scan for a single table NC index as the first job step before running the next job step that does work on a very large DW Fact table?
  • 29:15 Kevin: Asking for a friend: is the order of records guaranteed when inserting them in a transaction? The app sends row1 and row2 with their own CreationDate (set in the app) but in SS, row2 has an earlier date. Is this expected behavior? If not, any starting points?
  • 30:37 ShiftHappens: HI Brent, in my job 99% of the time I do not need to do any kind of sql tuning, however there are times when there comes these really complicated procs whose perf is bad and I try my best to tune but I am not the best at it. Any advice on how I can get better at it?

[Video] Office Hours: Quickie Before Dinner

Brent Ozar
Videos
0

Before heading out to dinner, I went through your highly-upvoted questions from https://pollgab.com/room/brento.

Here’s what we covered in this episode:

  • 00:00 Start
  • 00:20 Mert: Hi Brent, what is the relation between AlwaysOn and Windows Failover Cluster? Is WFC an obligation or a choice for creating an Always On availability group? It will be nice if you address the topic with shapes visually. Thanks.
  • 01:12 LemonOnAPear: Whats your favorite SQL Server bug / story about a bug?
  • 02:26 franklwambo: Have you plans to ship the current or future SQL server training classes to pluralsight? my company seems more inclined to the plural sight subscriptions.
  • 03:04 Call Me Ishmael: Enforcing referential integrity via foreign keys is often a source of friction between application developers and the database team primarily because of the discipline that entails and perceived performance issues. There are blogs that offer alternatives. What is your opinion?
  • 04:14 don s: Have taken your Fundamentals of Columnstore course more than once, great course! My question is for Azure Synapse Analytics and the Columnstore implementation in this environment. Same old Columnstore or has Microsoft made any improvements to the deterioration issues?
  • 05:11 TiredAndFrustrated: We have a lot of ad hoc queries in the plan cache (over 33%). In your opinion, which is better, Optimize of Ad Hoc or Forced Parameterization. Devs won’t change the code….
  • 06:03 flynders: Why do perfmon counters show lower figures for IO latency compared with sys.dm_virtual_io_file_stats ? I see much higher latency figures from the DMV using Paul Randals IO time period script. I would expect the values to be close, not maybe 5x off using identical capture interval
  • 07:41 Philip: What do you typically recommend/like/see “comment” wise when it comes to documenting within your Stored Procedures, Functions, etc ?
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?