Office Hours Speed Round: Text Edition

Some questions that come in at https://pollgab.com/room/brento have such straightforward answers that I don’t need to cover them on video. It’s time for a rapid-fire speed round:

Q: Uncle Buck: Is there a good way to tell if a given include column for a NC index is being used at all?

No.

Q: Steve E: Hi Brent, In the past you have shared links to a “reading list” tool you used to bookmark various online articles and share them with others,what was the tool please? I’d like to bookmark the various articles I’ve read over the years and be able to easily share those with others

For bookmarks I use Pinboard, and here are mine. For RSS feeds and newsletters I use Feedly, and here are my shared items.

Q: FrankieG: When faced with hundreds, if not thousands of NonSargable predicates over a wide range of solutions/products that have been in production for years in a huge base of legacy code how to begin to “fix” the problem?

Use sp_BlitzFirst @SinceStartup = 1 to find your server’s top wait types, and use sp_BlitzCache to find the top queries causing those wait types. I explain more about that process in my How I Use the First Responder Kit class.

Q: Faruk: Is it a fair expectation for employers to expect DBA’s to also write reports (SSRS, Power BI, etc)?

Generally, development and administration are separate job roles.

Q: Luis: Any performance gotcha’s to watch out for when the query plan is using the Filter operator to satisfy a non-sargable search predicate?

If you understand enough to write the question, then you already understand the answer. Performance will be bad.

Q: Last Action Hero: What are your thougths about contained databases?

I explained the problems with ’em here in 2009: part 1, part 2, and part 3. As I predicted, most DBAs have still never seen these in the wild, nor do I expect them to in the near term.

Q: Jose Luis: I need to migrate my existing DWH in SQL2012 to a new OnPremises SQL 2019. Should I consider AlwaysON for it? maybe SQL Failover Cluster? Or just a BIG Standalone Server? Most of my DBs are in Single Recovery Model.

For personalized architecture planning, hire me for consulting.

Q: Reed Richards: What is the best resource for extended events training?

Start with Jonathan Kehayias’ series 31 Days of Extended Events.

Q: Punxsutawney Phil: I thought that when you refresh a database on a nightly schedule for reporting/production use, that’s considered a “groundhog day” method and should be avoided. Yet, I’ve seen you twice in recent months recommend that as a solution to refresh production reporting databases.

Groundhog Day refers to the practice of reloading data via logged methods: inserts, updates, deletes, SSIS, etc. Restoring a backup is not a logged method.

Q: Leon Spangenberg: I keep seeing trace flag 3604 being turned on and off in my sql log. Why would someone do this?

That is detailed here.

Q: Max_Null :A very small table in Azure SQL used to keep track of locking in the application (~15 columns, no triggers) and has no more than 3 rows at any given time. Other than stale stats and blocking, is there any other reason that this table would be slow for a simple SELECT?

If you’re trying to track locks in a table, you’re going to hit locking problems, period, full stop, end of story. Bad design idea. Do something different.

Q: Alan: Do you think cheap cloud storage, like s3, will eventually replace old fashioned tape libraries and off-site backups?

For many of my clients, it already has.

Q: FrankieG: Hi Brent, can you talk about and explain why upgrading to the rewritten cardinality estimator (i.e. upgrading SQL Server from v2012 to v2016) causes serious performance degradation and the only way around it (that I’m aware of) is to force using the legacy estimator in settings?

Yes, I cover that and the alternatives in my Mastering Query Tuning and Mastering Parameter Sniffing classes.

Eyes up here, kidQ: Debbie Downer: Does Stack Overflow db have more downvotes than upvotes? If so, why?

You can download the database here for free to find out.

Q: Stan Sitwell: Is PostgreSQL consulting as opportune and lucrative as SQL Server consulting?

I have no idea – I don’t consult on PostgreSQL.

Q: Pina: Should I drop temp tables at the end of my stored procedures? What if there is one stored procedure that calls a bunch of other stored procedures, each of which has several to many potentially large temp tables? Is there a performance gain to dropping them? A performance loss?

I cover this in my Fundamentals of TempDB class.

Q: Ravi Pratap Singh: what are the books need to cover for good SQL understanding

Check out this list.

Q: Does Basically Anything: Hi Brent! When looking to tweak MAXDOP from a “sane default” to the best setting for my workload, what metrics should I pay most attention to as I try different MAXDOP settings to test the performance difference?

I cover that in the parallelism module of the Mastering Server Tuning class.

Q: DGW in OKC: Have you ever used encrypted connections (like SSL) to a SQL instance? Is there ever a reason one might be required to do this?

