[Video] Office Hours Speed Round: 21 Answers in 15 Minutes

HONK HONK! It’s time for a fast round of answers to concise questions y’all posted and upvoted at https://pollgab.com/room/brento.

Here’s what we covered:

• 00:00 Start
• 00:19 TheyBlameMe: Hi Brent. What is your view on using Dacpac diffs vs “old style” manual update scripts for deploying DB schema changes in dev-test-prod?
• 00:52 Alex: Hi boss, I have an app that creates a new DB for each customer. All DBs (3K~) have the same structure. Is there a best strategy or an article you can point to consolidate all DBs into one huge DB. Is it a good idea? I’m trying to save on maintaining hundreds of databases. Thanks
• 01:42 Perplexed: Had a vender application running a stored proc and it wasn’t working. Ended up using Profiler to capture error message and passing that to vendor to fix. Is there a better way to find error messages that procs are kicking out, but hidden by error handling in proc?
• 02:31 Piotr: Is there anything comparable to first responder kit for PostgreSQL that you like to use when performance tuning in on that side of the fence? How hard would it be to write a first responder kit for PostgreSQL?
• 02:56 HashMatch: Hey, Brent! I have a work superior who prefers to use several UPDATE statements instead of joins, to “keep track of row counts”. How do I best demonstrate this isn’t a good idea for performance?
• 03:39 Steven: Hi Brent, in a nighlty ETL my friend has 2 sprocs updating 1 table in parallel. A page-level deadlock occurs randomly (1 in a 100 runs). Any tips or ressources on how to fix a deadlock at page-level while keeping the sprocs parallel? Thank you
• 04:34 Piotr: Do you have any recommended tools for diff’ing two SQL tables for the purpose of showing the index differences between the two tables (DEV – PROD)?
• 04:55 Björgvin: Do you ever see any issues with using windows mount points and SQL Server?
• 05:29 TY: Hi Brent, in my job we often use the ROLLBACK of a transaction for testing purposes. Is there an easy way to rollback after two or more days, when the TRAN has been already COMMITED? Like a checkpoint where you can return to, but only for a single table or a database?
• 06:04 Piotr: Do you think we will ever see FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION in SQL2019 again? Is this feature worth upgrading from SQL 2019 to SQL 2022?
• 07:01 Mickey: Hi Brent, I have a reporting query that runs under 10 seconds on other environments but runs for hours without finishing on this one environment. I’ve verified stats are up-to-date and the proper indexes are the same across all environments. Any other recommendations? Thanks!
• 07:44 Gromit: Do you have a good way to fix high VLF count that doesn’t break log shipping?
• 08:04 Nick Smith: Hi. Why might performance degradation of truncate tables in tempdb be seen after some time working in SQL Server 2019? There are no schema locks. I make look wait types trace in the current session, then there is nothing but SOS_SCHEDULER_YIELD and SOS_WORK_DISPATCHER
• 08:37 LostInSpace: I have a developer using WAITFOR DELAY (10 – 15 minutes) to pause the iterations of his code instead of doing it in c#. I noticed them using sp_WhoIsActive with parms: @find_block_leaders = 1, @get_locks = 1, @get_additional_info = 1. It leaves suspended connections. good/bad?
• 09:28 WorkinForDaMan: I’m the DBA for a city with a hybrid environment (on-prem and Azure VMs). While I was on leave, IT pushed 2016 AZC+GDR build instead of, SP3+GDR. I’m comparing both for diffs but wonder if you’d suggest reverting to SP3+GDR since we don’t have managed instances. Thank you, sir!
• 10:07 Haydar: Do you have any recommended books / courses for sizing azure vm’s for lift and shift of SQL Server?
• 10:53 Doc: Do you encounter peeps that live on the cruise ship in retirement? Is this an option for you?
• 11:30 Ron Howe: What diagnostics would you recommend for a SQL Server that is fully 100% CPU throttled during query execution due to a “bad” query plan and you can’t get a SQL connection as such and a hard reboot seems the only solution?
• 12:03 Nortzi: Hi Brent, recently a SQL statement with a begin tran and commit tran was executed from ssms and returned a results message. We had a blocking issue the next day. Turns out this query was still technically running and never finished. What do you think could have caused this?
• 12:56 TY: Hi Brent, it seems like you know everything about SQL Server, or at least it seems like it. Can you do a short session with something that you don’t know much about and lead us through the process of learning it. It would be very beneficial to see how you build your knowledge. Ty
• 13:56 marcus-the-german: Hi Brent, do you recommend that the sql server instance collation is the same like the user databases. If yes, how should we deal with databases which have a different collation?