Blog

I step away from the backyard long enough to take y’all’s questions from https://pollgab.com/room/brento.

Here’s what we covered in this episode:

• 00:00 Start
• 02:23 Stockburn: Hi Brent, we are using distributed always on AGs, with a node in AWS for DR. We are thinking of using this to quickly migrate the work load to the cloud by failing over to the DR node, building a second server in AWS. Have you done this, recommend it or run away screaming?
• 04:38 Developer who cosplays as a DBA: Is parameter sniffing inevitable in a database that scales large enough? Or are there SQL Server rockstars out there who can write all their queries to be immune to parameter sniffing?
• 06:35 dirty-dba: I am tuning a SUM query. Removing all non pk indexes from the table makes the query run faster than it does with just the specific indexes required for the query, I am subscribed to your training package, but can’t get my head around why is slower with indexes
• 07:46 Vishnu: How frequently should we be updating 1. First responder kit, 2. sp_WhoisActive, 3. Ola Hallengren’s SQL Server Maintenance Solution?
• 09:17 Boutaga: Hi Brent, as a production dba I use SSMS templates scripts folder as a repository for my scripts, this is synced through onedrive. What tool would you recommend as a script repository which would sit between that and a local gitlab ? Is there any ?
• 10:24 Bleona: What should someone do if they witness scripts / source the IT manager brought from their previous company?
• 11:41 Enca : Can a SQL query be expensive without being slow or is slow + expensive mutually inclusive?
• 12:28 Dilip Kumar: Are there any gotcha’s with using SELECT TOP 0 C1, C2 to initialize temp staging tables?
• 13:15 Fernando Soler: Several of our SQL Data files have file stats showing 99% reads vs 1% writes. Are there any optimizations to be on the lookout for in this scenario?
• 14:11 Haluk Bilginer: What is the top Kerberos issue you run into with your clients running SQL Server? How do you like to troubleshoot?
• 15:24 Janis: We are on SQL Server 2016 and plan to migrate to 2019, as for now I have a couple queries where memory grants are going out of roof (1,5 gb for one query). Where I can find more info or deep dive in memory grants as all YouTube videos and google doesn’t help? I know 2022 adaptive
• 16:29 Kareena Kapoor: What are the recommended online training courses for running SQL Server in an Azure VM? What are the recommended online training courses for running SQL Server in an AWS VM?
• 17:05 Can Yaman: What is your opinion of using Azure Backup to back up Azure SQL VM?
• 18:53 Sean: Hi Brent! Did you have a chance to visit the volcano when you were in Iceland? I’m debating booking tickets to see the latest and greatest.
• 20:38 Pythor: Where is a good place to learn about source control for SQL Server stored procedures and schema changes?
• 21:22 Rajesh Khanna: What are the top issues you see for shops that choose to use SQL tables as queues?
• 22:16 Satnam Singh: Brent, Can you please help if there is any T-SQL to find DTU Utilization for each session on a Azure PaaS Database.
• 23:01 GiddyUp (409): Is there any forensic evidence you like to capture prior to killing a long running SQL agent job?
• 24:15 Dafina: Can a given table simultaneously support both fast OLTP and OLAP queries or is this not recommended?
• 25:30 DadJokerDetroit: If an SQL Developer recursively bangs his/her head, will they end up with a CTE injury?
• 25:55 Dumitru: Can I enable Accelerated Database Recovery, Always On Basic Availability Group and Log Shipping on primary replica of SQL 2019 Standard Edition?
• 26:25 KG: What’s the best way to migrate/upgrade an existing availability group to a new server and new sql server version? I’m primarily looking at log shipping versus leveraging the existing availability group.
• 27:12 Eduardo: How are the advertisers for your twitch stream chosen? How are the advertisers for your youtube stream chosen?
• 27:56 Sql100: In one of your q&a sessions, you had mentioned that creating index as well on the foreign column would also have efficiency of retrieving the data – with the include having multiple columns or just that particular foreign key index? Thank you in advance, as always.
• 28:39 Mike: Can’t download SqlServer Azure data to Excel with MFA login, only SqlServer Authentication. Is there a way using MFA login to download Azure data into Excel?

