Blog

Which of These Azure Courses Would You Attend?

Last Updated October 2, 2023

A member of the community (no, it’s not me) is thinking about offering a live training course and wants your help to decide which of these topics are the best fit.

Here are 6 $395 live online 1-day classes, and what you’d learn in each class:

An Engineer’s Guide to Realtime Data Handling and Analytics

Streaming data to live dashboards for real-time insights.
Ingesting message data through IoT/Event hubs.
Writing KQL queries against streaming endpoints.
Combining stream and batch data in a lambda architecture.

Compute & Storage 101 – An Engineers Guide To Every Azure Data Resource

What, why, how. An A-Z of cloud data resources.
Clarity on what to use when and where from a huge breadth of tools.
Jointly mocking up a series of example data architecture patterns.
Filling your toolkit with the right compute and storage resources.

Creating A Data Lakehouse In The Microsoft Cloud With Delta Lake

Delivering a cloud first data warehouse solution using Delta Lake.
Building ETL processes that scale for big data problems.
Workload dependency handling to ensure referential integrity.
Serving modelling datasets to the business.

End-to-end Data Engineering In Azure With Apache Spark

Coding against Apache Spark in Python, Scala, and SQL.
Understanding distributed workload executions.
Cluster management, creation, sizing, and costing.
Using Apache Spark in Azure Databricks and Microsoft Fabric.

Microsoft Data Integration Pipelines – The Fundamentals to Level 300

How to build out Data Factory/Microsoft Fabric integration pipelines.
Orchestrating everything in your cloud solution.
How to create dynamic, scalable data integration patterns.
Best practices for production, monitoring and alerting.

Practical Data Modelling And Serving – Converting Business Events Into Insights

Applying Kimble dimensional modelling techniques.
Understanding how to deliver comprehensive analytics.
Writing DAX and M to produce calculated outputs.
Creating Power BI dashboards from transformed datasets.

Which course(s) would you pay $395 to attend next month? (If you’re reading this in email, click here to take the poll on BrentOzar.com.)

[Video] Office Hours at the Munch Museum in Oslo

Last Updated September 20, 2023

Brent Ozar

Videos

While in Norway for Data Saturday Oslo, I set up my camera gear by the Munch Museum to answer your top-voted questions from PollGab.com/room/brento. I later found out it’s pronounced ‘monk’, not ‘munch’, so there’s that. I also fell in love with his giant mural The Sun, probably one of my top 5 favorite paintings ever.

Here’s what we covered:

00:00 Start
00:51 Brandon: Do you have any recommendations for building a resume for new DBAs?
02:18 Through the Barricades: Can you give us 3 example scenarios where we should use Log Shipping and 3 scenarios where we should use Transactional Replication?
03:58 Ricardo: I came across an IT department the other day that was so wary of GDPR that development happened in Production. Do you think this was just an aberration or evolution in action?
04:40 Alberto: What is your opinion of SQL Trace Flag 174 (174 – Increases the SQL Server Database Engine plan cache bucket count). We have high single use query plan count. SQL Server 2019 Enterprise
06:13 Ms. Elizabeth: SQL Assessment API vs DBA Checks VS sp_Blitz. Which one wins in a cage match?
07:58 DB Stevie: As you no longer provide Production DBA training, who would you recommend for training in that space?
09:40 ReluctantDirector: A “friend” saw a covering index that had Values in the included columns that didn’t match the table values. Rebuilding the index didn’t fix it, only a drop and create. A lot of reports use the index columns to improve performance, any suggestions on how to mitigate this risk?
10:28 Oli-the-dba: Have you ever worked with Azure Netapp Files ? On Face value it looks infinitely better than managed disks. Would the fact it’s an SMB file share put you off ? Even if it’s “enterprise class storage”

[Video] Office Hours at Dyrhólaey Overlooking Puffins & Black Sand Beach

Last Updated September 20, 2023

