Blog

You posted questions at https://pollgab.com/room/brento and I picked the fastest ones to answer:

Here’s what we covered:

• 00:00 Introductions
• 00:16 Disgruntled App Developer: Hi Brent a friend was recently reprimanded for running a trace in production. The rationale was perf impact and other ‘potential issues’ that weren’t forthcoming. Any thoughts on why traces have an impact on perf, when that impact becomes a problem and any other potential issues?
• 00:59 Krishna: What are the top issues you see with SQL Filestream?
• 01:48 Dwight: Hi Brent, any recommendations for where / where not to place masterdb?
• 02:09 StatisticsRules: Hola Companero! When running SQL Server on SSDs can I completely disregard fragmentation and literally never run index rebuilds if I update statistics continually (daily)? I’ve inherited DBs that have tables with 99% fragmentation, gut feeling is a bit queezy… need guidance.
• 02:25 Isaac Wahnon: Have you had experience with SQL installations on Nutanix virtualization? Especially high end systems, in your opinion is there an upper limit for SQL in a virtual environment?
• 03:27 Lucas: Hi Brent, what are your thoughts on the Microsoft Defender for SQL offering?
• 03:40 All_Alone: Hey Brent, Are DBA’s still using Log shipping delay to protect against “oops” deletes? Are there any new developer specific tools that prevent “oops” deletes, so that one doesn’t need a log shipping delay Server for that reason alone?
• 04:33 James: Hi Brent, is there any commercial training available for sp_HumanEvents?
• 04:55 Frito Bandito: Hi Brent, do you know of a good way to overcome the TSQL batch size limit of 65,536 bytes? Trying to update several columns in a single record but wary of hitting the batch size limit.
• 05:20 Louie De Palma: Is SQL in place upgrade (2016 to 2019) acceptable / low risk if the server is virtual and has a snapshot backup of all the DBs?
• 06:02 Neil: Learned a lot from fundamentals classes and then just now fixing a CPU performance problem in Prod. Dev made a log table with no indexes. So I added a clustered index on the id column in Prod. But now I’m thinking I should tell dev to add a primary key to the id column instead?
• 06:37 Rick: Hi Brent, what are the top SQL Server NUMA related issues you run into with clients?
• 07:29 i_use_lowercase_for_select: Hi Brent, hope you’re feeling better already. I was wondering what the title of the happy tune is you play while letting us do our lab work during your training classes.
• 08:30 Ferris B: When you were a DBA, did you keep any kind of journal / documentation to show management that you are actually doing work despite a smooth running system?
• 09:50 The last Bothan: What is the oldest SQL server version you have parachuted in and worked on?
• 10:25 Gizem: Any possibility of including sp_PressureDetector and sp_HumanEvents in future versions of the first responder kit?
• 11:06 Malik: Hi Brent, what are your thoughts about Query Store being enabled by default in SQL Server 2022? Is this safe?
• 12:28 Malik: Hi Brent, what is your opinion of the new SQL Server ledger functionality in SQL Server 2022?
• 13:22 BehindTheScenesDBA: Hello Brent, Any plan to present the Cluster/AlwaysON training with Edwin this year or in the near future?
• 13:52 Özlem: Should new new non-clustered indexes be created during business hours or strictly after hours? Any risks here?
• 14:46 Tu?rul: Does the SQL Server 5k (approximate) row lock escalation mechanism use estimated number of locked rows or actual number of locked rows to make the escalation decision?

I’m back in the US at my home studio in San Diego, so I did a live Office Hours session. Post your questions at https://pollgab.com/room/brento and upvote the ones you’d like to see me cover.

Here’s what we covered today:

