Office Hours, Silent Film Edition


Hey, remember in yesterday’s video how I said I was having a really bad day? Well, here’s how bad it was – I recorded two videos in a row, and I forgot to turn my microphone on for the second video.


So here are the questions & answers:

00:25 Calvin H: Any tips or gotcha’s for query tuning of hierarchy id data types in MSSQL? No. Just be aware that recursive CTEs (which are usually used with hierarchy IDs) produce single-threaded plans. If you have a big complex query, and HierarchyIDs are part of that, you might consider dumping the HierarchyID info to temp table first, and then joining to that.

01:22 Artis Leon Ivey Jr: What is your opinion of using Azure Backup to backup SQL VM instead of native SQL backup? Use the tool that your team is most comfortable with. Backups aren’t the place to experiment. If most of your team is Windows admins and they’re used to Azure Backup, then stick with that.

02:22 B. Horrowitz: What are your thoughts on having multiple different DB technologies across different microservices for a single app? What should the DBA team look like in this scenario? The place where you usually see that is when you have a lot of microservices, each with their own dedicated development teams. You don’t see it with 2-3 microservices with 5-10 total developers. So if you’ve got several 10-20 person dev teams, each using their own data persistence layer, then you usually see them doing their own database administration.

04:04 Cameron Harding: Who is the Brent Ozar for Microsoft Fabric training? Microsoft. They’re the only company that can afford to continuously lose money building training material for a product that’ll get a name change and architecture revamp every full moon.

04:59 Red Utley: Is Intel Hyper-Threading ever worth the licensing pain for SQL Server performance improvements? When you license physical boxes, you license physical (not hyperthreaded) cores, so there’s no licensing pain there. If you’re licensing by the guest, then typically the bigger CPU problem is the noisy neighbor VMs, not hyperthreading.

05:55 ChompingBits: I found out our backup plan, doing full backups on all our user databases (around 46 TB) was a out of the norm. (I cede it is overkill to do Fulls every day, but it sort of shook my confidence a bit. In your career what did you find our was a house policy and not a best practice? Happens all the time – it’s called cargo cult programming. Also, be aware that daily fulls on 46TB is indeed totally normal – it’s just that you’ll wanna use SAN snapshot backups at that size.

07:32 Maria Bonnevie: Should we ever be worried about high number of sessions / connections to SQL Server in relation to other metrics (sessions to sever memory, etc)? No, but I’m usually concerned when I see >1000 connections because at some point, they’re all going to pipe up and do something simultaneously. That’s where your heartaches begin.

09:07 Montro1981: Hi Brent, I’m on a fishing expedition and I hope you can direct me to the right pond, I have a filtered index on a date range. But the stats histogram is going all wonky on me all the time the first 100 steps are 1 day each but the rest (7470) gets put in 1 step. That’s beyond an Office Hours question, and it’s a consulting-style question. It’s just such a niche topic.

09:50 hamburger sandwich: Is there automatic windows OS patching for availabilty groups where it will failover and update? Not in a way that you’ll be comfortable with if you have 24/7 activity and long-running tasks like backups or CHECKDB. Every patching tool I’ve ever seen will happily patch and reboot in the middle of a database backup or CHECKDB.

10:51 Aashly: Hi Brent, We have an upcoming project where the team plans to to perform an in-place upgrade from SQL Server 2017 with mirroring to SQL Server 2022 with AOAG on a prod server. What are your thoughts on this and what precautions can be taken if we are left with no choice. I don’t do it.

11:51 Paul Oakenfold: Will you be visiting the new Fontainbleau Hotel when it opens? Not for a while at least. To learn more, check out VegasPaulyC and VegasStarfish on TikTok – they’ve done behind-the-scenes tours & discussions of it.

12:33 She-Ra: Is clustered index fragmentation any more consequential than non-clustered index fragmentation? Watch this.

13:10 Hal: What’s your opinion of Jupyter Notebooks and have you considered using them in your training classes? I absolutely love them, but … because they don’t show query plans, I can’t use them in training classes. You can get plans in Azure Data Studio query tabs, but not in notebooks.

And now if you’ll excuse me, I’m going to go throw my computer in a river and pour myself a drink.

Previous Post
The Black Friday Sale is Here: Save Big on Training & Apps
Next Post
[Video] Office Hours at Megunticook Falls in Camden, Maine

6 Comments. Leave new

  • In re: the hierarchyid question, I’ll push back slightly and say that if you’re using a recursive CTE to find data after doing the hard work of materializing the result of that CTE into a hierarchyid column in your table, you missed the point. While you may use a CTE for either or both of initial population or ongoing maintenance of that column, you’re amortizing the cost there so that traversing the hierarchy (i.e. selects) should be fast.

  • Sorry to hear that you had a bad day at the office!

  • LOVE IT! I’m going to have someone read me your questions and answers while I watch your video. hahahahaha!

  • I agree, it is definitely time for a drink.
    Laptop swim optional.

  • Thanks Brent, I was hoping you had seen this behavior with the filter statistics on a datetime column before. Oh well further down the rabbit hole I need to go

  • >>And now if you’ll excuse me, I’m going to go throw my computer in a river and pour myself a drink.<<
    Now, now, was it really the computer's fault?


Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.