[Video] Office Hours: Waiting for the Hot Tub to Fill
While waiting for my Husavik hot tub to fill up, I took your top-voted questions from https://pollgab.com/room/brento.
Here’s what we covered:
- 00:00 Start
- 01:28 1440×1080: always on with 2 nodes 1db, readable secondary. I’ve optimized both nodes to a point where the primary can withstand both workloads.i want to downgrade licensing from enterprise to standard. What is the process to switch from AOAG to BAG ?Do i just change the license ?
- 04:05 Compatability Level 140: Soon after auto-update stats triggers, I get single-row estimates for date ranges starting with yesterday. I thought the modern cardinality estimator was supposed to prevent this major error. What do I need to study?
- 05:11 Chase: When would having tempdb configured to only have a single file (mdf) be favorable, even if you more than 1 CPU? I was taught to have an mdf per CPU up to 8, then test for balance. A vendor system imports faster using temp tables using only 1 mdf, which makes no sense. Any ideas?
- 07:00 Manolis: Do you foresee a specific DB technology exploding in popularity due to close alignment with AI?
- 07:22 MySpecialHoneyPot: Hi, in various cases SQL Server chooses to use the clustered columnstore index where a suitable nonclustered index exists that delivers better performance. Besides index hints, is there a way to push Query Optimizer to not use the clustered indexes?
- 08:37 green tissue: What breaks when databases are owned by windows users? People always say it’s evil but I have never found a demo or specific example.
- 09:40 JerseyDBA: I was looking through sp_blitz on a server and found all the DBs have the legacy target recovery time set to 0 not 60. Auto checkpointing isn’t causing issues, any harm in leaving this and only flipping to 60 seconds if I start seeing I/O issues?
- 10:55 Austin Powers Shagadelic Box: My big server’s CPU never hits 70%, but I see lots of parallelism waits. Will adding more cores help? I’m just checking to see if I understand how SQL server uses CPU.
- 12:15 James: Hi Brent, In your opinion, would creating triggers to enforce cross database referential integrity be something you would consider, and not fall in the generic realm of ‘bad practice’?
- 14:08 chris: Do we know what Azure Managed Instance is using “under the hood” for HA/DR? If not Availability Groups is it something proprietary?
- 15:03 ChompingBits: What are you thoughts on how to work with vendors that don’t follow best practices. Our environment is mostly SQL isntances for vendor software we buy. We run SP_Blitz weekly and see lots of configuration warnings. How do you tell a vendor they don’t know what they’re doing?
- 16:44 AzureNewb: Do most of your clients use Azure Managed Instance or Azure SQL?
- 18:21 Contineo: Not all RDS instance types support putting tempdb on the local storage. What would you choose between: – a r5d or m5d instance with tempdb on the local storage but a EBS Bandwidth or 4750Mbps or – a r6i instance without tempdb on the local storage but an EBS Bandwidth of 10Gbps
- 20:05 SGU: So while I know that “Missing Indexes” are not 100% reliable as to the exactly index to be created, is there a was to determine what query “caused” that missing index recommendation to be made?
- 20:58 Depthcharge: Hi Brent! I’ve been trying to reduce PAGEIOLATCH_SH on a 25TB production database. Monitoring tool identified a 25TB+/hour of logical reads from 300 exec/hr “Task Manager” commands in the master DB, no associated SQL. Worth investigating, or should I focus on individual queries?
2 Comments. Leave new
For Green Tissue – In support of Brent’s comments, a wise person once told me “if I knew then what I know now, I’d know a lot less now”
Vendors using SQL Server wrongly, I had two similar cases with totally different outcomes.
After an upgrade, the software became very slow. My analysis showed that the vendor had added a lot of lock hints. Users complained (sometimes wait times of > 1 minute for a simple data change). After an intensive talk with the vendor (together with the product owner in our company), the vendor agreed, took out the hints and deployed their changes the next week. After that, the software was as fast as before.
Second case: Query Store showed extremely many lock waits. Similar case: lock hints, often even ‘lock table’. I informed the vendor, but they very friendly did nothing. A reason might be that the software is working, so there was nobody in the business that I could get along to make pressure. The software can be supported by SQL Express Edition, on the same server where the software is running. My guess is that the vendor originally used Access, which AFAIK, has no transaction protection (it is not a multi-user database), and there they had to lock tables explicitly to avoid loosing transactions.
So my guess is that if business is impacted, users and product owner are complaining, involve the product owner in negotiations with the vendor. It might increase your chances that the vendor will listen.