• 00:00 Introductions
• 00:41 WTF_SQLServer!?!: I have a some dbs that take nearly 6 hours each to finish recovery after patching. I’ve checked VLF counts and they are reasonable. The dbs are in SIMPLE recovery and logs are showing huge recovery times but millisecond times for analysis, redo and undo.
• 02:04 Marcus: Is SQL Server the only relational DB that can have parameter sniffing issues?
• 02:54 GrumpyDBAsFriend: A friend’s manager wants him to replace a SQL 2012 Enterprise server (24 cores) with a 2019 Standard server (12 cores). This is a heavily-used box with about 40 databases running multiple critical applications… should he just find a new job now?
• 04:38 Ferhat Karatas: I use Ola hallengren’s index maintenance script every night. But I figure out that sql server unresponsive during script execution.
• 06:00 Roland G: Hi Brent, is it ever a “good thing” to create one of clippy’s suggested indexes when the query already runs sub-second but consumes 200,000 logical reads.
• 07:16 Miguel: Hi Brent, what are the top issues you see surrounding Microsoft Distributed Transaction Coordinator?
• 08:38 Oscar G: Hi Brent, is ‘grumpy’ a justified stereotype for DBAs?
• 10:53 Louis: To combat effects of param sniffing, is it acceptable strategy to proactively / automatically run a common query once to put it’s plan first into cache before outliers have a chance to be put into cache?
• 11:55 Hoss Cartwright: Hi Brent, how can you detect when developers are hinting maxdop 0 in their queries?
• 13:42 Beta Ray Bill: Hi Brent, I sometimes see articles where the author has the data file and transaction log file for a given DB on separate drives.
• 15:05 Sultan: Hi Brent, what are the top gotcha’s / hurdles for a long time pessimistic locking SQL developer moving to “A whole new world” of optimistic locking (RCSI) in Azure SQL?
• 16:02 TurnerBurn: Hi Brent. My friend is pulling his hair out (he’s quite bald already) over a deadlocking issue. sp_BlitzLock shows lots of deadlocks of 2 queries, but both are crazy-fast when executed so poor performance does not seem to be an issue.
• 17:40 Egemen: Hi Brent, does Query Store work well with cross database queries?
• 18:51 gserdijn: A database server of a customer has 8 processors and SQL Server Standard Edition. So 4 processors are not being used due to licensing issues.
• 20:30 Sir Logs-A-Lot: Hi Brent, Will you consider writing a SQL anti-patterns book? It would be a New York Times best seller. 🙂
• 21:53 Süleyman: Hi Brent, what are the top features of PostgreSQL and Oracle that you would love to see incorporated in a future version of SQL Server?
• 23:40 FrankieG: In a recent server survey I found the option to use “legacy cardinality” was on.
• 25:10 1440×1080: Hi Brent ! Is there a way to recreate a query text based on its execution plan ?
• 25:53 SqlPadawan: Hi Brent. A career-related question: in your experience, what has helped you to decide when it’s time to move on to another place or to your own thing?
• 27:31 Jason Stapley: Is the future of the DBA role Data engineer or Data Lead or Data Steward or other? I’m seeing a trend toward Data engineer. Nice seeing you at SQL Bits 2022 UK!!
• 29:20 BehindTheScenesDBA: When/if you manage more DBAs under you, how do you keep the synergy/interaction among the DBAs as healthy as possible? Sometimes I see some members act like I am better than the others.
• 30:46 Ned G: Other than “Do it out of hours and use nolock”, How would you suggest my friend queries a couple of error log tables (~5m rows) for Message like %SomeError%?
• 32:24 GI Joe DBA: Management wants their first DWH in Azure. The data will be sourced from a few 100GB Azure SQL DBs. A non-dba name-dropped CDC, is it worth a look or is SSIS\DataFactory the common data feed sol?
• 34:20 i’ll_talk_about_bruno: What is your favorite deprecated feature in SQL Server
• 36:06 sqlsucks: Hello Brent, how was your SQLBits experience in London?
• 39:45 dman: Say one node of your two-node sql cluster is suddenly dead (non-recoverable hardware failure) and you have a 24 hour old vm snapshot for that server.
• 41:46 I want to be on a beach in Cabo: I have a legacy source system that used BIGINT’s for a PK. We switched to a new system that uses varchar as the new PK datatype. It’s still a bigint though. No joins to the source systems. Should I convert the new values to bigint
• 43:09 gabriele: my friend’s dev team is developing the old apps in .net core with codefirst, this means the database structure is binded by the program and therefore my friend can not suggest query changes because the queries are made by .net
• 45:23 Sir Logs-A-Alot: Brent, Nice fanboy shirt… Where do you purchase your funny shirts?
• 46:07 Boutaga: Hi Brent, love your show from the beach. I see in the XML of the compiled plan of a very slow query a parameter data type varchar(20) compiled with a value C followed by 19 white space

