Blog

12 Office Hours Questions I Don’t Have Answers For

Last Updated September 18, 2023

Hey, you! You look smart. Here are highly-upvoted questions that fellow readers submitted at https://pollgab.com/room/brento that I don’t have answers for. If you’d like to jump into the comments, you can reference questions by number with your answers. Time to show off your brains!

Kansas4444 asks: Hi Brent, do you have any advice on calculating checksum for an entire row on SQL Server ? (Brent says: I’ve never had to do this, so no idea.)
Margaret asks: Hi Brent — we are in the process of doing a lot of VLF reductions across the farm and I wanted to ask if issuing a CHECKPOINT command before trying to shrink the log was anything that might cause problems going forward?
Stanley Burrell: What is your opinion of Microsoft PerfInsights for load testing prospective new SQL Server VMs? (Brent says: never even heard of it.)
BamDBA asks: When using Transparent Data Encryption for databases in Availability Groups in a busy environment, are there any tips/best practices to increase performance?
I’m_Trying asks: Are you aware of any way to implement multifactor authentication for versions previous to SQL 2022 for on prem databases?
LCK_Tess_Monster: RE: MAXDOP=0 & Mastering Indexing – before moving to Azure, I saw intensive queries cause blocking to its internal threads in ActMon without causing problems for others. Under what circumstances could blocking internal to a SPID slow down IX creation or was it a false positive?
Ricardo: To refresh our SSRS server I want to move from backup /restore to Replication but am in 2 minds whether to replicate into the current database or a new one? A new one would be cleaner but we seem to have many reports with environmental requirements like hard-coded database names. (Brent says: this is the kind of thing I’d do a consulting engagement to really understand the requirements, available resources, budget, etc, but maybe some of y’all have free answers for them.)
DGW in OKC: What is your opinion of Azure AD? Does it work well with on-prem SQL Server security?
hamburger sandwich asks: How do you automate windows server OS updates for SQL Server high availability group clusters? (Brent says: this question comes up a lot, and I haven’t ever seen a good answer that didn’t involve possibly rolling back open transactions or backups, but maybe y’all have seen something I haven’t.)
Junior DBA wannabe asks: How would you script the creation of a database and add it to AG replication from a single node? Same for app login creation
HotPotatoJuggler asks: DBA says SQL Server DB wait states are negligable. Webserver has resource, and is barely moving the dial. Connection from webserver to DB <8ms. Running query <2ms. 300ms for the .net adapter to fill for each call (x20). AWS just old instance – do they slow them down? 🙂 Any clues? (Brent says: I’m not even sure what the hell this is asking.)
Frozt asks: Hi Brent, Do you have any gotcha’s we got to be aware regarding Always Encrypted with Enclaves? Also is this better than application level encryption?

Curious to see what y’all say!

Announcing SQLBits 2024: 19-23 March, Near London

Last Updated September 14, 2023

Brent Ozar

SQLBits

I hate picking favorites, but I gotta be honest: SQLBits is my favorite conference.

Sure, like any conference, it has workshops, training days, and even a free community day, but the icing on the crumpet is the attendee party. Every year, Bits goes all out to pick a theme and throw a monster Friday night party where people dress up, take part in games, and just blow off steam.

When is SQLBits 2024? The conference will be 19-23 March, 2024.

What is the SQLBits theme this year? The 2024 theme is Aviation. It ties into the Farnborough location, which is home of the Farnborough Airshow. Cue up your Top Gun, Airplane!, and Snakes on a Plane ideas for costumes…

Where is SQLBits 2024? The conference will be held at Farnborough Exhibition & Conference Center within an hour southwest of London. You can fly into London, and then hop a train to Farnborough where there are plenty of affordable hotels.

How much is SQLBits 2024? There will be a limited number of early bird tickets available at a discount. Bits will announce the pricing soon, and from the moment pricing is announced, we’ll have 5-6 weeks before ticket sales go online. That gives you a few weeks to convince your managers to spring for a ticket, and then when sales goes live, MOVE FAST to secure one of the limited discounted seats. (It isn’t like other conferences that sell unlimited tickets based on date ranges.)

Is SQLBits 2024 in-person? The event will be hybrid – you can attend either in-person or online.

When is the SQLBits call for speakers? It opens 25 Sept, and concludes 1st November. Check out the SQLBits Speakers page for more details.

The registration, call for speakers, and agenda aren’t available just yet, but now’s a good time to block out your calendar just in case. You never know when your boss will actually say yes! I’ve blocked it out on my calendar already, and I’m excited to get back to see everybody again.

[Video] Office Hours at Breiðamerkursandur, Diamond Beach

Last Updated September 4, 2023

Brent Ozar

Videos

Post your Azure SQL DB and SQL Server questions at https://pollgab.com/room/brento and upvote the ones you’d like to see me discuss. In this episode, I’m overlooking Breiðamerkursandur, Diamond Beach, one of the most Instagrammed places in Iceland. Here’s a shot from a prior trip when I climbed up on one of the icebergs:

This time around, I’m staying dry so I can answer your tech questions:

Office Hours at Breiðamerkursandur, Diamond Beach

Watch this video on YouTube

Here’s what we covered:

00:00 Start
01:40 Ozan: Hi Brent, can you recommend a book or resources to learn more about the internals of SQL Server in depth (SQL OS, block allocations, write and read logic on memory and disk). Does aligned block allocation (8*8K=64K) on storage still matters on SSD? Thanks
03:57 JustWondering: Have vendor app that selects all fields of a 2TB table.Plan says it’s doing a CI seek.Table has a non seq GUID as the cluster key, includes an ntext field (XML data) + 5 other GUID fields.Qry runs 10000x each cycle. Where do I start to improve performance since I can’t tune it?
05:51 Balaji Ram: Is it possible to implement SQL FCI using Windows Storage Replication in AWS? I tried but couldn’t add gp3 disk to Cluster. Cannot find any useful documentation. Can use AWS-FSX but storage is very expensive – $4.5 for 1 MB/Sec per month. Any blog/book/documentation pls? Thanks!
06:45 Faysal Qureshi: Do agree with Chat GPT’s ranking of 10 worst database management systems? 10. Microsoft Access 9. FileMaker 8. Oracle 7. MongoDB 6. MySQL 5. SQLite 4. dBASE 3. Paradox 2. FoxPro 1. Lotus Approach
07:23 Henry Jones Jr.: Which is more popular : Office Hours Twitch Stream or Office Hours Youtube? Which has more fortune and glory?
07:54 Begrudgingly-Learning-MySQL :-(: I hate even asking this here, but your one of the best authorities in the database space in my opinion. Do you have any recommended courses/classes that can help someone, begrudgingly transitioning from SQL Server to MySQL, write and tune queries?
08:39 SQL_Stormlight: What approach do recommend to troubleshoot this error when you have 1000s of dbs and no idea which query is causing the issue: “The query processor ran out of internal resources and could not produce a query plan.” This alert has been raised 100s of times over the last week.
09:35 GermanDBA: Hi Brent, What are some things to consider when weighing Transactional Replication vs ETL tools for keeping a copy of various table columns up to date on a secondary server?
10:32 Mr. SqlSeeks: I have been tasked to determine how our back-end processes perform with 2x and 3x data volume. Do you have any suggestions as to the most efficient way to essentially copy/paste from one instance to another for the purpose of doubling the data volume?
12:20 YY: Hi Brent, For a typical OLTP application, What is your recommendation on: How many filegroups? How may files per filegoup? What table types in each filegoup?
14:41 Red U: What are the top issues your clients run into when lifting and shifting SQL server to Azure SQL VM?
15:30 PrayEveryMorningToSQLGod: With a database that as a table of files (aka images) that is ~80% of it’s size (around 1.6TB). Do you recomend moving it to FILESTREAM ( tech looks dead ) or just leave it alone and pray to SQL Gods? Trying to improve: Backup Performance and Performance on getting the imagens
16:23 LostInSpace: How can I determine when to use Optimize for Ad hoc workloads?

“Oops, I Lost My Indexes in Azure SQL DB.”

Last Updated September 12, 2023

Brent Ozar

Azure SQL DB, Backup and Recovery

I got an interesting request for consulting, and I’m going to paraphrase it:

We were using Azure SQL DB with automatic index tuning enabled for months. Things were going great, but… we just deployed a new version of our code. Our deployment tool made the database schema match our source control, which… dropped the indexes Azure had created. How do we get them back?

First, restore a pre-deployment backup somewhere else. The indexes will still be present in that other database. Don’t overwrite the production database – I need to mention that in case SQL Server users are reading this to solve a similar problem.

Then, decide whether you want all of the old indexes, or only some.

If you want all indexes back, use a tool like sp_BlitzIndex @Mode = 2 to script out all of the index definitions, or Redgate Schema Compare or SQL Server Data Tools to compare the prod & backup databases. This lets you quickly put all of the indexes back, in the shortest amount of time, with the least amount of thought. However, it’ll also include indexes that may no longer be useful to you with your new version of app code.

If you only want some of the indexes back, keep that backup around – but wait to apply indexes until one of two things happens:

A query shows up on your top-10 most resource intensive queries from tools like sp_BlitzCache or Query Store, or
A user complains about a query

In either case, go into Query Store and try to find the prior versions of that query plan. Look to see which indexes it used to use, and then pull those index definitions from your pre-deployment backup. (Query Store won’t include the index definitions.)

If you can’t find prior plans in Query Store, you’ll need to review the tables used in the query, then review the indexes on those tables in the pre-deployment backup. Pluck the ones out that you think will be most useful, and then apply those in production.

After the emergency’s over, it’s time to think about how you’re going to prevent this problem the next time you do a deployment.

Change your deployment tooling to only make constructive changes, not destructive. If an index already exists, leave it in place. Frankly, I think this is the smart long-term thing to do regardless of how your deployments are done, or where your database is hosted. This same problem has existed for ISVs hosting on-premises applications for decades because customers will apply their own indexes just like Azure does.

Or, source control Azure’s changes as they happen. You can log Azure’s telemetry somewhere (or poll it on demand) and then build a process to apply those changes to a dev database, and open pull requests with Azure’s changes. That’s an easy sentence for me to write, but a challenging process to build. (I’ve certainly never bothered.)

What’s Faster: IN or OR? Columnstore Edition

Last Updated September 11, 2023

Brent Ozar

Columnstore Indexes, Execution Plans

Pinal Dave recently ignited a storm of controversy when he quizzed readers about which one of these would be faster on AdventureWorks2019:

SELECT *
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (1, 2, 3);

SELECT *
FROM Sales.SalesOrderHeader
WHERE CustomerID = 1
   OR CustomerID = 2
   OR CustomerID = 3;

SELECT *

FROM Sales.SalesOrderHeader

WHERE CustomerID IN (1, 2, 3);

SELECT *

FROM Sales.SalesOrderHeader

WHERE CustomerID = 1

OR CustomerID = 2

OR CustomerID = 3;

I laughed so hard when I saw the storm of responses on Twitter. People sure do get passionate about this kind of thing. If you ever wanna witness patience and generosity in action, look at Pinal’s responses to this tweet.

While the answer for the default AdventureWorks2019 database is that they perform identically, I wanted to extend the quiz a little.

Does columnstore change the answer?

Folks who’ve been through my Fundamentals of Columnstore class will remember that queries are performed differently when a columnstore index is involved – sometimes better, sometimes worse. (Usually better, though.)

We’ll start by dropping the existing indexes FKs, and triggers, then creating a clustered columnstore index on Sales.SalesOrderHeader, then running the two queries:

USE [AdventureWorks2019]
GO
DROP INDEX [IX_SalesOrderHeader_SalesPersonID] ON [Sales].[SalesOrderHeader]
DROP INDEX [IX_SalesOrderHeader_CustomerID] ON [Sales].[SalesOrderHeader]
DROP INDEX [AK_SalesOrderHeader_SalesOrderNumber] ON [Sales].[SalesOrderHeader]
DROP INDEX [AK_SalesOrderHeader_rowguid] ON [Sales].[SalesOrderHeader]
GO
ALTER TABLE [Sales].[SalesOrderHeaderSalesReason] 
	DROP CONSTRAINT [FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID]
ALTER TABLE [Sales].[SalesOrderDetail] 
	DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]
GO
ALTER TABLE [Sales].[SalesOrderHeader] 
	DROP CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] WITH ( ONLINE = OFF )
DROP TRIGGER [Sales].[uSalesOrderHeader]
GO

CREATE CLUSTERED COLUMNSTORE INDEX CCI
  ON Sales.SalesOrderHeader;
GO
SET STATISTICS IO ON;
GO

SELECT *
FROM Sales.SalesOrderHeader
WHERE CustomerID IN (1, 2, 3);

SELECT *
FROM Sales.SalesOrderHeader
WHERE CustomerID = 1
   OR CustomerID = 2
   OR CustomerID = 3;

USE [AdventureWorks2019]

DROP INDEX [IX_SalesOrderHeader_SalesPersonID] ON [Sales].[SalesOrderHeader]

DROP INDEX [IX_SalesOrderHeader_CustomerID] ON [Sales].[SalesOrderHeader]

DROP INDEX [AK_SalesOrderHeader_SalesOrderNumber] ON [Sales].[SalesOrderHeader]

DROP INDEX [AK_SalesOrderHeader_rowguid] ON [Sales].[SalesOrderHeader]

ALTER TABLE [Sales].[SalesOrderHeaderSalesReason]

DROP CONSTRAINT [FK_SalesOrderHeaderSalesReason_SalesOrderHeader_SalesOrderID]

ALTER TABLE [Sales].[SalesOrderDetail]

DROP CONSTRAINT [FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID]

ALTER TABLE [Sales].[SalesOrderHeader]

DROP CONSTRAINT [PK_SalesOrderHeader_SalesOrderID] WITH ( ONLINE = OFF )

DROP TRIGGER [Sales].[uSalesOrderHeader]

CREATE CLUSTERED COLUMNSTORE INDEX CCI

ON Sales.SalesOrderHeader;

SET STATISTICS IO ON;

SELECT *

FROM Sales.SalesOrderHeader

WHERE CustomerID IN (1, 2, 3);

SELECT *

FROM Sales.SalesOrderHeader

WHERE CustomerID = 1

OR CustomerID = 2

OR CustomerID = 3;

And then compare the logical reads done by both queries:

(0 rows affected)
Table 'SalesOrderHeader'. Scan count 1, logical reads 0, lob logical reads 731
Table 'SalesOrderHeader'. Segment reads 1, segment skipped 0.

(1 row affected)

(0 rows affected)
Table 'SalesOrderHeader'. Scan count 1, logical reads 0, lob logical reads 731
Table 'SalesOrderHeader'. Segment reads 1, segment skipped 0.

(1 row affected)

(0 rows affected)

Table 'SalesOrderHeader'. Scan count 1, logical reads 0, lob logical reads 731

Table 'SalesOrderHeader'. Segment reads 1, segment skipped 0.

(1 row affected)

(0 rows affected)

Table 'SalesOrderHeader'. Scan count 1, logical reads 0, lob logical reads 731

Table 'SalesOrderHeader'. Segment reads 1, segment skipped 0.

(1 row affected)

Both queries do 731 logical reads. (For clarity, I’ve removed a lot of the irrelevant IO stats.)