Brent Ozar

Videos

Got Azure SQL DB and SQL Server questions? Post them at https://pollgab.com/room/brento and I’ll answer the top-voted ones. This episode comes to you from Dyrhólaey, where I was completely surprised to find puffins at this time of year.

Here’s what we covered today:

00:00 Start
01:01 Debaucherous DBA: When moving DB files from one drive to separate drives for different file types, do you recommend any particular order of operations? For example, should system DBs be moved before user DBs and should system DBs be moved in a specific order? Thank you in advance for your help!
02:44 Safraz: Hey Brent, Thanks for all that you do. Off-topic question:Have you ever sat in an Oracle tuning lecture or talk and what was your initial reaction to the similarities or differences between Oracle and SQL Server tuning?
05:24 AJ: A lot of people at my company run SQL using subqueries in the FROM clause that could easily just be in the WHERE clause. Would this cause performance issues if bots run these queries hundreds/thousands of times. (They’re also parameterized)
06:14 JerseyDBA: Recently I have run into an issue where I am getting memory grant issues. The queries pull 18+ million records and do not fail or need any more index tuning but are clearing the plan cache. Is there another technic to tackle the memory grant problem besides tunning?
08:30 PrayEveryMorningToSQL: Company A, bought Company B, company B is using SQL Server (+350 DBs / +17TB ). Company A created a technical team to “help everyone better mutualize resources..”. One of first recomendations, “migrate to Postgres”. My answer: “No”. How to handle this with management? Worth it?
09:37 Rimuru Tempest Slime Demonlord: I would like to get ur position on sql on prem vs cloud I don’t think a 4B a year revnue company shud create unnecessary reliability w/ 3rd party vendor. Also we hv a big data center foot print, they should create jobs instead Considering same setup what is ur position on this.
10:23 Clarence Oveur: When did you make the jump to Apple Mac and why?
13:19 AllegedDBA: Do you think Microsoft will ever offer object-level restore capability? Why not?
13:47 Bollywood DBA: Just a fun fact. I keep seeing Bollywood actors asking tou DBA questions. : ) e.g. Aamir Khan, Kareena Kapoor, Sonakshi Sinha Balraj Sahni. Not sure whether anyone else noticed this or not.
14:19 Steve: As sysadmin y can’t I see SSIS connectns& activities, traces, activity monitor, sp_who[2], …? I believe not having any tbl seeing any other activity. Running packages w/o error using VS2019 oledb connect & dtsdebughost or dtexec exes all on same desktop as the SQL server itself

It’s Probably Time to Upgrade Your Cloud VMs.

Last Updated September 24, 2023

Brent Ozar

Cloud Computing

If you’ve been in Azure or Amazon for a few years, you’re probably on old, slow hardware.

In the last 3 weeks, I’ve had two clients who’d both been early cloud adopters. When they’d migrated to the cloud, they both used Azure Ev3 VMs – at the time, a good choice for SQL Server due to its relatively high amount of memory. When the Ev3 VM types were announced in 2017, they were hosted on Intel Broadwell and Haswell processors with 2.3-2.4GHz processing speed.

Since 2021, though, Azure’s newer Ev5 VMs are hosted on Intel Ice Lake processors that run up to 3.5 GHz, and in most cases, they cost the same. In my clients’ cases, both of them were starved for CPU cycles. By simply shutting down the SQL Server, changing its instance type through the portal, and then starting it back up, they were able to eliminate the CPU bottleneck without spending one more dollar.

Amazon, same story, especially in light of last week’s X2iedn announcement. If you’ve had the same VMs for several years, go to this handy EC2 instance comparison tool, note your instance type’s clock speed, and compare it to newer instance types.

In most cases, you can go faster for free.

You might ask, “Well, why isn’t the cloud provider fixing this for me? Why don’t they automatically move me to newer, faster instance types where it’s available?”

