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

Who's Hiring

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

If your company is hiring, leave a comment. The rules:

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

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

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

[Video] Office Hours: Overheating Cadillac Edition


I took your questions from before heading out to pick up my Cadillac from the mechanic.

Here’s what we covered:

  • 00:00 Start
  • 04:50 JudgeDredd: Hi Brent, have you ever been asked for opinion/expertise (as a database specialist) in a court trial? If yes, could you share some details?
  • 07:10 Aart Bluestoke: A maximum of 25% of ram for a query seems large; queries either seem to be tiny, or need more than 25% (but grab 25% and spill). Is it reasonable to set it to 10% or some smaller values, to avoid blocking by ram allocation if a couple reporting queries land at the same time.
  • 08:35 Satish: How does OLTP SQL DBA differ in required skills / duties from OLAP SQL DBA?
  • 10:21 StanTheMan: Hi,New DWH Server,Dell R750,SQL S2022,RAM 1.8TB,TEMPDB 4TB on local NVMe disks,stored procedure goes every 5minutes, error log every third day shows Incorrect checksum on TEMDB.mdf/ndf, CHECKDB IS OK, CHDSK IS OK, should be writte back on controller disabled?No one cant tell us
  • 12:25 Chris: Hey Brent, I have a large CCI which rarely requires updates to a tiny portion of it (~100 rows out of 100m). Even with NCI’s to support the seeks the key lookups for the update just plain suck and take tens of minutes. Are there quick wins or just need to bring the pain?
  • 13:51 DBA With Anxiety: Hi Brent. Do You have worksheet to decide, when we use sqlserver, or when we use another open source solutions ?
  • 15:12 Sigmund: How important (if at all) is emotional intelligence to the DBA role?
  • 16:10 Divya: What are your thoughts on surrogate vs natural key usage in SQL Server?
  • 16:54 Inspector Gadget: What are your thoughts on disabling / renaming SA account for security purposes?
  • 17:56 Dilip Kumar: How hard would it be to specify desired columns / column order in sp_blitzindex similar to how sp_whoisactive does it?
  • 19:00 Q-Ent: Hi Brent, Have you ever used Distributed Replay tool to assess performance workload on a new server?
  • 19:45 Ricardo: What is Data worth? Would you measure it by the amount of money it cost to get, look after, and store? (-say- 2000% once analysed and used), Or could you say its total-worth is 100% of the companies value (like company shares). As a full log of a company’s activity.
  • 21:06 Yitzchak: What are your thoughts on ChatGPT including training from commercial books without permission / compensation? Is this fair use?
  • 22:05 Cthulhu: Should you ever use sp_executesql directly in C# code? If so, when?
  • 22:40 Pablo: Should we configure alerts for SQL Error 833 Disk Delay? SQL Server 2019
  • 23:12 Rogge (like Stogie): Hello Brent: please help us understand why collation is different: SELECT COALESCE([i].[name], [i].[type_desc]) FROM sys.indexes [i]; Forcing either column to either collation “fixes the ‘glitch”‘. Why? (I’ve searched your site and other less reputable ones)
  • 24:29 Kris: Hi, Is there an easy way to dynamically merge XML data (data type XML) into fixed tables.
  • 25:34 pete: simple – I want to pipe the message tab, not the results tab to a file. Reality so far is that my research has this as almost, if not, impossible. point me in the right direction . .
  • 26:21 END TRY BEGIN CRY: Hi Brent. Suddenly one of my servers is taking 30min to backup a 4GB database. Wait types are ASYNC_IO, BACKUPIO, BACKUPBUFFER. Backups go directly to blob storage in Azure. All other servers are fine but this one is really slow. Also nothing else is currently using the server.
  • 27:51 Imran Abbas: What is your opinion of SQL Activity Monitor?
  • 28:00 Alexis Georgoulis: For Azure SQL VM, what are the pros / cons of managing disk encryption at the VM level vs through SQL TDE? Is it wise to run both?
  • 29:22 Oldie: Hi Brent, our developers use SSMS to create database backup files in case then need to restore but developers cannot delete the backup files from disk without getting access to the disk through an file share or if an administrator creates an job to delete the files, any advice ?.

[Video] Office Hours: Azure SQL DB & SQL Server Questions


Post your SQL Server and Azure SQL DB questions at and upvote the ones you’d like to hear me discuss.