No, but I’ve heard of security teams requiring it in some cases.

Q: Need4Speed: A friend asked if there was a way to bring a detached database back online if you are missing one of the three data files.

Detaching a database is dumb. Deleting a database is dumb. If someone did *two* dumb things to a database, I’d say you know what, I can’t trust those files. Let’s restore from backup. If that person tried to fight me, I’d say, “You’re the one who did two dumb things already – how’s about you step away from the keyboard and cool off for a while, and let the pros take over?”

Q: Kirk Saunders: Are there situations where you would use a RIGHT OUTER JOIN? I have found I can put that table higher in the JOIN structure and LEFT OUTER instead. I just don’t want to miss out on a solution option if there is a real strong use case for it.

Me personally, I’ve never used it.

Q: Have you tried turning it off and on: We want to implement RCSI on a (10k batches/sec / 4.5TB / 7K session) server to help reduce blocking. What QA do you suggest, and any books/videos to consult?

Check out the isolation levels module in my Mastering Server Tuning class.

Q: KIRBY W BURKHOLDER: While watching a recording of “Mastering Query Tuning” recently, I remember you mentioning a problem the CE has with “Ford” and “Mustang”. My friend has the problem but all Fords are Mustangs and all Mustangs are Fords. Vendor supplied code that he can’t change. Any suggestion

Leave a comment on the training class module. There’s space for longer questions in there, including PasteThePlan options to include the execution plan, so I can see the exact problem you’re talking about.

Q: George: What is your favorite way to measure storage speed in Azure SQL?

The fast/easy storage test is CrystalDiskMark. It’s not completely definitive – it’s just a hot-or-not test – but in most cases, the storage is nooooot hot, so I don’t need a definitive test.

Q: Neil: We always used default instances. Planning on a multi-instance server (in test) to host multiple web environments with copies of the same db’s on each instance. What are some ‘gotchas’ to look out for? I’d probably give each instance its own data/log folder names, anything else?

The term you’re looking for is instance stacking.

Q: Doug E: Do you ever bother with reconciling AWS / Azure bills to actual resource usage?

Yes, we’ve done it for SQL ConstantCare’s own back end during performance tuning.

Q: SQL Serenader: Hi Brent, We have seen an uptick in dropped connections. Do you think using the deprecated SQL Native Client 11 driver, instead of the more connection resilient ODBC 17 or OLEDB 18 drivers could have anything to do with our connection issues?

I have no idea.

Q: SeeCoolGuy: what is your opinion if you find your colleague forcing the engine join hint to use a hash join vs letting the query engine find it’s own join criteria?

That colleague might have attended my Mastering Parameter Sniffing class where I cover hint usage.

Q: Philip: I’m getting a warning against an Azure SQL DB saying “Non-parameterized queries are causing performance issues” with Azure wanting to turn on ALTER DATABASE Name SET PARAMETERIZATION FORCED. Are you able to talk about this a little, and how you would go about investigating?

I assume you’re getting that warning in sp_BlitzCache or sp_Blitz. Copy the URL into your browser and read that. I’ve written extensively there on how to fix it.

Q: Enzo: Hi Brent, As you like fast cars, will you be attending the first F1 race in your home town next year (Las Vegas 2023)?

Yes, if I can get good tickets.

Q: Roadtripping fool.: Any chance you can make these into a podcast like your old office hours? I used to make my family listen to them on road trips. Ah memories.

No, it took time and cost money.

Q: Michael Devor: Hi Brent, My friend would like to know if there are any special considerations or concerns when tuning indexes on a partitioned table?

Yes. Have your friend attend my Mastering Index Tuning class.

Q: SQL Crooner: What is your opinion of the Parquet file format?

I have no opinion. Never used it, never opened it. Nothing against it.

Q: Festus: What percent of the time do you see customer tables where the primary key / clustered index are on same vs different columns?

Way, way, way less than 1%.

Q: prasad: What r the steps we should take to learn sql in depth?

Use it, help other people solve their problems with it, and then teach it.

Q: Eric S: How many years before our AI overlords displace human DBA’s?

OtterTune is already working towards that for MySQL and PostgreSQL, and I’ll be excitedly watching their progress. I think it’s a cool problem. Given Microsoft’s lack of progress on the automatic indexing front, and given how little of that is coming in SQL Server 2022, I think our jobs are still safe for a long, long time.

Q: SQL_Ninja: Hi Brent, what is your thoughts about SQL server vulnerability assessment functionality or would you recommend any other third party software for similar assessment ?

