Office Hours: Holiday Speed Round Edition
Вставка
- Опубліковано 9 чер 2024
- We've got a bunch of fast questions at pollgab.com/room/brento so I rip through 'em. Here's what we covered:
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? - Наука та технологія
You are just great Brent .....
Aww, thanks!
If you want a convoluted way to find the user, line up the restart time and try to match up their login from the Security Event log. It sucks but it's at least something.
You know it is gonna be real when Brent gets THAT close to camera! 😂❤
Thank you very much!
You're welcome!
GUIDKeysWasteAndFlushBufferCache question related specifically to a PK CX, rather than NCX. PK CX is typically much wider than other indexes and is typically the most important index on the table. Unlike many NCX, the table designer actually has the option to choose an ascending key for the PK and CX.
The question is about inserts. Inserts affect both the CX and NCX indexes identically.
@@BrentOzarUnlimited Yes, inserts affect all indexes, but a PK CX is often much wider than other indexes and is usually the most frequently accessed - hence the effect on the CX is far greater than a NCX. A PK/CX will churn much less buffer cache if it has an ascending key, that avoids filling the cache with entire rows of obsolete records. This means recently inserted records of the CX are more likely to remain in the cache - entire rows, ready to be accessed again for key lookups, subsequent operations etc.
The downside of course is potentially page contention. I'd hope that working in memory rather than disk, and having maybe 50 to 200 recent/relevant rows per page (rather than one) would offset this in most instances. Of course every workload is different and milage will vary.
@@KiwiDT Sorry, I'm going to continue to disagree. On a typical table with 5-10 indexes, you're only talking about 10-20% difference per insert, and that's on the absolute high side. If you want to convince me otherwise, you'll need to create an experiment to prove it with a typical table with 5-10 indexes. Cheers!
Fair enough@@BrentOzarUnlimited. I appreciate your thoughts. Thanks for taking the time to discuss!
Brent luv ur stuff ( even though I’m non real sql..) very cool and seem like a good fun guy ( just need to convert u to bikes!) but great watch every time thanks for putting the time into the stream
Thanks, glad you like my work!