[Video] Office Hours: 22 Good Questions and a Stinker

Lots of good questions on today’s broadcast! If you’d like to submit one, go to https://pollgab.com/room/brento and upvote the ones you’d like to see me cover.

Here’s what we covered today:

• 00:00 Start
• 01:05 Testing123: When inserting or updating data into a table, from a concurrency perspective, does it make sense to break up the complicated SELECT logic into a staging table and then bulk load/update the data into the destination. i.e. Will SQL only grab exclusive locks at the end of the tran?
• 03:00 Curious DBA: Hi Brent. What query tuning approach would you take to force SQL Server to do multiple backward seeks instead of one expensive forward seek? I.E. Composite PK on (ID, Datetime). Search for max(datetime) on a single ID does a backwards seek, multiple IDs do 1 expensive fwd seek.
• 05:11 Dru: What are the pros / cons of setting the SQL Server vm clock to UTC time instead of local time?
• 06:08 George : Is it OK that when you give a caustic answer I call you “Brentward” in a slightly disapproving tone? (As if it’s your full name) P.S. I’ve gotten a new job with help from your classes. Definitely going to get the full set.
• 07:55 DianaCarneiro: Hi Brent, I’m currently using an AlwaysOn AG configuration with WFCLs, and it has a few instances running on 2017. I was wondering if I can upgrade just one instance from 2017 to 2019 while keeping the rest on 2017. What do you think? The info about it is nothing concrete.
• 09:43 Sid: What is the advantage of using sys.partitions over doing a count(*) over a table to get the count? We seem to be having some locking issues since sys.partitions is the same table as opposed to count(*) being separated. maybe a nolock hint?
• 11:20 Mike: When you deploy Azure SQL Server VM (preinstalled SQL 2019), it only shows VM cost. For Ent. Edition – what will be the additional (hidden) license cost per CPU core ? Is it $274/month, same as in new 2022’s Pay-As-You-Go license billing model, or it is different amount ?
• 12:08 Stone Tablet Monk: What is the best data warehouse design book for SQL Server?
• 13:43 depthcharge: Hi Brent, took your Fundamentals of TempDB course and used it to diagnose and correct GAM page contention in TempDB, thanks! Is the guidance for correcting GAM waits the same for user databases? All my googling just turns up articles on TempDB.
• 15:00 Elad: I have a table with identity column as clustered PK with high fragmentation. only inserts and updates without any deletes. updates is datetime2, decimal and int columns, no string datatype Table size ~500K rows with ~25K updates and ~10K inserts in 24hr. What can be the cuase? 15:54 Stone Tablet Monk: What are your favorite PostgreSQL books?
• 18:09 GucciRules: Hi Brent, in Azure SQL Managed Instance, backups are automated; however, it doesn’t appear that the system databases are included in these automated backups. Any idea if they are in some form, or whether we need to run our own sysdb backups?
• 19:10 Steve: We have a consultant who disabled CPU0 because he had a box with 128 cores and CPU0 was experiencing huge contention with VMWare activity. This server has 8 cores, so disabling a core is a big impact on performance. Should I ask to enable the core, or does he have a point?
• 21:18 Porsh-uhh: I remember hearing vaguely about issues with SQL Server 2022 pre-release which negatively impacted monitoring tools in some way. Are there any issues like that with the release of 2022 and do they affect the First Responder Kit? Anything to really worry about deploying 2022?
• 22:14 Dru: Is SSMS query plan viewing better with one large monitor or multiple smaller monitors? What is optimal monitor size for this?
• 23:24 Phineas: What are your thoughts about manually clearing SQL wait stats? When should this be done?
• 24:12 The Fall Guy: How do you decide when to store data as JSON blobs vs storing data in a well defined schema?
• 25:15 Tugay Ersoy (Admiralkheir): Hi Ozar, We have enabled CDC in SQL Server 2016 to catch the changes. After a while, the log file got too big and we couldn’t shrink it.When CDC is open, it pulls SQL Log status to REPLICATION and does not allow us to process,so we had to close CDC How can we implement a solution
• 26:25 Dance Monkey: Is it reasonable to update stats with full scan for a single table NC index as the first job step before running the next job step that does work on a very large DW Fact table?
• 29:15 Kevin: Asking for a friend: is the order of records guaranteed when inserting them in a transaction? The app sends row1 and row2 with their own CreationDate (set in the app) but in SS, row2 has an earlier date. Is this expected behavior? If not, any starting points?
• 30:37 ShiftHappens: HI Brent, in my job 99% of the time I do not need to do any kind of sql tuning, however there are times when there comes these really complicated procs whose perf is bad and I try my best to tune but I am not the best at it. Any advice on how I can get better at it?