I don’t get the chance to try every tool out there, so I’m not really qualified to give a good review.

Q: cloudy: Hi Brent, we are currently use on-prem sql server for dwh & reporting, my company is starting to run some workload on aws, what’s your take on migration to Amazon Aurora (we can change the application to support it)?

What’s the problem you’re trying to solve?

Q: Anatoli: What is the best cloud database technology / brand for a shop that has plenty of developers but doesn’t want a DBA?

Use the one most of your developers are most familiar with. That’ll result in faster delivery to customers.

Q: Joe: Hi Brent, Under what circumstances would you set Min SQL Memory to anything other than zero?

None, because I hate instance stacking.

Q: Maksim: What is your opinion of disabling the guest and SA users in SQL Server?

I don’t really care. My bigger issue is usually shared passwords, like app passwords, and disabling accounts doesn’t fix that.

Q: Haydar: Do you foresee any DB company acquisition / buyouts on the horizon? What company takeover is the odd’s on favorite?

I don’t track the business side of the industry.

Q: Gary: Hi Brent. What’s your take on the number of instances one Production DBA should be responsible for? Automation helps and it’s rare everything breaks or needs service at the same time, but it can be a burden for one employee to manage 200+ instances. Thanks!

See this post.

Q: Joel: Do you ever look at sys.dm_os_schedulers for performance related troubleshooting?

Yes. We talk about it in my Mastering Server Tuning class.

Q: ?akir: What is your favorite tool / technique for monitoring failed SQL agent jobs?

Alerts.

Q: Gözde: How often should we back up SQL agent job definitions?

Back up your system databases daily. Jobs are stored in the msdb database.

Q: Ronaldo: Is tech documentation reading a lost art? If so, why?

Yes. Words are great for searchability, but videos are often easier for beginners to approach. Thus the rise in just-in-time learning: people searching for something, finding a 5-minute YouTube video on it, watching it while doing the thing, and then discarding the video, and never really learning the technique. I’m fine with that. It works for beginners.

Q: Leon Spangenberg: Hello Brent. I am inserting row into an existing table. However when ever I do the insert with a predefined set of filter the insert just does not work. I don’t get an error its just like sql sits there and does nothing. Any advice?

Use sp_WhoIsActive to see what it’s waiting on.

Q: Brent: Why are so many of these answers pointing people towards a training class?

Because the answers are 30-60 minutes long, and that’s why I teach classes. I love these questions, don’t get me wrong – but I love them so much that I’ve built entire presentations to do justice to their answers.

Previous Post
I’m teaching Mastering Parameter Sniffing at the PASS Summit!
Next Post
Who’s Hiring in the Database Community? June 2022 Edition

8 Comments. Leave new

  • I did not know you could do that with LMGTFY. That is cold. 🙂

    Reply
  • John Ballentine
    May 31, 2022 4:27 pm

    “Q: Brent: Why are so many of these answers pointing people towards a training class?”

    I think it’s just the opposite, actually – I’m impressed by how much time you spend answering questions WITHOUT referring people to your training. Thanks again for all of your work on this stuff; a lot of people would be more abrupt with some of these, to be generous, odd questions you answer.

    Reply
  • There is a major caveat to the answer to this question

    Faruk: Is it a fair expectation for employers to expect DBA’s to also write reports (SSRS, Power BI, etc)?

    And that is company size. The smaller the company the more likely it is to have the “DBA” really mean “Does anything that touches SQL / the DB server that isn’t app development “

    At a large company with multiple DBAs the dev and maintenance is separated. But a smaller company with one “DBA” it’s more likely to wear the “also writes reports” hat.

    Reply
    • And even smaller company would have someone like me – hired to do DB reporting, a little app development and whatever. But now I spend most of my time replacing monitors, chasing down fax issues, phone issues, PC issues, vendor software issues……. Bleh.

      Reply
  • Roadtripping fool.: “Any chance you can make these into a podcast like your old office hours?”

    Ha! And here I was – wanting to thank you Brent for reading the questions out loud in the videos. When a new video shows up, as I drive home from work I listen to them in the car (youtube -> bluetooth -> car media). So it’s just like a podcast.

    Reply
  • Why is detaching a database dumb? Not that I do it, just curious why that is.

    Reply
    • Not a dumb question at all! My problem with it is that you may not be able to reattach it correctly if it has corruption. (That’s not a black and white rule – there are plenty of kinds of corruption that will still let you attach the database.)

      Reply

Leave a Reply

Your email address will not be published.

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