Ah, sunset – time to kick back on the balcony, watch the ocean, and discuss SQL Server. You post questions at https://pollgab.com/room/brento/, upvote the ones you’d like to see me answer, and I discuss ’em:

• 00:00 Introductions
• 00:30 iAmGroot: Hello Brent, my friend has a VLDB and he’s asking what is a good strategy to implement in order to archive (offload) old data somewhere else (not on the same database) with minimal impact on the live database uptime; he also says he would avoid ETL jobs like the plague
• 01:42 Mehdi: Hi Brent! The estimated number of rows is equal to the actual number of rows. The server has enough memory. Why does the sort operator spill to disk? It is common in creating the nonclustered index operation. (It is not a query). The actual execution plan is parallel.
• 02:53 LLeopold: Hi Brent, can RCSI affect pure clustered index insert, and what tool (sp_blitz*?) can you recommend for troubleshooting slow inserts? Best regards, Igor
• 03:50 WorthTheTuningEffort?: Hi Brent, my friend wonder where you would put your effort first (from sp_BlitzCache results)? In tuning a query that execute 7K time per minute with average duration of 0 or in a query that execute 3 times per minute with average duration of 200. We have control of the app. Tks!
• 04:48 DefaultDBA: I have a question on how you would handle reducing parameter sniffing issues in Entity Framework. Our legacy system was all Sql Server objects (stored procs, views, etc) and with the new app, management overcorrected, and is using only EF (which is controlled by developers)
• 05:47 Frankie Carbone: Hi Brent, is there much value for the MSSQL DBA in learning Entity Framework?
• 07:07 Mr. Ed: Hi Brent, what are your favorite short-cut keys in SSMS? Do you assign any custom short cut keys? P.S. Please don’t eat me 🙂
• 09:06 Dave: Hi Brent, I recall you talking about naming conventions for indexes in Mastering Index tuning and how you now drop IX_ from the beginning however I can’t find it, any chance you can point me in the direction of the blog you mentioned?
• 10:10 DBA_Mufasa: Hi Brent, Do Microsoft really perform licensing audits? With everything they have going on, what are the odds they will send the “licensing police” to look after broke companies using SQL Developer as their production environment?
• 10:55 Roy: Hi Brent, If I’ve deleted a large amount of rows, will SQL Server stop expanding the database files (they are set to Autogrow currently) and re-use the free space it or is further action required? Thank you
• 11:10 Shamwow: Hi Brent, is there a good way to identify the UPDATE queries that are resulting in lock escalation for a given table?
• 12:10 Booked out SQL DBA: Hi Brent, management is hiring a cybersecurity officer and planning to implement the CIS SQL checklist. Some of those rules are no good. I’m planning to partner with this person and encourage cost vs benefit discussion on each rule. Do you have any advice on this approach?
• 13:45 Eckhart Tolle: Hi Brent, how is meditation going? Can you recommend some resources? PS: Stop fooling around on TikTok and be more in the Now! 1
• 5:10 Victor Von Doom: Üdvözlöm Brent, Is it a bad idea to parameterize SELECT TOP(@N) when used against large (several million row) tables?
• 15:30 The exhaustion is real: Do you have tips on time management? I struggle with balancing time with my kids, wife, work, and me time, in that order. I’m at a point in my life where it feels like I can’t go any further career-wise without sacrificing the things that I value more than work.
• 19:08 Hamid: Hi Brent, what is the most accurate way to calculate space required for tempdb when running checkdb on a multi terabyte database?
• 19:47 CacheBoy: Hi Brent, my friend was wondering what were your thoughts about SqlDependency and Service Broker for managing cache evictions on the application layer? Any recommendations for him for distributed L2 caches for apps (web site & utility apps) using EntityFramework? Thanks.
• 20:39 Tobias Fünke: Hi Brent, is the SQL estimated (not actual) query plan ever useful for anything?
• 21:30 Preben: Hi Brent, can you explain why SQL Server 2022 seem to be mostly focuses on improving GAM/SGAM updates? Do you think this is the true bottleneck they need to fix or do you have a different opinion?
• 21:50 Ricardo: Ola Brent, are parallelism deadlocks diagnosed / resolved the same as regular deadlocks? Love your sp_blitzlock.
• 23:06 Trushit: Do you have a recommendation for a T-SQL style guide?
• 23:22 Martin: Hi Brent, what’s the impact on performance of different isolation levels? Love your new beach episodes 🙂
• 24:05 Felix: Hi Brent, do you have any book recommendations for learning about all of the possible operators in a SQL query plan?
• 25:01 Jasmine: Hi Brent, will you be dressing as Disney’s Aladdin for SQL Bits?