Think of it like renting a car. If you went to a car rental company back in 2017, and you rented a 2017 Toyota Camry, you were probably happy. Today, though, your Camry’s old, tired, and pales in comparison to what the car rental company has today. It’s up to you to take a pit stop at the car rental company and exchange keys.

Otherwise, you’re happily paying full price to rent a 2017 Camry.

And why on earth would they tell you about that? You’ve paid for that server many, many, many times over, and as long as you’re willing to keep doing that, hey, they’ll keep cashing your check.

New Amazon X2iedn High-Memory Instances

Last Updated September 20, 2023

Brent Ozar

Amazon Web Services (AWS)

Amazon just announced new X2iedn instance types for Amazon RDS SQL Server. They’re a sweet deal for people who want a high-performance managed database, with really fast cores and more memory per core than you can get in Azure’s managed SQL offerings.

Say your workload needs 8 CPU cores. Here’s a quick comparison between a few instance types, all priced with SQL Server Standard Edition licensing included:

So if you want >64GB RAM, but you want to stay with a low core count, you can.

However, if you want to use as much of that memory as possible, there’s going to be a price to pay. SQL Server Standard Edition is limited to around 128GB of memory for the buffer pool, although it can go over that amount for stuff – that’s beyond the scope of this blog post. If you’re looking to upgrade to Enterprise to leverage every last byte:

Or, another way to think of your 8-core options:

64GB: $35/GB
128GB: $30/GB
256GB: $19/GB

The more you buy, the more you save. Go figure. And of course, along those lines, you can save big on those by using Amazon’s Reserved Instances, as I discuss in my Running SQL Server in AWS & Azure class.

Does this mean Amazon RDS SQL Server is better than Azure SQL Managed Instances? Not at all: it’s just one of a bunch of attributes to consider. For example, even today, Amazon still doesn’t offer SQL Server 2022 in RDS – although I do have to point out that it’s been almost a year, and SQL Server 2022 still isn’t ready.

[Video] Office Hours at Svínafellsjökull Glacier in Iceland

Last Updated September 8, 2023

Brent Ozar

Videos

Got Azure SQL DB and SQL Server questions? Post them at https://pollgab.com/room/brento and I’ll answer the top-voted ones. This episode comes to you from the most hike-friendly glacier in Iceland, one where you can park your car and step onto the glacier 15-20 minutes later after an easy walk. (I’m not standing on the glacier in this – I’m in front of it.)

Here’s what we covered today:

00:00 Start
01:32 Kenny Blankenship: What is your opinion of the Microsoft Azure AG deployment experience that walks you through creating the cluster, VM’s, and availability group for SQL 2022?
02:43 Mrs. Watanabe: What is your opinion of using Windows S2D as storage host for Azure SQL VM data and TLog files? Do you run into perf issues with this?
03:21 sandimschuh : Where is the cut-off point in SQL Server where a nested loop with key look up becomes more expensive than a scan? Or to put it another way, what criterion does SQL Server use to decide that a scan is cheaper?
05:21 CuriousDBA: Hello Brent, Given server A and server B What is the best solution in your opinion to archive data from server A to server B and purge data on server A?
05:55 DerekSQLDBA: Hi Brent – We have some dev servers in availability groups with a lot of databases (200-250+) so we need a lot of worker threads and are paying for 16 cores for each server, but overall usage is under 10%. Is there any way to deal with the worker thread bottleneck for cheaper?
07:22 Mrs. Watanabe: What is your opinion of using network storage such as Azure NetApp and Silk to bypass azure disk throughput limits for Azure SQL VM?
07:58 Clippy: What is your opinion of the Azure SQL VM Best Practices Analyzer (part of SQL Agent extension)? Is this comparable in quality to sp_blitz?

12 Office Hours Questions I Don’t Have Answers For

Last Updated September 18, 2023

Brent Ozar

SQL Server

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:

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.

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.

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:

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:

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:

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.

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.