[Video] Office Hours: Microsoft Database Q&A
Let’s go through a LOT of your top-voted questions from https://pollgab.com/room/brento on a VERY early Saturday morning.
- 00:00 Start
- 01:52 DBAInAction: Hi Brent Microsoft just announced Automatic Index Compaction for Azure SQL and Fabric. do you see this finally killing off the traditional ‘index maintenance’ debate? Also, any gut feeling on whether this will eventually be backported to box versions of SQL Server? Thank you!
- 04:19 VladDBA: Hey Brent, meant to DM you but figured this fits office hours: I’m on 2-month garden leave and weighing whether to hunt for another job or launch my own consulting business. If you were in my shoes, what would you do?
- 06:28 Jacob H: What is everyone using for stress testing SQL Server in 2025? Looks like Microsoft has deprecated all it’s tools. (Distributed Replay and it’s replacement Replay Markup Language Utility)
- 09:01 EagerBeaver: hi Brent, when populating table from data from another table via INSERT INTO SELECT is there a way to OUTPUT columns that are not being inserted? I need to store mapping between old and new PK and the only way I found was to use MERGE which you don’t recommend. Thanks
- 10:30 Thomas Franz: Auto Create statistics is enabled on my DWH (billions of rows in partitioned Columnstore tables). When someone executes a query that uses on uncommon column first time in WHERE / JOIN it takes forever to create an execution plan. Is there a way to autocreate the stats async too?
- 11:56 Zale: Ola Brent, have you encountered any significant performance issues in SQL Server caused by the use of cursors?
- 12:37 SteveE: Hi Brent, Do you know of any resources for creating good AI prompts for query tuning? The people who appear to get the most out of AI appear to be good at writing prompts
- 14:57 MyTeaGotCold: Has SAN multipathing improved over the last decade? I remember when we had to be very careful with vendors not having true multipathing.
- 18:43 Jason Not JSON: Should we start designing schemas differently now that AI tools prefer semi-structured data like JSON?
- 21:11 Felipe: Hi Brent, Nowadays, do you think it’s still worth becoming or remaining a DBA specialist? Maybe this is just my impression, but I’ve been working as a DBA for over a decade (mostly with Oracle) and I’m starting to think about changing my career to another role.
- 23:48 .Net Dev: I’m on Azure SQL DB at compat level 140. I’ve heard you say that being “out of support” is a valid reason to upgrade. Since SQL 2017 is hitting EOL, does that logic apply to compat levels in Azure? Or is it fine to stay on 140 indefinitely if I have no performance issues?
- 24:36 Andrew G: I am SaaS support. I work with VMs and SQL server. Why do t-logs, if not properly maintained, affect memory / cause timeouts? Is this something to do with TempDB? If you could let me know if you have a course that covers, this currently working through fundamentals! TY !
- 27:59 Dopinder: We have many old sp’s that reference no longer existent columns but fortunately these sp’s are no longer used. Is there a good way to force a recompile for all sp’s so that we can identify these crusty / old sp’s that error on recompile so we can delete? SQL 2019
- 29:16 Matteo: Hi Brent, I just finished the ‘Faster, cheaper, cloud databases’ module. How much do you think the recent price increases for physical RAM and SSDs will impact the way we evaluate the cost-effectiveness of cloud VMs?
- 32:21 YouTubeFreeLoader: As a query tuner, how do you address or identify performance issues that might be a data issue in a system you aren’t familiar with. For example, a query suddenly performing worse but the root cause is a data issue like a job isn’t running that should be but you don’t know it.
- 34:50 How Did I Even Get Here: Just an update. I recently asked for ideas on choosing a pet project (don’t worry, during work hours). I set up a CMS to deploy/run sp_Blitz and its friends across 12 servers from my CMS and collect data in a table on the CMS. It’s AMAZING! Is this still rare, and if so, why?
- 38:19 Josef: How do you index for a WHERE condition with LIKE ‘%’ + @SearchText + ‘%’?
- 40:06 chris: Howdy, Brent! When working with a client to resolve a problem they’ve brought to you, how much time would you say you spend on documentation after you’ve landed on a solution?
- 43:35 DickBowen: Is using DBCC SHOW_STATISTICS to extract the histogram information into a temporary table for a lookup of RANGE_HI_KEY values a good idea?
6 Comments. Leave new
Thank you for the responses to the 21:11 mark question regarding remaining a DBA. I’ve had concerns lately about databases moving to cloud providers/vendors, AI platforms becoming increasingly used, etc. I’m definitely on the back side of my career and the notion of restarting in an adjacent or even entirely new field was equally distressing.
However your answer bolstered my confidence and I feel it’ll be alright. It may not necessarily be with this company, but someone somewhere is still going to need on premise SQL support long enough to get me to retirement!
Thank you, needed to hear those words.
Regarding Dopinder’s question at 27:59. Well, that is what database projects (SQL Server Data Tools) are for.
Years ago I found an ancient script by Choco Smith at: https://chocosmith.wordpress.com/2012/12/07/tsql-recompile-all-views-and-stored-proceedures-and-check-for-error/
The script will not catch all errors, however. And it uses SET FMTONLY ON, which has been deprecated. “When FMTONLY is ON, SQL Server does not execute any data-retrieving statements, but only sifts through the statements to return metadata about the result sets.”
You might give it a try. GL.
You can using C# write something fairly easily using SqlParser to find all references of a column. I do this all the time and have utilities that can tell you not only what statement any table or clause is referenced in but I can also tell you how each column is being referenced. From there it’s just a simple check to see if the column referenced in “sys.columns.” I use my tools all the time to find where you have size mismatches.
Wayne – I’m going to reply with the same thing I’ve been replying to you about before: if it’s easy for you, show your work. Write a blog post or Github project showing how you do these things, and then when you comment this stuff, link to your blog post or repo.
I’m going to put you on a comment time-out for a while. When you’re ready to share your work, email me a link to the Github repo or blog post at help@brentozar.com and we’ll talk through it from there. Thanks for understanding.
Terrible advice related to the DBA that found stored procedures using dropped columns. Absolutely terrible.
That DBA has no clue if the stored procedure is being used but probably can only tell you the last time those stored procedures were called. Just because a stored procedure was not called doesn’t mean that stored procedure isn’t still being referenced in code. That’s why I have mentioned some of the tools I have written to find that type of code in C# code using Roslyn. Using SqlParser it is possible to find any code in Programmability that references a column that is no longer in the database. Every table reference gives the aliases so you only have to look up those referenced columns. In C#, you can run this code multithreaded so you can go through thousands of stored procedures in minutes.
I would want to know the DBA that dropped those columns without checking the database to see where those columns were still being referenced. That’s something a DBA should have done and if that affected an application the DBA should have been let go along with any manager authorizing that drop.
The one question everybody should be asking is whether or not those stored procedures that aren’t being used (as well as those that are) are currently in a repository in their current state. There might be some application that isn’t in active use that may still reference those procedures so those procedures may still be needed. I still reference legacy apps in C# that were written in 2003 and many apps are still in active use that were developed in .Net 3.0. If that code isn’t archived then somebody should be getting their butts kicked and deleting such code should be a terminating offense.
A good project manager would like to know the list of stored procedures that are referencing dropped columns. I think they would like to check why those columns were dropped as those columns should be documented in something like JIRA. Doing any cleanup should be something that is schedule and no DBA should be dropping columns without following common sense procedures.
CRINGING…
Wayne – I’m going to reply with the same thing I’ve been replying to you about before: if it’s easy for you, show your work. Write a blog post or Github project showing how you do these things, and then when you comment this stuff, link to your blog post or repo.
I’m going to put you on a comment time-out for a while. When you’re ready to share your work, email me a link to the Github repo or blog post at help@brentozar.com and we’ll talk through it from there. Thanks for understanding.