That means the queries are performing identically in terms of reads, and their execution plans both look the same too:

Both plans perform equally.

They both suck.

They’re both retrieving all of the rows in SalesOrderHeader – even though no rows actually match our query! If you test this against the default rowstore indexes in AdventureWorks2019, you’re going to come away with the conclusion that columnstore is worse than rowstore – and frankly, for many kinds of queries, that’s true.

Does SQL Server 2022 compat level fix that?

AdventureWorks2019 ships in compat level 150 (2019). Let’s switch over to 160 (2022), and try that again. Here are the new logical reads:

(0 rows affected)
Table 'SalesOrderHeader'. Scan count 1, logical reads 0, lob logical reads 0
Table 'SalesOrderHeader'. Segment reads 0, segment skipped 1.

(1 row affected)

(0 rows affected)
Table 'SalesOrderHeader'. Scan count 1, logical reads 0, lob logical reads 0
Table 'SalesOrderHeader'. Segment reads 0, segment skipped 1.

(1 row affected)

(0 rows affected)

Table 'SalesOrderHeader'. Scan count 1, logical reads 0, lob logical reads 0

Table 'SalesOrderHeader'. Segment reads 0, segment skipped 1.

(1 row affected)

(0 rows affected)

Table 'SalesOrderHeader'. Scan count 1, logical reads 0, lob logical reads 0

Table 'SalesOrderHeader'. Segment reads 0, segment skipped 1.

(1 row affected)

BOTH QUERIES DO ZERO READS. ZERO.

That’s because SQL Server 2022 (in 2022 compat) is able to skip the segments altogether. Note the line “segment skipped 1”, as opposed to the 2019 version which actually read the data.

How? Well, the 2022 execution plans look different than the 2019 versions:

These new simpler plans don’t have a filter because the data’s already being filtered earlier in the plan, at the index scan level. If you hover your mouse over the Columnstore Index Scan operators, you’ll see a new Predicate option that we didn’t have before:

This is called “predicate pushdown”, and it’s something that Microsoft has been gradually adding in to more and more columnstore plans over the years. They’ve been pretty good about tracking what’s new in columnstore with each version, but I gotta confess that I actually thought integer predicate pushdowns were already happening, hahaha. It might be a regression in 2022, but frankly, I don’t have multiple machines handy with different versions to test it. (I’m finishing up my Norway vacation, getting ready to fly back to the US, but Pinal’s blog post tickled me so much that I had to bang this post out quickly.)

So in summary, when you’re using columnstore indexes, what’s faster, IN or OR? The answer: 160 compatibility level.

[Video] Office Hours at Jökulsárlón Glacial Lagoon

Last Updated September 4, 2023

Brent Ozar

Videos

Post your Azure SQL DB and SQL Server questions at https://pollgab.com/room/brento and upvote the ones you’d like to see me discuss. In this episode, I’m at Jökulsárlón Glacial Lagoon, a wonder of nature, watching the icebergs pile up, stuck in the lagoon due to the incoming high tides.

Office Hours: Jökulsárlón Glacial Lagoon

Watch this video on YouTube

Here’s what we covered:

00:00 Start
01:15 Oli-the-dba: I hear “clustering is dumb and complex” on azure IaaS all the time from non dbas. One can only assume they hate HA/DR. However, Have you had any clients go clusterless ?
03:14 Ozan: Hi Brent, do you prefer to access external data via linked server or if possible via polybase? What is your experience with polybase? Thanks
04:01 Jag B: When avoiding blocking, is READPAST hint better than NOLOCK?
04:50 Xavier P: What are the top issues your clients run into when deploying Power BI Gateway?
05:37 Jerry Mathers: What is your opinion of DBeaver (
https://dbeaver.io/) for working with SQL Server VM and Azure SQL?
06:22 Wally: Should Azure drives that host SQL VM data files be formatted with 4k, 8k, or 64k block size?
07:29 Shehroz Sabzwari: During the building / installing of a new SQL Server instance, when should you run sp_blitz?
08:21 Oli-the-dba: Hi Brent, do you see a use case for Azure PaaS if you have a team of 4 experienced dba’s. I’m at a 500 server shop about to embark on our azure journey, performance preferred over cost savings.
09:25 Analyse T: Do RPO/RTO numbers usually improve when migrating from Log Shipping to Always On Availability Groups?
10:25 Mahtab Keramati: What test apps do you like to run on prospective new SQL VM Hosts for CPU, Network, and Disk performance?
11:06 Izzy G: Enjoy how you relate technical problems to every day experiences. Did you have to learn this or does it come naturally?

[Video] Office Hours at the Blue Lagoon

Last Updated September 4, 2023

Brent Ozar

Videos

Road trip time! I’m recovering from jet lag after flying over to Iceland, so I went through your top-voted questions from https://pollgab.com/room/brento.

Office Hours at the Blue Lagoon in Iceland

Watch this video on YouTube

Here’s what we covered:

00:00 Start
00:55 Shehroz Sabzwari: What are your pros / cons for running on-prem SQL Server in a VM versus bare metal?
02:04 adba: what are your recommendations for windows performance plan for sql server running on a VM. My private cloud vendor says it is not required to be set to high performance.
03:25 Holy: Can’t you agree that all RDBMS databases are approaching their limits, just as ISAM databases did in the 1980s and 1990s?
05:47 sandimschuh: Hi Brent, is it always safe to change the database setting from Read Committed to RCSI? What kind of queries or query patterns will be negatively affected by this change?
07:04 Mike Conrad: Hi Brent, My team uses system versioned tables a lot. Our ORM, EFcore, sometimes will write updates that don’t change any data, but this does generate a new HISTORY table row, bloating our audit trail. Are instead of triggers a viable way to prevent this?

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

Last Updated November 6, 2023

Brent Ozar

Who's Hiring

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

Why Australian Azure SQL DBs Went Down for 8+ Hours

Last Updated September 4, 2023

Brent Ozar

Azure SQL DB

On August 30, Azure’s Australia East data center had a big problem, affecting customers like Bank of Queensland and Jetstar. Here’s the timeline:

30 August 2023 @ 08:41 – Voltage sag occurred on utility power line
30 August 2023 @ 08:43 – Five chillers failed to restart
30 August 2023 @ 10:30 – Storage and SQL alerted by monitors about failure rates
30 August 2023 @ 10:57 – Cosmos DB Initial impact detected via monitoring
30 August 2023 @ 11:15 – Attempts to stabilize the five chillers were unsuccessful after multiple chiller restarts
30 August 2023 @ 11:34 – Decision was made to shutdown infrastructure in the two affected data halls
30 August 2023 @ 20:29 – All but two SQL nodes recovered
31 August 2023 @ 04:04 – Restoration of Cosmos DB accounts to Australia East initiated
31 August 2023 @ 04:43 – Final Cosmos DB cluster recovered, restoring all traffic for accounts that were not failed over
31 August 2023 @ 08:45 – All external customer accounts back online and operating from Australia

Note that 11:34, the decision was made to shut down infrastructure without Microsoft failing your databases over elsewhere. If you were an Azure SQL DB or Cosmos DB user, and you weren’t paying for replicas in another data center, it was up to you to follow Microsoft’s disaster recovery guidance.

Controversial opinion: I actually love that and I think it’s great.

I see a lot of Azure SQL DB users make the mistake of assuming that Azure includes disaster recovery, but it does not. It’s on you, and as a result, you save money. (Same thing in AWS Aurora PostgreSQL.) I’m sure there are plenty of small business databases that don’t need disaster recovery within a day or two. Heck, even Bank of Queensland probably has some databases that fit into that category, although… probably not as many as actually went down, hahaha.

There’s a problem with that, though: Microsoft didn’t notify affected customers about which of their databases were down, or that the customers should start their DR processes. Microsoft couldn’t notify customers because … they didn’t know who those customers were. Microsoft’s Azure status history doesn’t let you easily link to a single event, but if you expand the outage on 30 Aug, the preliminary writeup is really detailed, and explains why they were flying blind:

From a SQL perspective… Some databases may have been completely unavailable, some would have experienced intermittent connectivity issues, and some databases would have been fully available. This uneven impact profile for databases in the degraded ring, meant that it was difficult to summarize which customers were still impacted, which continued to present a challenge throughout the incident.

Boy, I have been there. When multiple databases and servers go down, one of the first thing management wants to know is, “Which specific apps are down?” When you can’t answer that question, it makes management pretty nervous, and adds even more stress to the situation.

As we attempted to migrate databases out of the degraded ring, SQL did not have well tested tools on hand that were built to move databases when the source ring was in degraded health scenario. Soon this became our largest impediment to mitigating impact.

It might be tempting to point and say, “Well, Microsoft, you should have that” – and they should – but I don’t see a lot of shops with well-tested automated DR failover tools.

I’ve long said that Azure SQL DB does a better job of database administration than not having a DBA altogether, and this is a good example. Customers who didn’t have a DBA wouldn’t have been any better off managing their own DR in a situation like this, and frankly, most customers who do have a DBA wouldn’t have been better off either. (If you smugly think you’d be fine, point to your current list of production servers & databases, and prove that every single database you have is also synced with DR. Go ahead. I’ll wait.)

Because every DB moved required manual mitigation via scripts, it seriously undermined our ability to move fast even once impacted DBs were identified, and DB moves were scheduled.

Elsewhere in the post, they mention that over 250,000 databases were involved in just one of these troubled rings of databases alone. You just can’t manually do anything with 250,000 databases, so I can only imagine how stressful it was to try to write the automation code under fire. Props to the folks working that night.

Overall, this kind of incident – and how Microsoft responded to it afterwards – is why I think that if you don’t have a DBA, you could do a lot worse than relying on Microsoft, Amazon, and Google doing that job for you instead. Platform-as-a-Service lets someone else stress out about the outage, troubleshoot it as quickly as they can, then build better processes to shorten the next outage.

[Video] Office Hours: Overly Caffeinated Edition

Last Updated August 17, 2023

Brent Ozar

Videos

After consuming waaaay too much coffee, I went through your top-voted questions from https://pollgab.com/room/brento:

Office Hours: Overly Caffeinated Edition

Watch this video on YouTube

Here’s what we covered:

00:00 Start
03:53 It’s ‘a me: Is there a feature in SQL that Microsoft abandoned that you wish they had perfected?
05:43 TheEveryDayDBA: My friend asks, he is using sp_blitzIndex on a OLAP db workload. Quite a lot of Indexaphobia: High Value Missing Index are trigger for a table, that contains a Clustered ColumnStore index on it. Does sp_blitzIndex look for ColomStore indexes when it runs?
07:01 RenegadeLarsen: Hi Brent Any plans on going to SQLBits next year? Kind Regards RenegadeLarsen
08:06 BrentIsMyHero: Hi Brent! You are my hero! 🙂 Can you please recommend your top 3 books for a Production DBA role. Thanks and have a great day!
09:28 ascme: I’ve been a mssql DBA for 15+ years and have dabbled in other platforms. My company is moving most DB services to IBM LUW and MariaDB. I am half interested in learning more about them, but not sure that is a career building positive for me. What would you do?
10:30 Ozan: Hi Brent, you recommend performing snapshot backups if the database size is greater than 1 TB. Why not also for databases with smaller sizes?
11:18 Miles: Hi Brent,How do you handle egoistic managers or toxic peers who take credit for your work, don’t appreciate it, and hinder your promotion despite hard work? Have you faced such situations? Can you share your experiences and advice on dealing with them?
13:16 unspoiled: I had an issue with excessive blockings, I tracked this down to be related to compile locks of which I have never heard of before. how do compile locks cause a wide spread blocking issues and how could it be prevented?
15:41 Blue K: Is Azure SQL any better/worse for implementing one DB per customer than traditional SQL VM?
16:59 Kaysar R: You mentioned using snapshot backup over native SQL backup for large terabyte DB’s. Do you recommend first upgrading to SQL 2022 before attempting snapshot backups?
17:41 Chips Ahoy!: In a recent office hours you recommended that XML and JSON should be stored in the DB as a blob. You are also known to say “don’t put data in the DB unless there will be joins or filters on it” Seems contradictory. Did I misunderstand something?
18:31 Red U: Have you seen anyone successfully automate the scale up / scale down process for Azure SQL VM? What were the lessons learned?
21:04 Izzy G: What are the top issues your clients run into when querying SQL Server over a WAN instead of a LAN?
21:32 It’s ‘a me: Hi Brent. You talk about backups a lot, but I’ve never heard you mention backing up to URL? Is there a reason you don’t mention it as an option? Drawbacks, reliability etc
23:15 Faroek: Is the query store purely a logging feature, or does SQL Server also use it to re-use plans or check for plans to help with query executions?
24:06 Grubsnik: In a recent office hours, you mentioned that SQL server has become much better a prioritizing ram over disk for tempdb in the last 5-10 years. Can you tell which version specifically? We’ve been running TempDB on ramdrives and I’m wondering if that is an antipattern for SQL2016
24:56 Doug E: What are your thoughts on Microsoft purchasing Activision? Does this permanently hurt the competition?
25:47 sandimschuh: What is a good way to identify queries that pose the risk of a sudden change in execution plan (switch from NL to a scan or visa verse)? The problem occurs with queries that use at least one index with heterogeneously distributed data (bad estimates) and nested subqueries.
27:38 Lance Boil: What is your favorite cruise ship line and destination?

[Video] Office Hours: All About Miles Edition

Last Updated August 10, 2023

Brent Ozar

Videos

Today’s webcast featured an awful lot of questions from one particular person who was super-active at https://pollgab.com/room/brento:

Office Hours: All About Miles

Watch this video on YouTube

Here’s what we covered:

00:00 Start
00:03 Miles: Hi Brent, I am aware of “sp_statement” completed and sp_batch_completed events in trace and extended events. But, what is the use of rpc_completed event? How this event will be helpful while collecting traces and when should we capture this event? please explain.
01:37 Mike: Azure SQL Managed Instance has uptime SLA of 99.99%. If we are okay with maximum of 4.5 minutes of downtime per month (0.01%) – that means we do not need to add any secondary replicas for HA at all, and single instance is sufficient?
05:16 Miles: Hi Brent, we have a dev team always say, its the SQL problem. Eg:DB locks is the root cause of app API slowness, why SQL Server got restarted,we can’t afford it. How to handle such teams? How we can do a much better job?
07:16 Pradeep M.: When log shipping gets behind by 24+ hours do you try to determine why it got behind or focus on improving network bandwidth so that it doesn’t get behind in the future?
08:16 Miles: Hi Brent, what is process involved in base-lining a query performance. I really want to know how to do it and why it is important to do it. Please explain with an example.
08:51 JimLic: I have seen performance issues when using more than 3 CTEs stacked on top of each other. Is there some documentation that says “don’t use more than 3 CTEs in a single query”. Does “It depends” apply here? What have you seen with CTEs?
10:45 Miles: Hi Brent, Any piece of advice for new DBA’s with 5+ years of experience to increase their credibility and visibility?
13:25 Rapid Dragon: What’s your opinion of SQL stretch DB? Do and of your clients use it / like it?
13:57 Miles: Hi Brent, How to find a right mentor? How to approach them? Difference between teacher & mentor? If one agrees to be a mentor what things mentee should be prepared for?If mentor charges more, find another? Have you faced situations where mentor affordability was an issue? Thanks!
18:15 Miles: Hi Brent, based on your work experience, what are the top 5 essential qualities a good DBA should have?
20:16 who knows: I have a lot of old c# apps that are forcing SQL transactions to go ReadCommitted, is this preventing my SQL server from using row versioning? I have RCSI enabled in my databases.
21:26 Hjordis: What is your opinion of SQL Trace Flag 834 – “Use large-page allocations for the buffer pool, columnstore, and in-memory tables”?
23:03 zlobnyfar: Hello Brent! If the money is not a problem – what is the best HA and DR solutions you can recommend. A few small conditions: SQL Server 2017 or 2019 ENT Edition, NO downtime and NO Data loss. Thank you for comprehensive answer!
24:11 Julie Berthelsen: What are the pros / cons of implementing first responder kit in TSQL vs PowerShell? Microsoft SQL support tools tend to prefer PowerShell for some reason.
25:30 Ciaran O: Microsoft recently mentioned potential shortage of GPUs as a risk factor to it’s business. Should Microsoft do like Apple and start making it’s own chips?
28:08 Drax: How do you know if your Azure SQL VM is too overprovisioned (i.e. paying for unused resources)?
29:04 Tony Feuz: Would you recommend turning on TDE? We are facing some changes for SOC2 and we are wondering if we should go this far. Our backups are currently encrypted and I did read your blog post – but the post does not seem to specify if you are for or against it…. Thanks!

Free Webcast: Avoiding Deadlocks by Tuning Queries

Last Updated August 26, 2023

Brent Ozar

Conferences and Classes

You’ve been getting more and more deadlock errors, and users are starting to complain. You’re wondering if your queries are the root cause, and if so, what you should do to fix ’em.

In a fast-paced session, I’ll explain the 3 causes for deadlocks, and the 3 ways to get relief. We’ll use 2 demo queries to show unrealistically simple deadlocks, plus more real-world deadlocks that are harder to solve.

Register here to attend free on September 21. If you can’t make it live, the recording link will be emailed to the registrants as well. See you there!

[Video] Office Hours: I Need Your Help with These

Last Updated August 9, 2023

Brent Ozar

Videos

Today, I need your help with some of the top-voted questions from https://pollgab.com/room/brento. Chime in in the comments:

Office Hours: I Need Your Help With These

Watch this video on YouTube

00:00 Start
00:52 Accidental DBA: Hello Brent. Thank you for keeping your Q& A sessions entertains and informative. My question is non-technical…Have you ever said this to your client? You keep using that word. I don’t think it means what you think it means. : )
02:04 Peter: Hi Brent, I would like to cobble some in-house SQL training from your fundamental and mastering courses I have attended. Are you okay with this? And if so, what credits do I need to attribute to you?
02:45 JimLic: My employer is moving from SQL Server to AWS (postgres/aurora/glue/lambda). When would it be an appropriate time for a seasoned MS SQL Server DBA to jump ship? [Old dog, new trick]
07:55 Anatole Taubman: What are your thoughts on office vs remote vs hybrid work requirements for SQL DBA’s? Has Covid forever changed this requirement?
10:10 Fundamentals Newb: Hey Brent, I get a “Many Duplicate Plans” warning when I run Sp_Blitzcache (Around 55K plans in cache). We have 1 dev, his resources are spent. How do I determine if forcing parameterization is the way to go versus forcing plans via Query Store? How would you test this?
11:19 Miles: Hi Brent, How to tell my SQL Server needs more memory? what factors or metrics do we need to measure and what are those thresholds we should be looking at?
11:48 Miles: Hi Brent, How can we determine when a query can’t be further tuned? When do you decide that the query needs more hardware resources? Where do we stop tuning a query?
13:27 BJ: Hi Brent, what do you thing about Entity Framework CRUD (single insert/update/delete) operations as solution for high performance OLTP database with large number of transactions?
15:09 Confused Dev: Hi Brent, my friend says, a query is faster if its only using tables from 1 DB rather than 2 DBs on the same server. Is there any truth to that?
16:03 Miles: Hi Brent, we have usage of MSDTC transactions inside our SQL Server for a vendor specific db.These txns sometimes cause heavy blocking. My question is, why do people use distributed transactions and what are the use cases of MSDTC based on your experience? Can we avoid them?
17:38 Don’t Bother Asking (DBA): Hello Brent. Today, my requested me do something sessions that were stuck in rollback. As far as I know, there is nothing that can be done, we must all wait for the rollback/killed session to complete. Is there any new functionality in SQL 2022 for long-running rewind operations?
19:00 Miles: Hi Brent,When do implicit data type conversions become costly, even for bookmark lookups/RID lookups? Our vendor database lacks control over design and data types. How to convince the app team about scaling issues with growing data?
20:23 DadJokerDetroit: If a large number of DBAs go to a restaurant (at a conference for example), should they be allowed to join tables?
20:55 Ramesh: Is there a good way to identify which SQL clients are using older outdated SQL driver against SQL 2019?
21:38 MightBeAStupidQuestion: Hey Brent! I know you typically say to store files in a file system. With the popularity of data lakes would you feel comfortable storing files here too?
24:23 Don’t Bother Asking (DBA): Is it preferable to have a DBA team for all sorts of databases, such as SQL Server, Oracle, MongoDB, Cassandra, and so on, or is it better to have distinct DBA teams for each? My friend posed this question since he intends to combine all DB support teams into a single DBA team.
24:24 Don’t Bother Asking (DBA): Is it preferable to have a DBA team for all sorts of databases, such as SQL Server, Oracle, MongoDB, Cassandra, and so on, or is it better to have distinct DBA teams for each? My friend posed this question since he intends to combine all DB support teams into a single DBA team.
25:47 Manto: Do you prefer to use Azure ephemeral disk for local tempdb or disk read cache in Azure SQL VM?
26:20 Manto: For performance reasons, does sector size matter for Azure SQL VM ephemeral drive used for TempDB? If so, what is your recommendation?
27:32 Don’t Bother Asking (DBA): Sorry for my typo on previous question, Have you had any experience with Liquibase as a tool for CI/CD for databases? or have any other alternative solution for this purpose on SQL Server?
27:56 Philip: Hi Brent – VM Snapshots and Database Backups (Full/Differential/Transaction Log) – how do you see the best setup for a stand-alone SQL VM where the Infrastructure team want to do VM Snapshots as well as Database Backups?