Ask me questions at https://pollgab.com/room/brento and upvote the ones you’d like to see covered. I can pretty well guarantee that your favorite question this time is going to be the last one I answered.

Here’s what we discussed today:

• 00:00 Introductions
• 00:57 Sean W: New app with data model TBD; what is the thought process one should go thru and questions to be asked to determine if relational or nonrelational DB should be used? Thanks!!
• 02:42 MyFriedAsk: Hi Brent, My friend server has 1 database and getting and several applications connecting to that database. For some reason they get sql timeouts in the applications on Tuesdays. How can we figure out what causing SQL timeouts?
• 03:20 Goose: What is the danger zone rate for inserts per second in SQL Server? What’s the highest you have seen?
• 04:54 i_use_lowercase_for_select: Hi Brent, my friend is looking at your alerting script and wants to implement it. However they also want their system (SSMS/PRTG) monitoring system to get notified using SNMP traps. Do you have any advise on this? PS looking forward to the upcoming live sessions!
• 05:51 Sohil: Do physical reads vs logical reads ever matter for performance tuning?
• 07:12 Robert S.: Hi Brent, do you have a Goldilocks methodology for determining the optimal number of inserts to perform in a transaction so that transaction log writes are optimized (i.e. not too few, not too many).
• 08:23 RenegadeLarsen: What is best Brent…CPUs, Memory or Fast disks.
• 09:00 Kendra Little: What is your favorite isolation level, and why is it NOLOCK?
• 09:31 Sqlsucks: Hello Brent, In today’s world where things are changing so fast, what would you recommend to learn new to someone who has 5-6 years of experience with SQL server? I would assume cloud is the future, but please explain where should I focus? Thanks
• 12:17 Greg Dodd: The leading column of an index is very selective (only around 5 rows for over 95% of the rows) but the stats in the histogram shows 100’s or 1000’s of rows expected. If I rebuild stats, it gets a lot better if I do a fullscan. Thoughts on how to make it better without fullscan?
• 14:51 Leroy Jenkins: Hi Brent, what’s your opinion on some peep’s advise of “Do not install SSMS on the SQL Server / Do not remote desktop into the SQL Server” ?
• 16:54 Megurine Luka: Hi Brent, I can run the same query in SSMS 18.x and Sentry One Plan explorer. Sentry one will show residual io bang – warnings that I don’t see in SSMS. Have you experienced this as well?
• 18:32 ProductionDBA: Hi Brent, I’m very exited for the new SQL2022. I wanted to test it but I’m still new to the SQL world. I was wondering have you ever participated in an early adoption program of any of the old versions and what is expected to do and to test, what feedback you need to provide.
• 20:59 VISHAKHAPATNAM: Hi Brent, what options should be used to create a new index in the fastest time possible? Use SORT_IN_TEMPDB?
• 21:58 marcus-the-german: Hi Brent, could you tell us anything about the persisted log buffer or Non-Volatile Memory SQL Server Tail Of Log Caching on NVDIMM? Is it worth thinking about?
• 24:30 LifelessDBA: Hi Brent, what would be the reason(s) behind creation of statistics on a column in read-only secondary database, while primary database already has a statistics on that very same column?
• 25:50 Sean C: My (dumb) friend inherited an Azure instance with auto-create idx enabled/drop disabled. The sys created idx have non-standard names and the idx keys,etc are different in each DB. He’s can’t think of a way to implement DEATH. Any thoughts on how to manage/script this out? Thanks!
• 28:50 Maurice Moss: Do you see same or different reoccurring issues in Azure SQL that you see in self managed / installed SQL Server?
• 31:38 del-a-nooch: Hi Brent. Long time listener, first time caller. My backup admins are asking if they can exclude DB data files from the daily server backups. I’m taking regular SQL backups (to two different data centres), can you foresee any problems with this approach? Thanks, del-a-nooch.
• 33:40 Kendra Little: Which breed of dog is SQL Server?

