Blog

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

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

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

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

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

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

Here’s what we covered:

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

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

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

Here’s what we covered:

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

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

Here’s what we covered today:

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

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

Here’s what we covered in this episode:

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

ChatGPT, Resource Governor, manually created stats, Always Encrypted, and as always, fragmentation: let’s answer your questions from https://pollgab.com/room/brento.

Here’s what we covered today:

• 00:00 Start
• 01:13 Timbalero: Hi Brent. my friend knows your view on rebuilding indexes. He also thinks that external index fragmentation affects pretty much only readahead scans. For scientific purposes, what metrics should he look at to see if defrag makes a difference (if only marginal)?
• 02:21 chandwich: Hey Brent! What kind of advantages (or disadvantages) do you anticipate with the recent emergence of ChatGPT, specifically in the SQL world. Have you used it?
• 04:23 BrentsFastCars: Hi Brent, I have been reading one of your posts about running SQL Server in a virtual environment. You talk about when there are more cores than Standard allows and using affinity masking to disable cores. Have you seen your customers disable hyperthreading as another solution?
• 05:40 Bill Bergen: Brent…I have to say it again….you are a genius….now to the question….is there a way to correctly and completely use TSQL to script out all parts of resource governor for migration to another server
• 06:48 TomInYorks: Hi Brent. What arguments are there for and against manually creating statistics on every column of every table when auto create/update statistics options are enabled?
• 09:15 Wasn’t_Me: A software company produce a software for compensations and bonuses. They cannot use their own software internally otherwise some employees could see the compensations of other employees. Can Always Encrypted be the solution and who should own the keys? The CEO?
• 12:25 Life-long Learner: My friend asked me the other day while we were talking about archiving a 2TB Audit Table, to reduce its size and we asked what was better, rebuild the indexes or drop and recreate? Thank you very much for everything, I love your courses!!!

Y’all just never run out of interesting questions at https://pollgab.com/room/brento! I’m impressed, got another great round today.

Here’s what we covered:

• 00:00 Start
• 00:25 Chrisbell: Recently we’ve been facing thread starvation issues. How can we troubleshoot it when even sp_whoisactive ( even with DAC ) is unresponsive and takes forever to return a result set ? How can we get to the root cause of this since its all happening within a few seconds ?
• 01:24 Lu: We disable TLS 1.0 and 1.1 on our windows servers; how to affect the SQL server and SSIS jobs?
• 05:19 Macrieum: Hi Brent, I am at the beginning of my sql career and trying to set up select query across 50+ sites/servers that all share a vpn/domain. 4+ hours of work/week saved if the query returns to a central location. Can you point me in a starting direction? Thank you
• 06:32 RoJo: What is best way to update/patch with least downtime? patching current box seems risky.
• 07:41 Don’tBotherAsking: Hey Brent. Love your work. We have a database in which all PII (names, birthdates, etc) is column-level encrypted. Query performance is getting worse over time–presumably because it has to decrypt more and more data. Is there any way to optimise queries on encrypted columns?
• 09:25 Kalfr: What are the best options for oopsy query recovery in SQL 2019 Enterprise AG shops?
• 11:28 pete: How important are non-equality columns in indexes after all they might follow 2-4+equality columns
• 12:42 Safraz: Hey Brent. From your weekly links, I discovered that EVE ONLINE used SQL Server 2005 at one point and they documented their issues with scaling hardware to improve performance. I’m not sure if this is still the case but have you ever consulted for them?
• 13:30 Piotr: What are the top causes for SQL log shipping breakages?
• 14:17 Robbaco: Could 2 Create Index statements in the same DB and same schema lock each other (no FK relations) because of inserts/updates/deletes on the sys-tables? could data for unrelated objects change in the sys-tables (something like a reorg)?
• 15:26 PhilRich: I have an automated process (weekly) that restores backups (Full/Diff/Logs) to a test server – runs DBCC – and then reports on all the databases. Is there any point in running DBCC on the production servers?
• 18:22 Dru: What’s the best monitoring software for vanilla PostgreSQL, Aurora PostgreSQL, and Azure PostgreSQL?
• 19:08 Mike: Sorry if this has been asked before, but – if you were to explain to your boss who does not understand who Database Administrator is – and what he does – how would you do it ?
• 20:35 Kevin: What’s your least favorite part of being a consultant? what advice would you give to someone who wants to follow your footsteps? Thanks

After I stopped selling live classes, I took some time off all live broadcasting period. It was a nice couple of months over the holidays, had a good time with the family, and now I’m starting to fire up my Twitch channel again.

I’m not setting a schedule yet, just broadcasting when I have time available, so if you want to get alerted when I start streaming, subscribe to that channel and turn on notifications.

Here are the questions we discussed:

• 00:00 Start
• 06:17 JimLic: In moving to new physical servers with virtual disks which are claimed to be ‘better’ than our old physical disks, replicas are showing super slow redo queues. What is the importance of block size across replicas? Anything we configuration with SQL and/or disk? Any Tests?
• 08:20 Chris Stoll: Do you have any resources/tips on a new dba needing to inventory the servers/instances/databases at a company? I have around 30 servers that I want to start documenting that currently have 0 physical documentation. Thanks Brent!
• 09:54 Mike: I’ve never used a Mac nor Azure Data Studio. Will Mac and ADS be enough to perform my DBA duties, or should I restrain to Windows and SSMS due to ADS missing something important?
• 10:55 TK_Bruin: Yo Brent! What would you say are the top 2 or 3 functions or features that SQL Server has added over the last 5 years that have been the most transformative or promising in terms of business benefits?
• 13:18 Ted Striker: Any tips or gotchas when tuning queries that use OpenQuery to run a remote OleDB SQL query?
• 14:21 Roger Murdock: What are the best/ worst filegroup design strategies you see in the wild?
• 15:48 chandwich: Hey Brent. I just completed your Fundamentals of and Mastering classes, but I haven’t applied it all directly to my job yet. How would you recommend I show this off on my resume?
• 17:00 Dru: When default invocation of sp_whoisactive takes 3.5 minutes to produce a resultset, what is the first thing you would look for in those results?
• 17:38 Nick12: Hi Brent. How’s your week? Is there a way to avoid an eager spool for the halloween problem in a simple UPDATE query that sets a column to a fixed non-null value and filters for that column being null?
• 20:56 Wren: Would you recommend using a surrogate key similar to a row-id (autoincrement integer) even if there is a usable unique PK column in a table?

I am waaaay overdue for a haircut, but instead of being a responsible adult, I stopped to take your questions from https://pollgab.com/room/brento.

• 00:00 Start
• 00:43 Mike: We have 3 Dell PowerEdge R630 servers with SQL Server installed. Everything functions for 3.5 years straight. How long is it expected to work?
• 01:30 Shalom: What are the worst incidents that you have witnessed due to SQL errors being routed to a mail folder that nobody ever reviewed?
• 02:40 TheBigMC: Hi Brent. I’m about to start a new job where I’ll be looking after 100 SQL Servers. I’ve been told that’s a guess. How can I reliably scan a network to find servers people don’t even know exist
• 05:00 Steph: Hi Brent, what are your top most dangerous seemingly benign SSMS menu items for which you shouldn’t approach your mouse pointer when connected on a prod database (for instance I once misclicked on ‘Fragmentation’ in ‘index properties’ on a prod db…). Thanks.
• 06:02 Tim: Hi Brent. With Windows Server 2022 you can set the allocation unit size of a disk up to 2M. Is 64k still the best practice for SQL Server?
• 07:58 DGW in OKC: Do people actually use Identity columns any more? What are the pros and cons of this practice?
• 08:26 IndexingForTheWin: Hi, the company I now work for has taken the decision since 3 years to completely stop index rebuilds and only do stats updates. Wouldn’t we benefit from rebuilds (perhaps yearly)?
• 09:18 Hamish: What are the pros/cons of using TSQL PRINT for debugging sprocs vs using table variables for debugging sprocs?
• 09:44 Max: A friend of mine ask, what is better – add a bit field and index it on VLTB (over 2 Tb in size) with 60+ fields and 13 indexes already OR create a new table to store PK values of rows which have value 1 for this new field? Thanks
• 12:10 John Bevan: Q1. When you have SQL running on an AzureVM, is it acceptable to use the D drive (i.e. low latency but ephemeral) over an attached disk for the TempDB?

Normally, y’all post and upvote great questions at https://pollgab.com/room/brento, but in today’s episode, y’all upvoted some stinkers. Buckle up.

• 00:00 Start
• 00:47 SQLKB: Hi, according to sp_BlitzCache I usually have more than 260k plans in cache, created in the past 1 hour, is it a big number? Comparing number of plans from exec_query_stats vs exec_cached_plans the numbers are 260k vs 130k , what could cause the diff between those numbers?
• 02:23 Chase C: What do the options under linked server provider settings mean? “Allow inprocess” is frustratingly un-googleable and my technical manuals for SQL Server are also rather short on content. Cheers!
• 04:09 sELECT RAM: You mentioned that PLE is useless recently. Why? and what is the alternative?
• 05:21 Call Me Ishmael: Will SQL Server ever mandate the semi-colon as a statement terminator?
• 07:35 Mike: What do you think about running SQL Server in Kubernetes for Production workloads in year 2023?
• 09:07 Yitzhak: You once used a nice analogy in relating pilots to air planes and DBA’s to SQL Servers. Will you please share that again?
• 10:47 Haddaway: When moving large tables to a new file group, does it ever make sense to do the migration with bcp command line vs using TSQL to copy the data to new location via insert?