[Video] Office Hours: Really Long (and Really Short) Answers

Last Updated August 9, 2023

Brent Ozar

Videos

Today’s batch of questions from https://pollgab.com/room/brento requires some really long answers, and some are right to the point.

Office Hours: Really Long Answers and Really Short Ones

Watch this video on YouTube

00:00 Start
02:13 Eh? Aye…: As DBAs, how can we best prepare for the AI world in terms of data? Should we start with AI as a concept fundamentals, or what ua the ‘data type’ we should just jump right in and start to learn to work with? E.g. if we were into mapping, we would look to learn geospatial.Ta much
03:37 Tim Gitchel: What is the best method for configuring Linux or K8S to connect to FCI with MultiSubnetFailover=True? I get mixed results when adding this to the connection string and am not sure of all the moving parts and options.
05:04 Jorge: What are your pros / cons of deleting old backup files before vs after OLA SQL backup for multi TB db backups?
06:40 Bekim Fehmiu: Do you recommend page or row compression for new tables in SQL Server on prem?
07:31 Miles: Hi Brent, App db grew suddenly from 3TB to 5TB, filling 90% disk. How to size data&log drives for such sudden growths? What proactive measures can we take to monitor&plan for sufficient space? what questions to ask the app team to avoid unnecessary downtime’s? Thank you.
09:22 Hjordis: Is it ok to delete from a CTE (instead of a view) when doing fast ordered deletes?
10:46 Don’t Bother Asking (DBA): Hi, Do you advocate using storage replication as a data resiliency/availability strategy for SQL Server data/log files? Does replication at the storage level work with SQL Server?
12:34 Miles: Hi Brent,pls explan how RCSI works when readers &writers are involved and when writers & writers are involved? Unable to visualize how all this works. RCSI is enabled on app db.We don’t see any opentran in use tempdb go dbcc opentran go but tempdb size keeps growing and growing.
14:13 RacerX: What’s the fastest you have ever driven and which car was it in?
16:32 Urs Fischer: Have you ever seen any successful large code migrations from SQL Server to PostgreSQL for money saving purposes? What were the lessons learned?
17:44 planCacheObliteration: Batched queries (eg below) flush our plan cache. How should our devs fix this? One query per batch? Batched a different way? (@p1 int,@p2 int,@p3 int… …@p1000 ); delete tbl where id = @p1; select @@rowcount; delete tbl where id = @p2; select @@rowcount; update…
19:26 Confusing DBA: Hi Brent, we ran into performance issues recently. We used steps from How I Use the First Responder Kit to teach our developers to solve those issues, but they only want direct answers / spoon-feed solutions and like to ask random questions. What do you suggest to deal with this?
20:18 Miles: Hi Brent, We have a vendor specific db and we see lot of query hints being used in their code,lots of (NOLOCK) (ROWLOCK) & (UPDLOCK). hints.Is this fine or do we need to ask the developers to remove those hints? In recent times with multiple integrations,this db is becoming slow.
21:38 Mike: In Azure SQL Managed Instance, should I still perform DBCC CheckDB checks?
23:24 Mike: In Azure, SQL Managed Instance (32 vCore, 224 GB RAM, $8K) of comparable size to SQL VM (32 vCore, 256 GB RAM, $11.5K), is cheaper. Isn’t Managed Instance supposed to be more expensive, given that backups, patching and HA are taken care of by Azure?
25:09 Ornella Muti: What are the top use cases for SQL SYNONYM you see in the wild?
26:56 Tim Taylor: What’s it like living in a new house during major renovations? Would you do it again?
27:42 Q-Ent: Hi Brent, Do you have any suggestion on how we can update our DWH in Near Real Time from Staging environment? I use transactional replication right now but the amount of changes is too high and causes performance issues on distribution database.