Got SQL Server questions? Post ’em at https://pollgab.com/room/brento and upvote the questions you’d like to see me discuss.

Here’s what we covered this morning:

• 00:00 Introductions
• 00:45 SQL Simian: Everybody loves free advice, but we want war stories. I have heard your 174 indexes story twice now during recorded classes. Got any more? What are the weirdest things you have seen implemented? OR: What are the most exotic places you have visited when you ‘parachute in’?
• 02:49 CaveOfWonders: MS SQL Synonyms are a new concept to my friend and I (insert Brent’s “A Whole New World” line). We were wondering what your thoughts are on synonyms and if you had any pointers to keep in mind when writing or tuning queries that use them.
• 04:27 Andrew: For some of our actual query plans in SSMS, the sum of the node costs add up to over 100% (116% in one case). Is this a bug in SQL Server or SSMS? Running SSMS 18.10, SQL Server 2014 SP2
• 05:40 Rick: Brent, what is your experience with letting the SAN compress large (multi TB) SQL backups instead of doing the compression with SQL server?
• 07:58 SwedeDBA: Hi! Our application has one database per customer. Currently, we run about 20-30 customers per instance, and three instances per physical server. Would running all this on one instance per server be more beneficial instead, to allow the server to balance resources better?
• 08:58 Mahir: Brent, besides price, what criteria should we use when evaluating SQL monitoring software?
• 09:39 Gabriele: If my friend had to monitor 500 remote clients postgres and 500 remote sql server what tool would you recommend?
• 13:17 Calvin: Hi Brent, when should should you disable a non-clustered index vs drop it like it’s hot? Does disabling free up any space?
• 14:55 Neil: My company runs 15 merges every minute to sync an online store with the ERP database. What’s the alternative? Can SSIS be used for this? What is SSIS? What is love?
• 16:38 Drew: SQL is randomly throwing 824 corruption errors on tempDB on a brand new SAN. Vendor says there is no issue with the SAN, what are our next steps?
• 18:21 Mihir: Hi Brent, We are running into an unusual tempdb contentions in multiple environments where despite having tempdb files equal to number of logical cores (28 in our case) we still get contentions on PFS and GAM page. What else can we do to resolve this?
• 20:05 Jim: What should we configure MAXDOP to? Our 2019 physical server has 64 total cores including hyperthreaded cores.
• 20:50 Count Chocula: Hi Brent, is there an easy way to clear the server’s index stats (used by sp_blitzindex) without rebooting the server? Don’t want to failover after making a bunch of index changes just to get updated index stats.
• 22:14 Neil: Should we get a bigger, faster drive on Azure VM, or build a software raid in Windows with a bunch of smaller, slower ones ? Is there a way to trial azure hardware to test this? We tried the big drive with data, log, tempdb together and it was fast, havent tested raid
• 23:21 Chris Thomas: Are there levels of Enterprise licensing for a VM Guest vs VM Host? We currently only license our VM, but have sufficient licenses to cover the host (we’re the only VM on the host). I’d like to try HT, but our Infra Manager says that we would need a different license to do this
• 24:01 Margaret: Hi Brent — We have some Always on clusters with readonly secondaries SQL Server 2016. We run an index reorg daily and quite often that causes a lag in the secondary. Would changing the synchronization to async while the reorg runs, then changing it back help with this?
• 26:00 Recap and plugging