There are only a couple weeks left in this summer’s marathon of Office Hours sessions, which means there’s limited time left to ask your toughest database problems (not trivia questions) at https://pollgab.com/room/brento.

Here’s what we covered in this episode:

• 00:00 Start
• 03:22 tanchenglai: Hi Brent, I have implemented a system that records data in another factory. When changing shift, the data cannot be inserted into DB. I suspected due to too many machines causing the storage problem (hardware issue). How could convince the hardware guys to upgrade server storage?
• 05:44 MooneyFlyer: Hi Brent! Do you have any recommendations on how to manage self-referencing tables? Say an Org Chart and queries like “Does this employee have this manager in its hierarchy?”. I usually solved this with recursive CTE, but I’m concerned about performance. Sorry for the WofT!
• 06:28 Juan Falcon: We are heavily invested in SSRS 2019. Should we be looking at other options/products for when we jump to SQL Server 2022? Please advise.
• 08:01 BeanZW: Using Merge statement for Update, Insert and Delete from source to target with the selected date time in target table. Is that the correct way using Merge?
• 10:07 Benbo Baggins: DBA’s believe indexes are a function of data not schema, will change over time and vary between clients depending on their data usage. Dev’s believe that they are a function of schema and should be defined to suit the data and application. What are your thoughts?
• 10:54 RoJo: We have 12 remote Devs that use our Dev SQL DB to develop on. Is there a way to safely allow this on the Internet without giving VPNs to every Dev? or other options for sane development remotely ?
• 12:58 Era Istrefi: What criteria do you use to decide if a given foreign key should have a supporting index or not?
• 13:52 StatisticallyUncertain: If there are any, what are come cases should automatic statistics creation should be disabled?
• 16:25 Jiun: Hi Brent, I’ve a SQL procedure that my web site will call. The procedure executes a Java script. My problem is that it’s taking too long to execute (about 6 seconds), and over half of the run time is spent importing libraries and loading objects for the script to use.
• 17:38 Azul: What are the pros / cons of all devs sharing a single SQL Server instance vs hosting their own instance which they keep in sync / up to date? Which do you recommend?
• 19:32 Nora Istrefi: When should a computed column be persisted?
• 22:55 johnkurt: Non-cluster index has pointers to the clustering index in a rowstore table or rowid to a heap-tabel. How is the connection between Non-cluster and cluster on a cluster-column store index ?
• 23:56 Rani Mukerji: What is your opinion of PowerBI vs Tableau?
• 25:50 T: Best DR solution for SQL Server 2019 Standard Edition
• 26:30 Dua: What is your opinion of using TSQL merge statement to update DW dim target table via source staging table?
• 26:47 Dhurata Dora: What is the best resource for learning about modeling DIM and FACT tables in SQL Server?
• 28:02 DBA with MBA: Have you run into issues with Nutanix? Our 1TB DB (Mfg production OLTP, not super busy), after restore to more $ Nutanix (3-4 nodes), runs ~30% SLOWER on default settings due to Nutanix’s difference with HyperV/SAN. Vendor suggests like splitting MDF into many vDisks and more.TIA
• 28:53 DKKimbo: We keep getting query store going read only after an internal filestream error message is logged. File stream isn’t enabled, have you seen filestream cleanup errors on systems that don’t have it enabled?
• 29:55 lucky12345: Does the closed source nature of MSSQLServer make it less vulnerable to hacking attempts when compared to PostgreSQL?
• 30:19 Neithan: This may be irrelevant but can you explain what’s the point of the isolation level? And why aren’t everything Serializable?

I took your questions from https://pollgab.com/room/brento 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 ?.

Post your SQL Server and Azure SQL DB questions at https://pollgab.com/room/brento 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?

Seriously, how do y’all keep coming up with so many great questions? I went through your top-voted questions from https://pollgab.com/room/brento/ 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?

Stack Overflow was down, so y’all posted questions at https://pollgab.com/room/brento 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?

Almost all of today’s questions from https://pollgab.com/room/brento 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?

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

Here’s what we covered:

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

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

Here’s what we covered in this episode:

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

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

Here’s what we covered:

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

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

Here’s what we covered:

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