Office Hours: Ask Me Anything About SQL Server

Post your questions at https://pollgab.com/room/brento and upvote the ones you'd like to see me discuss. Here's what we covered today:

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