Updated First Responder Kit and Consultant Toolkit for August 2023

Last Updated August 20, 2023

Brent Ozar

First Responder Kit Updates

Turns out y’all actually work over the summer – there are actually cool new features this month! I think I’m actually going to have to record updated sp_BlitzIndex, sp_BlitzLock, and sp_BlitzQueryStore modules for my “How I Use the First Responder Kit” class because these features are pretty awesome.

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

To get the new version:

Download the updated FirstResponderKit.zip
Azure Data Studio users with the First Responder Kit extension:
ctrl/command+shift+p, First Responder Kit: Import.
PowerShell users: run Install-DbaFirstResponderKit from dbatools
Get The Consultant Toolkit to quickly export the First Responder Kit results into an easy-to-share spreadsheet

Consultant Toolkit Changes

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

sp_Blitz Changes

Improvement: better chance of running to completion when you’re not SA. sp_Blitz has always required sysadmin permissions in the past, and this is our first pass at it, so if you have limited permissions and you run into errors, feel free to file ’em as issues and we’ll see if we can work around it. (#3292, thanks Erik Darling.)
Fix: reduce possibility of missing replication in use. (#3316, thanks DavidSchanzer.)

sp_BlitzCache Changes

Fix: more accurate estimation of memory grant used overall across multiple executions of a query. (#3313, thanks mdpenguin.)
Fix: more accurate percentage of duplicate query plans. We’ve been battling this issue for yeeeears, and I still can’t reproduce the issue in the lab, so I don’t think this is the last chapter on this issue. (#3314, thanks WaldenL.)
Fix: skip warnings of ResourceDB queries. (#3315, thanks WaldenL.)
Fix: better compatibility with AG secondary replicas. (#3291, thanks mbambion.)

sp_BlitzIndex Changes

Enhancements in #3294: in the columnstore visualization section, each rowgroup’s state, trim reason, etc are now shown:

And in that result set, the size of the secondary dictionary (if required) for a column is now included in its size. Here’s the Users.DisplayName column before – note the sizes in the far right:

Now, with the secondary dictionary size included, the DisplayName column’s true size shows up as being dramatically larger because the strings are relatively unique and don’t compress well:

sp_BlitzLock Changes

Improvement: if the queries involved in deadlocks are still in the plan cache, sp_BlitzLock now shows an additional result set with their plans. (#3293 and #3311, thanks Erik Darling.)
Improvement: when targeting one session, make sure it exists first. (#3306, thanks Erik Darling.)
Improvement: faster XML queries. (#3305, thanks Erik Darling.)
Fix: time searches are now UTC compatible. (#3307, thanks Erik Darling and Mike Hodgson.)

sp_BlitzQueryStore Changes

Improvement: Parameter Sensitive Plan Optimization (PSPO) compatibility! If you search for a single @StoredProcName, we now show all of the plan variants for it. (#3312, thanks sqljared.)

sp_DatabaseRestore Changes

Improvement: new @KeepCdc parameter restores that attribute too. (#3289, thanks SQLSlinger.)
Fix: compatibility with SQL Server 2022’s new undocumented columns in RESTORE HEADERONLY. (#3190, thanks Razvan Socol.)
Fix: incorrect error message in debugging output. (#3320, thanks Greg Dodds.)

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 in Telluride, Colorado

Last Updated August 14, 2023

Brent Ozar

Videos

We headed up to the mountains to get away from the Vegas heat. Before the sun rose, I took your top-voted questions from https://pollgab.com/room/brento.

Office Hours in Telluride, Colorado

Watch this video on YouTube

Here’s what we covered:

00:00 Start
00:36 Ozan: Hi Brent, how should Volume Snapshot Backups (VSS) be configured correctly that it will not freeze the database’s IO for a couple of seconds or minutes? Best regards from Berlin
02:00 Miles: Hi Brent, As a DBA (5+ years), how do you track your work? Any tool or Excel? for ex: manager asks send me the list of items worked over last 10 months. things like that. Any piece of advice for visibility and credibility, dealing with a biased manager?
03:01 Yullka Mujo: What are the top issues you see with using FileStream in SQL Server?
03:24 Logar The Barbarian: What kind of problems have you dealt with and resolved for clients utilizing Dynamics GP for their ERP systems?
04:43 Pavle Vuisic: Is it ok to update to latest SQL CU when you are four updates behind or should you install one CU then the next, etc?
05:13 Rotnei: Do you have any suggestions for dealing with Azure Alert fatigue? Email rules / folders seems potentially heavy handed.
05:55 Mahlagha Jaberi: What is your opinion of performing Azure SQL VM backups using crash-consistent snapshots?
06:51 Miles: Hi Brent,Dev team is try to DELETE a log table based on co-related sub-query. It read 4TB data and It caused heavy blocking for more than an hour. Eventually had to kill the session. Even Rollback it took almost 30 mins. Db is part of AG. Any advice to make the DELETE’s faster?
07:41 Hjordis: When should you put new tables in a file group other than PRIMARY?
09:01 Ozan: Hi Brent, regarding MAX DOP I get in a 16 CPU VM environment with MAX DOP = 0 (16) better, faster results than with 8 which is the max value recommended by Microsoft. What problems could I face when I stay with 16 or 0? Thanks
09:31 BobbyTables: You mention that while you haven’t done them recently, you’ve heard of potential issues when doing an In Place Upgrade for MS SQL OS. What issues have you seen reported?
10:53 Shahab Hosseini: What is the top batch request per second (sustained) you have seen at your clients? How many DBAs did they have?
11:38 Ingvar Vittfarne: Do you have a recommended naming convention for SQL Agent Job Schedule names?
12:13 Nomzamo M: How do you know if the SQL engine has inlined your user function or not?

Office Hours: Before Heading Out on Vacation

Last Updated August 14, 2023

Brent Ozar

Videos

I took a break from packing my bags long enough to answer your top-voted questions from https://pollgab.com/room/brento.

Office Hours: Before Heading Out on Vacation

Watch this video on YouTube

Here’s what we covered:

00:00 Start
02:42 T-man: Hi BO. A friend has multiple Distributed AGs (on separate business division servers) but needs to share ODS data with each of the DAG DBs. Any near-real time architecture ideas so they can stop batch ETL? Adding replication on top of AGs can work, but yuk. Thanks, you da best.
04:11 BigData: Adding a server login during peak times is causing deadlocks with random user sprocs. It appears related to SCH-M on security cache. Server is 1.5tb and security cache is 13+ gb. Can’t recreate issue on small dev server. Are there any gotchas when clearing security cache?
04:59 reluctantly_tolerant : We are using AWS FSxN (managed NetApp ONTAP) provisioned for 512MBs throughput with SQL 2019. The DB files are so fragmented that 2k iSCSI IOPS causes 50k-60k disk IOPS and AWS is throttling to 20k IOPS and ~130MBs throughput. How can I fix this and prevent recurrence?
06:59 Eli: Hi Brent, observed a hash match operator performing 1000x slower when Table B is first in a join, but no spill! Join is many-to-many, with one duplicate value in Table A showing up 40M times, 200M times in Table B. From an internals perspective, what makes B-first so much slower?
07:43 Tim Gitchel: Is there a way to capture any connection to the DAC? I am seeing an error from a nightly Tenable scan that there is another connection preventing access, but I can never seem to catch it. Could something else be causing this same error?
09:01 pete: Why would a bit-wise operation in a table join cause a query to run long?
10:37 Sonakshi Sinha: What did DBA Brent do for physical fitness?
13:47 Golshifteh Farahani: I addition to Ozar training, what are the top things SQL DBA’s should be doing to future proof their careers?
15:32 OliTheDBA: At what point do you think the cons outweigh the pros of automation ? For example, fancy multi-thousand line powershell scripts to snap restore db’s are great and save time, but the up keep and maintenance isn’t enjoyable and can often take hours a week. (Rant)
16:50 Bilal Lashari: What are your top memories from your visits to the Microsoft Redmond campus?
19:28 MooneyFlyer: Hey Brent, kind of a silly question, so roasted if needed. Why STATISTICS IO ON doesn’t give any information about written pages? Is there a way to get this information? I’m trying to give hard evidence to indexing naysayers on the real impact of adding an index to a table.
21:08 Bjorn: Is it ok to use PAGLOCK hint in conjunction with TSQL – DELETE TOP (@SomeVar) FROM dbo.Foo to prevent lock escalation?
21:57 Grimlock : What is the optimal nap time and duration? Have you always followed this?
23:13 Alex M: Do you have any experience/thoughts on using non-MS SQL replication technology with SQL Server, such as Zerto for DR?
25:08 Vangelis Protopappas: What are the top SQL full text issues you see with your clients?
26:00 Demet Evgar: Does the DBA role for non-relational DB’s differ much from that of relational DB’s?
27:57 Pareesh Patel: When doing one DB per customer for multi-tenant, what is the max number of DB’s you would not exceed when using SQL A.G.?
29:18 Stu: At what point do you think a software developer should look for a new job, what are the tell tale signs?
30:53 west: What are your thoughts about Microsoft Fabric and its dwh ability?

The PASS Summit Lineup Is Out and Registration is Open!

Last Updated August 14, 2023

Brent Ozar

#SQLPass

This year’s PASS Data Community Summit is November 13-17 in Seattle. (No online version is available this year – you gotta be there in person.)

If you’ve attended in person before, you already know the value: it’s like a family reunion for the Microsoft data community. It’s your chance to see the authors you’ve read so many times, hear their latest lessons, and network with people who can help you the next time your company has hard times. Go register.

If you’ve never attended before, here’s my sales pitch:

The pre-conference workshops on Mon/Tues are $595 each, and that’s a steal for a full day of in-depth training on topics that you just won’t find locally.
The full 3-day pass is $1,995, and the sessions are organized into learning pathways so you can quickly figure out if there are topics that are a good fit for you
Pick your pre-cons and your learning pathway, write them down, and then show your manager, “Here’s what I’m going to learn for $3,185.”

Send that to your manager and see what comes back. Then, the negotiation starts. Here are techniques I’ve heard folks use in order to get their companies to pay for some, if not all, of the conference, hotel, travel, and meal expenses:

“I understand that we don’t have a travel budget, so how about the company buys the conference ticket out of the training budget, and I’ll pay for my own travel?”
“I understand we only have a limited training & travel budget for the whole team. How about I go, and when I return, every Friday for the next 2 months, I’ll teach a lunch & learn for the rest of the staff, covering one of the sessions I attended?”
“I’m going to attend whether the company pays for it or not, and I’m going to take vacation to attend. If it’s important to you that I’m reachable during that time, you can pay for the conference ticket and travel, and I won’t take vacation, and I’ll be reachable via email while I attend the sessions.”
“I understand we don’t have any budget, so how about I pay for it, but you don’t make me take vacation time while I attend, and I won’t be reachable?”

I know you might be reading that and going, “There’s no way my company would go for that.”

But they do – it’s just a matter of finding the right combination that works for you and them. Go make it happen, because you need to start making connections and gaining knowledge that will help your career.

(Unfortunately, I won’t be there – Summit is the same week as the Las Vegas F1 night race. I’m super excited to see F1 cars racing down the Strip!)

[Video] Office Hours: Stump Me On SQL Server

Last Updated July 20, 2023

Brent Ozar

Videos

I went through the top-voted questions from https://pollgab.com/room/brento and discussed ’em live on my Twitch channel.

Office Hours: Ask Me Anything About SQL Server

Watch this video on YouTube

Here’s what we covered:

00:00 Start
00:39 tanchenglai: Hi Brent! We bought your Level 2 Bundle last year. Recently, we went into frequent thread pool issues; we suspect it is due to high CPU utilization. Our IT manager advised us to delete old data (keep only 3 – 6 months). Is deleting data the best solution for performance issues?
01:32 Kulstad: I have been given the “glorious” task of upgrading a production SQL server from 2005 Standard Edition to 2019 Standard Edition. Can you recommend and tips, tricks, and “gotchas” for such an upgrade?
03:24 chandwich: What are the most indexes you have ever seen on a single table? Was it too many? What kind of hardware requirements would be necessary to ensure that tables indexes are all usable?
05:00 MS Excel: Hi Brent, recently we got request to migrate on-premise database to AWS RDS instance. Database is about 300GB in size. What would be your approach to do it with minimal downtime (except CDC data replication)?
06:09 Maksim Bondarenko: Hi Brent. Is it possible to get CDC database/database table changes on some other server instance?
06:46 Xavier R: Hi Brent, what is in your opinion a good average query execution time for rapports or application performance in small/mid/large/very large database?
08:00 Xavier R: Hi brent, i have 4 years of experience with MSSQL. How easy or hard is it to transition to a job that uses DB2?
09:28 Clippy: What are your thoughts on copilot integration with office 365 for 30 dollars per user?
13:09 Alex M: Considering that you visit some nice, high-end restaurants, do you also have an interest in cooking at home? If so, Any suggestions for favorite recipe sites?
15:41 SelectStar: A qry that joins 2 tables, intermittently (once a wk more/less) fails w/ error converting varchar to int. It gets fixed after dropping/re-adding any index from one of the 2 tables. When it fails, it has a diff exec plan. It’s a vendor qry, that uses deprec join. What would u do?
16:26 Balraj Sahni: Is high batch requests per second ever a concern on it’s own or does it have to be simultaneously high along with another wait stat to be of concern?
17:59 Luca: What is the most efficient way to capture SQL Execution timeouts to help troubleshooting?
20:19 Miles: Hi Brent, We see tempdb size growing up to 5TB. Version store size is 4TB..RCSI is turned on few dbs. What does version store that big means to you and how can we reduce the size of version store? How to capture those offending queries take up space in tempdb and tune them?
21:10 Ben: I am struggling to find best way to query Nested JSON – when there are 4 cross apply the estimated rows are 312milion, and the actual is only 5 rows. If I place SQL Function to manipulate the data SQL give 3G ram for the query .Would you recommend using CLR instead of SQL?
21:43 Jessica: Hey Brent, the last time you discussed hosting for pollgab was March 2022. Have you considered an update to that post in 2023 to show how hosting costs have changed month to month vs usage since then?
22:27 Chat GPL: Hi Brent, a friend of mine struggles with tuning many small queries, about 200 quite simple stored procedures, called sequentially for a total of 1M times. Trivial plans, no blocking, no missing indexes, same execution time. How would you start when there is no low hanging fruit?
23:24 Augustus: What spoken language translation tools do you like to use for work and vacation?
24:23 Sultan: Any recommendations for how to keep logins and SQL agent jobs in sync across AG replicas?
25:31 Dimitris Horn: Do you ever see issues related to SQL Connection Pooling with your clients?
26:43 Mike in Dublin, IRL: Hi Brent. If you query a non-clustered index on a heap table and data from a column not contained in the index is required, does the query have to scan the heap table for the row ID or can it perform a seek to the row ID?
27:22 Doesnt Buy Anything: Hi Brent a friend is looking for advice. Would you advice an ‘in place’ upgrade of the Windows Server OS from 2012 to 2019 for a SQL Server 2017?
28:50 Benji: Will we ever see a mastering tempdb class?
29:09 SQLChess: Hi Brent. A friend of mine started a SQL blog but is struggling to find new interesting topics to post. What is your methodology for new blog posts? Thanks!
31:10 Jessica: Hey Brent, What sort of tests can I use outside of complicated load replay systems to test how much overhead a new indexed view would cause? Is the put it in production and see if anyone screams viable?

[Video] Office Hours: Extended Answers Edition

Last Updated July 31, 2023

Brent Ozar

Videos

Y’all posted so many good questions at https://pollgab.com/room/brento that I went longer than usual this time:

Office Hours: Extended Edition

Watch this video on YouTube

Here’s what we covered in this episode:

00:00 Start
02:17 Tanchenglai: Hi Brent, recently we always ran into thread pool issue. I checked Performance Dashboard and found that CPU spiked over 80% occasionally. I suspect apps can’t connect after CPU over 80%.
03:55 Janis : I’m facing a puzzling issue with a simple join query, warns excessive 1/2 GB memory grant when joining the final table. I wonder where to dig deeper for a solution.
05:02 Novice Consultant: Hey Brent! How do you handle those clients who said they don’t know the how-to when you suggest some non-SQL Server solution
08:03 Eli: Hi Brent, hope you’re well. In your training, you make use of CTEs, but not correlated subqueries. Reusability aside, are there other reasons to prefer CTEs over correlated subqueries? Also, does subquery optimization behave differently if nested deeply?
08:53 Sonakshi Sinha: Is it beneficial to periodically delete the auto generated stats and let SQL re-generate them in hopes of culling the stats that are no longer used?
09:44 FroztDbz: Should an sql server service account password have expiry or can it be added on exemption list. Also how should I justify to them that this should be added to exemption list since it will create unnecessary downtime when not tracked properly
10:28 Dimez: In DBD do you play killer more or survivor more and who do you main as killer/survivor?
11:45 i_write_tsql_in_lower_case: Hi Brent, with this writing SQL Server 2022 has 6 monthly CUs. What’s wrong with SQL Server 2022?
12:23 Chris: Have you ever used any tools that can assist in comparing execution plans that is better than the built in one for SSMS?
13:29 DBA with Anxiety: Hi Brent!. Thanks For Response My Last Question, Appreciate it very much. How to overcome FOMO in the world of databases
15:19 Dansa: We have a newly created table with a daily-partition aligned clustered column-store index. The partition function has 4800 partitions. Attempting to drop the empty table did not succeed after 5 hours.
16:36 Aamir Khan: What is your opinion of Microsoft SQL Server slow performance troubleshooting documentation?
19:01 Vangelis Protopappas: What are your pros/cons of MSTeams vs Slack?
21:06 Vishnu: What tools (external to source control) do you like to use when comparing two TSQL scripts for differences?
21:29 Dimitris Horn: We have many power users that can setup new / modify existing SQL agent jobs whenever they feel like it. Do you have any tips for how to track all the SQL Agent Job changes?
24:50 Sonakshi Sinha: What are your thoughts about using schemas to segregate multi tenant tables within a single database (MyDB.Co1.Sales, MyDB.Co2.Sales, etc)? Is this hard on plan cache / query store?
25:35 Eli: Hi Brent – heard you got a new gaming PC! If you ever get tired of Dead by Daylight, have you ever considered Deep Rock Galactic?
26:50 Double Bad Ass: What is your favorite cloud data warehouse and why?
27:37 gserdijn: Hello Brent, I noticed something I’ve never seen before. Some databases had OLDEST_PAGE as Log Reuse Wait Description. What does that mean?
28:33 Eduardo: Prior to SQL 2022, which SQL version was the previous poorly adoption version?
29:56 Kerem Bürsin: Does SQL page/row compression reduce the need for db normalization?
30:32 Bernard Tai: What was your favorite DBA class / subject as a student?
33:11 pete: An SP with a 3 table join takes 30 seconds with the results into a
#temp, the second part has a 3 table join using the result set and 2 of the original tables, but it takes 2+ minutes. The only thing that is way out of kilter is the estimates of rows to be operated on. help pls
34:52 Miles: Hi Brent, What metrics to measure and what threshold values to consider to determine how busy is my SQL Server? I want to check the load on the SQL Server. Thank you.
36:48 PretendsToKnowSQL: We have a few maintenance plans we would like to “copy” to another instance. Are there any easy ways to do this or is it better to just take the time to rebuild them on the other instance?
37:30 Jessica: I am currently crying in standard edition for indexed views. Have you ever seen issues wrapping an indexed view in a second view to add the noexpand hint on the first view? (create view View2 as Select * from ix_View1 with (NOEXPAND))
38:08 Ozan: Hi Brent, at least two times i heard you saying that you get up at 3 AM. But so far i didnt hear you telling us when you go to bed!? I’m curious because 3 AM is damn early. Best regards from Berlin
39:02 Miles: Hi Brent, We have 4TB vendor specific DB. We see a lot of open spids idle for over 8hrs. By killing long running head blockers, what kind of impact will it have on other AG replicas?
40:02 Eduardo: Do you know what the tipping point is in SSMS where it will crash from showing too many query plans from a stored proc that has cursors?

Blog

Does columnstore change the answer?

Does SQL Server 2022 compat level fix that?

Consultant Toolkit Changes

sp_Blitz Changes

sp_BlitzCache Changes

sp_BlitzIndex Changes

sp_BlitzLock Changes

sp_BlitzQueryStore Changes

sp_DatabaseRestore Changes

For Support

Hi! I’m Brent Ozar.

Get Free SQL Stuff