Here’s what we discussed in this episode:

  • 00:00 Start
  • 02:47 therealprodDBA: In the last webcast, I heard you cite that majority of your clients are on AWS. I got curious now that my shop is considering migrating onprem SQL to azure. Is there any known issue that the said clients were avoiding on azure SQL DB, managed instance and hyperscale?
  • 04:08 Festus: What naming convention do you prefer to use for unique constraint indexes vs non-unique indexes?
  • 05:21 EnglishmanDBAInIsrael: Following Garðarshólmur question on backups, is snapshot backup suitable for a highly active environment? I thought of having a AG secondary dedicated to snapshot backups, and then IO freeze does not affect users, but still gives the super fast disaster recovery.
  • 06:03 Seeker of Parallelism: Hi Brent, I added indexed views to tune my query. Unfortunately only my dev system uses the indexed views, the prod system gets the old query plan (already tested with option recompile). What could be the reason for different execution plans on both systems?
  • 07:21 END TRY BEGIN CRY: Hi Brent. I need to shrink a log after it grew to about 3 times the size of the DB @ 150GB (bad dev coding). The DB is in Simple recovery mode and had a backup done, and the log file says its 99% free space, but shrinkfile doesn’t make it any smaller. Can you think why? Cheers
  • 09:24 Doug E: Should we be concerned when “Reason for early termination of statement optimization” equals “Timeout” for a complex query in a stored proc?
  • 10:16 boutaga: Hi Brent ! Do all application using queries with parameter have parameter sniffing in SQL Server ? In my experience it is the case, but if not, how can I code an application in that manner ?
  • 12:42 Peter: How do you go about learning new technologies? You help a lot of us in your articles and teaching, but who teaches the teacher – and what methods work best for you?
  • 17:20 fajitapete: So I got a customer who handles the database server. The server is serving my application. 5 filegroups (3 for data, 2 for indexing) with 17-23 files per filegroup. Files are all different sizes, am I wrong in trying to show there is a performance issue with this many files?
  • 19:23 Todd C: Hi Brent: Do you ever do escape rooms with friends or family? They are popping up all over the place now. Even cruise ships have them (Royal Caribbean at least). I bet you would be pretty good, given your problem solving skills and analytical mind.
  • 20:51 Yuvati: What is your opinion of SQL Trace Flag 4199 (Query Optimizer fixes released in SQL Server Cumulative Updates and Service Packs) and when is it appropriate to use?
  • 23:15 Dave: Should indirect checkpoints be used with the master database?
  • 24:14 Eduardo: Is Azure ultra disk storage performance specs closer to onprem SAN specs or usb thumb flash drive?
  • 25:57 Zoom Towny: Do you have a quick/easy way to determine if a given query historically used the stats density vector vs histogram when estimating cardinality for a given index?
  • 27:44 SleepyDBA: Hi, I have trouble with a vendor application that includes a table with three columns and just one row. Application actively updates that row. So, table has large number of ghost rows, querying it results in 32k logical reads. is the existence of ghost rows connected to RCSI?
  • 28:43 Eduardo: For running SQL Server VM in the cloud, does one cloud vendor provide better storage price to performance ratios than the others?
  • 30:37 Ezra: Should SQL DBA’s know docker containers?

Finishing Up Your Free Azure Networking Training

Microsoft Azure

To celebrate this month’s launch of our new class, Fundamentals of Azure Networking for the Data Professional, we’re making one video a day completely free. Here’s what’s on tap this week:

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

Like the class? Wanna catch up or re-watch at your own pace? Use coupon code CheckTheDNS for 50% off the $89 price. Start learning now!

[Video] Office Hours: SO MANY Good Questions


Seriously, how do y’all keep coming up with so many great questions? I went through your top-voted questions from on a live stream on my Twitch channel, like I do on most Wednesdays & Thursdays, and really enjoyed these:

Here’s what we covered:

  • 00:00 Start
  • 04:14 Groove_timer: Hey Brent, Trying to convince my employer to not do In-Place upgrade across our SQL estate that is mostly 2012/14 instances. Rather they did side-by-side. But due to costs constraints they are not willing to do this. Are SQL Server in-place upgrades still ill advised?
  • 07:15 StuckInAQuagmire: Have you ever had a situation where a stored procedure took too long, so application reran it without stopping the original, and it took long, and it cascaded into zero resources available and the only way to stop it was to stop SQL Service?
  • 09:39 Bomi: Hello! I watch your office hours constantly. Love your teaching pragmatic database usage. But I’ve never used MS SQL server. Is there a Brent for SQlite or MariaDB or Postgres or something? Or should I start using MS SQL server just because it has the best learning material?
  • 11:32 Peter: I’ve JUST learned about Query Notifications. Normally I’d use event hooks in an app to raise notifications about data changes, but this would be useful for situations where the data can be changed outside of the app. What are the drawbacks / gotchas to using Query Notifications?
  • 14:00 .Net Dev: Hi Brent, it seems like azure sql DB doesn’t allow me to update the setting for cost threshold for parallelism. Do you know it’s not configurable? Do they automatically modify the setting depending on the needs my DB?
  • 16:50 Garðarshólmur: What is your opinion of the new snapshot backup support in SQL 2022? Is this the answer for multi tb database backup?
  • 18:11 Kevin: Morning Brent, This may seem trivial. I’m a little leery about moving from SSMS 18.12 to 19.1. Any gotchas there that you know of, please?
  • 19:29 Kris: Hi, In SQL Server 2019 is there a way of running dynamic SQL with EXEC where the SQL statement is over 8000 characters?
  • 20:18 Slonik: What are your favorite PostgreSQL blogs?
  • 21:55 Psycho (SPID) Killer: What is your opinion of the hybrid buffer pool / PMEM support in SQL Server?
  • 24:34 CJ: Since SQL Agent alerts aren’t available on Azure SQL Managed Instance, is there a list of equivalent items we can/should monitor thru the Azure Portal (Azure Monitor). Any resources you can provide would be great.
  • 25:40 Dom: What would be the most important “features” that you would see as “requirement” for a monitoring tool ? We are looking at something that monitor everything so it won’t be “excellent” with SQL but I wonder what is the “must” that it should be monitoring (OS excluded)
  • 27:32 Eduardo: Do you know of any good tools that parse SQL query plan XML to extract the params / param values for use in SSMS query testing?
  • 28:06 Konya: What is your opinion of Azure Synapse?
  • 30:57 Brandon: I’ve seen your article on “3 connection strings” for scaling transactional systems. What do you think are the best options now for providing read only replicas for the near-current and older connection strings?
  • 31:27 Aditya: When should you use async auto update stats vs sync auto update stats?
  • 34:40 Erum: What is your opinion of the Microsoft announcement for new support of .NET 5 C# Language Extension for SQL Server?

[Video] Office Hours: While Stack Overflow is Down


Stack Overflow was down, so y’all posted questions at and I gave ’em my best shot.

