You post questions at https://pollgab.com/room/brento and upvote the ones you’d like to see, and my job is to come up with accurate answers on the fly. Let’s see how I did.
Here’s what we discussed:
- 00:00 Start
- 03:25 SickOf: Brent is there a backup product you can recommend?
- 04:38 OneEyebrowRaised: I’m noticing three significant shortfalls in Always On: 1) Login synchronization is manual, 2) Scheduled Jobs synchronization is all manual, 3) stored procedures put in the system databases aren’t shared across nodes. Do you know of any tools to address these shortcomings?
- 06:11 Sean C: Hi Brent, TIA for roasting me lol. We use dynamic SQL to loop through 200+ cols to validate against a set of specs, resulting in a lot of plans being painted when tuning. Is there a way to suppress benign exec plans like looping through commands, etc to reduce bloat?
- 07:47 I’ll be BacH: Is Data Modeler still a career path? Or has that merged into Database Developer? Do you see any sub-specialties in the Database Developer career field?
- 10:05 Doug: Do folks ask easily Googled questions intentionally?
- 11:11 Isaac Wahnon: You answered Rojo about using Distributed AGs for version upgrades, saying “No because it’s so much work to set up.”We want to upgrade from 2019 to 22,What upgrade procedure can reduce risk and downtime for a system with AGs and Distributed AGs? In-place or replace.
- 12:27 macfergusson: Hey Brent, in one of your courses you mention using GUID PKs and clustered indexes, they aren’t nearly the bad choice that a lot of DBAs make it out to be. If you do go this route, are there any different best practices that you recommend? Fillfactor, extra memory?
- 13:11 JediMindGorilla: Hi Brent. I always tell people “I am not the guy that can code a cup of coffee from 0, I am the guy that makes it taste better”… is it common to have people looking for SQL jobs that may not have as much “coding” experience (code from scratch), but are good at performance?
- 15:15 Sammy: An architect I know likes to default every Primary Key INT with -2,147,483,648 or BIGINT and its lowest to avoid resizing. Brilliant or needlessly clever?
- 15:57 Accidental_dba: Can we run multiple backups on a single server as we have 100+ databases? Currently we use olaha scripts. Currently backups starts at 12am and finishes around 7am which is when all our stores open? Trying to make backups finishes by 5am
- 18:18 Just_Winging_It: Brent, In your experience, when you rebuild indexes and fragmentation is still present, what are the usual suspects to check? Please feel free to roast me.
- 19:14 gjocarroll (George): Hi Brent, have you encountered any new entry into your Top 5 Waitstats/issues in the last few years/SQL Server versions?
- 20:35 Universe For Rent: I’m often tasked to review SQL code that’s about to be pushed to production.
- 22:04 ArchibeaR: Been working with SQL Server since 6.5…. I know i’m old. Working with a new team building Azure setup from the beginning. Wondering if you have some suggestions on reading to get upto speed.
- 22:46 Developer Who Cosplays As a DBA: I recently ran into compilation timeouts in a prod database that caused web pages to time out, and I’d love to put together a demo of comp. timeouts for educational purposes. Do you have any tips for how you would go about intentionally writing a query with a long compile time?
- 23:45 Ive_Got_Heaps: Hypothetical: Brent decides to sell all of his cars (see hypothetical) to fund a new database engine. What are your dream bells and whistles? Sub question, when can I invest?
- 25:18 Piotr: What’s the largest server you have seen log shipped? Any issues with shipping large servers?
- 27:15 core: hey Brent! Is there any easy way to detect SQL Server Agent missed jobs? For example, if the SQL Server Agent is stopped due to an issue with the service, server, or planned maintenance. thanks!
- 27:14 Guilty DBA: Hi Brent, is there any way to configure the querystore (QS) to handle identical Pans more efficiently ?
- 29:21 Yevgeny: How far back in SQL versions does office hours go back? Has the format / content / hairstyle of office hours changed much since then?
- 30:54 Haydar: Is there a good way to programmatically obtain the query hash / query plan hash of a query after execution for auditing purposes?
- 31:40 Eduardo: How do you recommend implementing sproc debug logging when the sproc could be running on AG primary or AG readonly secondary node?
- 32:40 Wren: Hi Brent! Do you think the noted 2019 query/performance slowdown might patched any time “soon” (next year or so)? Or is it just too “baked in” to the version?
- 34:25 Jessica: Hey Brent, I’m going to be in Vegas for a tech conference in about a month. What’s a good local restaurant or coffee shop that are just amazing to go to that are away from the “tourist” areas
- 35:58 GP Geek: How do you get more than 8k or 4k into an NVARCHAR or VARCHAR variable? I’ve had a recent case where the limit was reached without the user noticing it and missing data
1 Comment. Leave new
Regarding the Backup question (15:57 Accidental_dba):
To decrease the backup duration
– do your backups local, the network may be the bottleneck
– let another server (your backup server) get (pull) the backups; do not let your SQL server copy the backups to the other server (this would need write permissions which can be bad in the case of Ransom ware)
– test different settings for @BlockSize, @BufferCount and @MaxTransferSize, this can help very much (I use @BlockSize = 512 for Log Backups (usually very small backups), 65536 for DIFF and FULL; @MaxTransferSize = 4194304 and @BufferCount = 512 * 1024 * 1024 / @MaxTransferSize;
– consider not doing FULL backups every day, often only a very small part of the DB is changed so that a weekly FULL and daily DIFF may be enough
– consider to create multiple database groups and do the full backup for grp1 at Monday, for grp2 at Tuesday…
– do not shrink your database / rebuild the indexes every day, this will cause only tons of load / big diffs but does not really help your performance