Most of the https://pollgab.com/room/brento questions from this episode had really short and sweet answers. Let’s take a break for the holidays and knock ’em out:

Here’s what we covered this episode:

• 00:00 Start
• 03:29 TheyBlameMe: Have you ever made it across the finish line for a customer by changing the way their application connects to SQL Server (i.e. OLE DB vs ODBC driver)?
• 03:51 Sleepless in Seattle: How often, if ever, do you use partially contained databases?
• 04:36 GUIDKeysWasteAndFlushBufferCache: With a GUID PK & CX on a large table, most new rows will land on 8K pages containing only old, obsolete data. Almost every new row (that will be accessed again soon) has a whole 8K page in the buffer cache! Buffer cache will be mostly old, obsolete data. Am I missing something?
• 06:26 Gökçe: What should you do when you notice the third party vendor app is using NOLOCK like butter all over their TSQL queries? Have you experienced this?
• 06:59 Björgvin B.: Entity Framework is spamming the SQL plan cache with many duplicate plans. How do you deal with this issue? Use sp’s instead?
• 07:22 Anastasios: Do you have any good stories where SQL Server was blamed for slow app performance but turned out to be something completely unrelated to SQL Server?
• 08:27 MyTeaGotCold: Has your opinion on agent jobs changed in the past 5 years? AWS lambda and its kin seem to be replacing the Task Scheduler.
• 09:13 Stooky Sue: As SQL migrates from on-prem to the cloud, do you still see separation of duties between DBA and Storage Admins?
• 10:12 bagllop: What would you suggest my friend to use instead of linked servers? SSIS? OpenQuery? Or is there some new fancy stuff he could use?
• 11:41 Rena: Is adding foreign keys just to get join elimination ever advised?
• 12:28 Persephone: What scripts do you like to use for Columnstore Index maint?
• 12:52 Encino Man: Is there a good way to determine if a given query from C# is using the density vector or histogram for cardinality estimates?
• 13:40 Stooky Bill: For SQL Server, Microsoft recommends a minimum target for page life expectancy of 300 seconds. Is this still a good recommendation or does it need updating for modern times?
• 14:10 DBADany: Hey Brent, in a working environment with multiple DBAs having sysadmin permissions, are you aware of anyway we could audit who restarted SQL Server? Apparently SQL Audit only tracks the time for stop/start itself but no details of hostname or IP from who did it? Thanks
• 15:15 Huyang: What is your opinion of Azure NetApp Files and Silk cloud ISCSI SAN for hosting Azure SQL VM data files?
• 15:26 OnSiteDBA: I have heard you mention PostgreSQL as a cheaper RDBMS alternative to MSSQL, but hardly mention MySQL. Is there a reason you hardly mention MySQL? notable performance hitches, feature-specific limitations etc.
• 16:46 KyleDevDBA: Hi Brent, Do you have a favorite wait type to fix and why?
• 17:12 Renzi: What are your pros / cons of app consistent snapshot backups vs crash consistent snapshot backups for multi TB boxed SQL db’s?
• 18:29 Stooky Bill: What are your thoughts on the TOP operator short circuiting parallelism? Good thing or bad thing?
• 20:07 ChompingBits: Why is it that the Microsoft owned apps are always the worst offenders with SQL issues? Sharepoint and SCCM have longstanding known issues with deadlocks. SCOM is a beast that spawns all kind of GUID named agents jobs. SCORCH doesn’t support availability groups.
• 20:57 Argyris P.: Do you have any good ways to find all large (billions of rows) static tables in a boxed SQL Server instance?
• 21:38 Alex: Huge fan. What’s your opinion on azure database fleet and should I aim to replace my elastic pools with this new feature or is it another gimmick?
• 22:32 Pytzamarama: Hi Brent! When we update our customers databases (a lot of them are still on SQL Server 2008R2) for a new app version, we drop/create all procedures and triggers. How does this impact performance? Thanx
• 24:12 Toymaker: Is there ever value in perf testing queries cold vs hot (I.e. DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE)?
• 24:37 WouldPrefer3rdNormalForm: Have you ever seen or heard of a ceiling/threshold for XML columns, beyond which performance craters? We are scared by two large XML columns (and their indexes). They are currently performing adequately, but comprise about about 40% of our 1.5TB database and continue to grow…
• 25:49 Kane Baden: Was literally typing a question around “what do you think are the main questions around picking MSSQL vs. Postgres…” while watching your last upload when you spelled it out for the last question on that SaaS question. So I figured I should let you know thanks instead! Thanks!
• 26:10 Bonnie: How do you run sp_blitzcache to target a temporary stored procedures for analysis?
• 26:35 bottomless: Azure SQL Database Serverless is supposed to cost less but at the end of the month it costs more than DTU: our SaaS has routines and jobs that wake up the database. From you experience have you aver seen a successful use of Azure SQL Database Serverless?