Got questions about SQL Server? Post them at https://pollgab.com/room/brento and upvote the ones you’d like to see me cover. This episode was the first live one I shot in my home studio in Cabo:

Here’s what we covered:

• 00:00 Introductions
• 02:19 Nullpointer: What are your thoughts on temporal tables, I don’t hear you talk much about them pros/cons.
• 03:24 Trushit: If my query is returning returns quickly enough but my estimates vs actuals are more than 10x a part, do you think I should tune my query further even if the speed is acceptable?
• 04:09 Burhan: Is there a good way to estimate how much a given transaction will impact the transaction log ahead of execution time? Concerned about filling up the transaction log with some DDL commands on a table with millions of rows.
• 05:03 Roy Hinkley: What are the top issues your clients run into with SQL full text search?
• 06:14 Mehdi: Hi Brent! What is a reliable way to transfer new records to another destination? It is insert-only and identity column should be OK.But if multiple sessions are inserting data to the table, we may have race condition and skipped records depending on how they commit.
• 06:42 Hazan: Brent, what are the top multi-tenant / SQL related issues you see on a regular basis?
• 08:23 Carl: Does the advice of always adding at least two data files to every SQL file group (for performance reasons) hold any water when those files are located on a NVME SSD SAN drive?
• 08:55 Sultan: Hi Brent, how long do you wait after a SQL CU is released before you feel comfortable installing it?
• 10:00 Tolga: Hi Brent, love your training classes / videos. Do you have any plans to create any AzureSQL training?
• 11:38 Nadim: Besides compat version, what other settings should we consider updating when migrating from older to newer version of SQL server?
• 13:03 Tahir: Hi Brnet, our DB’s currently have recovery interval of 0. Microsoft docs recommend keeping this value at 0 unless you experience problems. Have you ever experienced a problem with recovery interval of 0?
• 13:44 Eran Haddad: We use sql server 2014 compatibility level and soon to upgrade to 2019. How can I convince my managers to upgrade the compatibility level without warning? Beside restoring to another db and make tests (it’s pretty hard to do)
• 15:45 Leonard: What is your opinion of SQL Buffer pool extension? Has it ever gotten you over the performance finish line?
• 17:26 Rahmi: Hi Brent, how do you know when it’s time to move a high write table to a new less busy filegroup / SAN volume? Will sp_blitzindex help here?
• 18:23 Hadir: Hi Brent, Is SELECT TOP(N) without an ORDER BY bad for any particular reason?
• 19:16 sol: Praised be the acclaimed tortugas savior! What drives you to answer the same questions over and over again during OH without smashing your iPad against a wall? Jokes aside, do you become hyped / addicted to this type of community interactions during now-all-worldly “trends”? Thx!
• 20:33 Hasan: Hi Brent, is SQL availability groups any faster or more efficient for replicating large amounts of transaction log activity compared to SQL log shipping?
• 22:12 a friend of mine: is saying that certification is worth nothing. What do you think about payed trainings? Are they always worth money spent?
• 23:51 Rico Suave: Hi Brent, love your classes. How often do you update or re-shoot previous recorded class videos?
• 24:35 Jonas Grumby: Is Query cost or logical reads a better identifier of expensive queries?

I had the pleasure of sitting down with David Giard virtually and discussing what developers need to know about databases. I always have fun talking with David – he’s a great follow on Twitter – and I think it shows in this conversation:

Enjoy!