Here’s what we covered:

  • 00:00 Start
  • 04:33 Amir: Does Az-900 worth the effort and money? What about other microsoft certifications?
  • 06:16 Mr. M: Hi Brent. Which feature in SQL Server you like the most?
  • 08:43 Q-Ent: Hi Brent,I took mastering classes bundle and nowI am on Mastering index part.While you do your magic on the queries, you never consider statistics.Do you think statistics update is an obsolete maintenance method?I am really excited and I totally recommend everyone to join a class
  • 09:28 Yvette: What’s the recommended max ceiling for a single AG group in terms of number of DBs and max size per DB?
  • 11:37 Ricardo: What should I do when I say to a new customer “no, you should not be partitioning that fact table. It’s not helping with the overnight load”. But they ignore me and continue to have problems (My default reaction is to silently fume).
  • 13:33 Netanel: What is your official jingle / song?
  • 14:11 Benji: Does table row / page compression affect SQL backup performance?
  • 15:28 Ralph N.: Do you know of any slick ChatGpt fact checking tools?
  • 16:26 Freyja: What is your opinion of using column store index on a temp table in a stored proc?
  • 17:25 Yitzhar: What is the best way to capture locks for a problematic stored procedure when you are asleep?
  • 19:05 Rufus: Does Data Warehouse vendor typically drive reporting software choice or vice versa?
  • 20:23 Karthik K: Hello Brent, My friend is trying to create new NCI on CCI(Clustered Columnstore) table having other NCIs, sometimes it generated sub-optimal plan for the index creation part taking too much time. Is there way to influence the optimizer like providing trace flags? Pls suggest
  • 21:49 Tonio: HI Brent, Is there a good way to estimate how much memory a SQL Server instance running OLTP workloads needs? Any easy calculation based on database sizes, biggest tables, etc.? I have customers who insist that 16 GB memory is good for their 500 GB DB and have performance issues.
  • 24:09 Replicant: Been years since I’ve used replication, avoided as much as possible, how is it in new versions of SQL. Would you use it?
  • 25:08 Denish Patel: Power BI freeze the SQL whenever it fetch the full data, is there any effective way to solve such large data grabbing operations on database which also have lots of INSERT operations.
  • 25:44 Don’t Bother Asking (DBA): Hi Brent, Have any clients approached you regarding the implementation of CICD with a database in the pipeline? I mean automating integration/deployment of database changes alike application changes (bugfixes/updates) using CICD tools. What is your catch with such requirement?
  • 27:25 Hany : Hi Brent, Trying to look for a Lifetime Access payment option for Level 2 Bundle (All your Fundamentals & Mastering classes plus SQL ConstantCare and the Consultant Toolkit), couldn`t find it, only One Year Access is available, is it possible to add it? Thanks, Hany
  • 28:16 Bocephus: Can you show us how you like to use dual monitors with SSMS?
  • 29:01 Tonio: Is there any feature of Oracle Database that you wish SQL Server had? Also Thanks for the previous question’s answer 🙂
  • 30:02 Piotr: What is your opinion on naming and nesting of CTEs?

SQL ConstantCare® Population Report: Summer 2023

The short story for this quarter: SQL Server 2022 adoption rates have stalled, even backtracked, and it doesn’t appear to be due to the cloud, either.

For the long story: ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates? Let’s find out in the summer 2023 version of our SQL ConstantCare® population report.

Out of 3,124 monitored servers (up from 3,002), here’s the version adoption rate:

The big 3 versions are all within 1% of the last quarter’s numbers:

  • SQL Server 2019: 38% (up 5% since last quarter)
  • SQL Server 2017: 19% (steady)
  • SQL Server 2016: 27% (down 1%)

On the other extreme:

  • SQL Server 2022: 1% – actually down, from 52 servers last time to 46 this time
  • Azure SQL DB: 2%
  • Azure SQL DB Managed Instances: <1%

Just 13% of the population are running unsupported major versions (2014 & prior), and that’s steady from the last report.

Here’s how adoption is trending over time, with most recent data at the right:

It looks like companies are standardizing on SQL Server 2019 for new installs rather than SQL Server 2022. In the past, we’ve seen development environments going live on new versions first, ahead of production pushes, but we’re not even seeing that anymore.

I’ve written about how SQL Server 2022 still isn’t ready yet, and how even the updates are breaking. This feels like the moment where we call it: companies are skipping SQL Server 2022. It’s a shame, and I know Microsoft employees have to be frustrated about this, and I know I’m not going to make any friends at Microsoft by saying the quiet part out loud, but here we are.

Free Azure Networking Training This Week

Microsoft Azure

To celebrate this month’s launch of our new class, Fundamentals of Azure Networking for the Data Professional, we’re making one video a day completely free. Here’s what’s on tap this week:

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

Like the class? Wanna catch up or re-watch at your own pace? Use coupon code CheckTheDNS for 50% off the $89 price. Start learning now!

[Video] Office Hours: 30 Minutes of Rapid-Fire (And a Little Burnination)

1 Comment

Almost all of today’s questions from were great! Well, except two.

Here’s what we covered:

  • 00:00 Start
  • 02:13 Andrei: What’s the appropriate response/punishment for developers who insist on storing json and xml in nvarchar(max) fields?
  • 03:43 JustGoogleIt: What are your thoughts on Jeff Moden’s “Black Arts” Index Maintenance — GUIDs v.s. Fragmentation? How would you configure index maintenance on databases with mostly GUIDs for the clustered indexes but some ints sprinkled in?
  • 04:31 Eli: Any thoughts on whether the availability of NVMe storage interfaces on Azure Ebsv5 and Ebdsv5 VMs will finally help close the gap on cloud IOPS and throughput?
  • 05:19 Praveen Kumar: Hello Brent, I want to change the datatype from ‘int’ to ‘bigint’ on a partitioned table with 2 billion+ rows. What is the best way to do this with with minimal downtime? I don’t want to create triggers to capture DML query changes as I’m afraid that it might impact performance.
  • 06:23 MrGPT: The SQL CPU reached 99 and I saw the ‘704 Workers Created 300 Idle’ log and I couldn’t find any wait type on SQL except Hadr_Lease_Timeout, but this is about cpu reaching 99. How are so many workers created? Only solution set Max Workers 1000 ? 2 servers with AlwaysON
  • 08:28 Venkat: Is the Azure SQL DB auto indexing similar in index suggestion quality to Clippy in canned SQL Server (i.e. disregards key order, joins, etc)?
  • 09:29 Fritz: Where do you shop for your cool meme shirts?
  • 10:15 MooneyFlyer: Sometimes I create schema-bound/indexed views to improve select performance. How can I see the detailed execution plan SQL uses for inserts/updates/deletes on the tables used by the view? SQL is only giving an “Index Update” operator in the execution plan.
  • 11:25 Benji: Do you ever do the exotic car racing in Vegas?
  • 12:41 NetworkGuy1262: We use SQL Standard with multiple databases ~128G on NVMe disk. Combined, 700G. Servers have 384G RAM. Can you suggest a test method or tool to measure the performance benefit upgrading to Enterprise rather than sticking with 128 buffer pool limit of Standard with fast NVMe?
  • 15:37 Sergi : Is it possible, using SQL, to raise the CPU usage % as privileged time? I understand that this metric is only used for system processes but I’m not sure.
  • 16:13 Jason from Michigan: I occasionally see an error of “Time-out occurred while waiting for buffer latch type 2 for page”. Rebuilding the index “fixes it” but I am curious what the mechanics are of what’s causing this error to occur.
  • 18:57 Dwayne: Which twitch gamers do you enjoy watching?
  • 20:20 Pytzamarama: Hi Brent! We have 8 hotel databases on one SQL Server Enterprise, on premise. The managment have opted for moving them to Azure SQL. Since hotel business is a 24/7 one, what is the quickest way (the least downtime) to migrate all 8 SQL Server databases to Azure?Thanx!
  • 21:40 Jesse: How would someone get into SQL Server consulting? What do you like the most and hate the most about being a consultant?
  • 24:08 Rufus: How do you track down data corruption when the corruption source lies in networking hardware?
  • 25:11 Iskenderun: Should we expect much of a server performance hit when we simultaneously enable SQL transport encryption and SQL encryption at rest (TDE)?
  • 26:03 Tony: How many of your customers run SQL Server on Kubernetes? Is this a skill worth learning?
  • 27:11 Tim: Any plans to purchase the Apple Vision Pro?
  • 28:51 Vishnu: What are the tell tale signs that the network bandwidth for your SQL Server is not sufficient?
  • 30:01 Eduardo: Are there any risks to our SQL Server for running long periods of time without a reboot?
  • 30:48 StuckInTheCloudDBA: Hey, Brent how do you approach index creation for applications such as Informatica MDM where include columns are not supported? Will it be possible to have optimized enough performance only with indexes with key columns?
  • 32:27 Izmir: Does commercial SQL monitoring software negate the need for Query Store?

Free Azure Networking Training This Month!

Microsoft Azure

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

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

You want to understand:

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

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

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

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

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

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


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

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

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

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

Normally, XML looks kinda like this:

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

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

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

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

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

[Video] Office Hours: DBA & Developer Relationships

1 Comment

Y’all posted & upvoted questions at, and we finished up the session talking about the relationships between developers and database administrators.

Here’s what we covered:

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

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


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

Here’s what we covered in this episode:

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

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

Blogging, Videos

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

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

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


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

Here’s what we covered:

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

How to Make Database Changes Without Breaking Everything


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

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

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

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

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

Let’s start with the Stack Overflow Users table.

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

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

destructive set of database changes would be to:

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

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

Instead, a constructive set of changes might be:

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

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

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

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

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

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

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

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

Yes, constructive changes are more work for you.

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

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

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

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

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

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

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

1 Comment

I start today’s Office Hours by playing with the very underwhelming Github Copilot in ADS, then take your questions from

Here’s what we covered:

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

Updated First Responder Kit and Consultant Toolkit for June 2023

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

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

To get the new version:

Consultant Toolkit Changes

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

sp_Blitz Changes

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

sp_BlitzLock Changes

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

sp_DatabaseRestore Changes

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

For Support

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

When you find a bug or want something changed, read the 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 and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

The Best Career Advice I Ever Got (#TSQL2sday)

Steve Jobs. Photo by Matthew Yohe.

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

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

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

Steve Jobs said, “Real artists ship.”

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

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

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

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