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?
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
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?
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?
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?
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?
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?
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.