I hang out by Hallgrímskirkja, the modern church in Reykjavik, and take your highly-voted questions from https://pollgab.com/room/brento. Here’s what we covered:

• 00:00 Introductions
• 00:18 __normally_weird (Sean C): Can you dispel the now prevailing myth that you and your wife are the only8 people in Iceland at the moment?
• 01:33 Andy Leonard: What “bugs” exist in SQL Server 2019?
• 04:22 Michael Devor: You have said you want to retire (relatively soon). How have you gone about preparing to support yourself and your wife for your retirement.
• 06:03 Kevin M: Have you encountered sp_BlitzCache, QueryStore, and SQL Profiler showing a query returning a large rowcount, high CPU time, and large read count, but running the same query from SSMS shows less rows, reads and less CPU?
• 07:56 Philip Clark: Can I Just say, you’re awesome Brent. Hope I get to meet you in person one day!
• 09:24 Mike Byrd: Possibly a dumb question, but is it possible to redirect output from SET STATISTICS IO ON to a file or table?
• 11:35 Random Name: A certain security software vendor is pretty adamant that the application should be installed on the same VM as the SQL Server because “there could be a lot of traffic.” Any guiding principles for when (if ever) to have the full application installed onto the database server?
• 13:15 Martin: Hi Brent, I know that using MERGE is advised against. Are there any other operators that are problematic?
• 15:10 Philip Clark: What do you recommend these days in terms of what processor (Intel/AMD) with clock speed and number of cores to choose? Minimum to start with for an Enterprise 2019 dedicated server? And how would this differ when specing an Azure VM for SQL?
• 18:06 Michael Devor: Can you have too few indexes and could that be my friends issue?
• 19:32 Seven: I asked a question earlier about stats fullscan; maintaining the integrity of full scan during auto updates. i guess what i meant was if enough rows have been modified to trigger an auto stats – if it could “append” a fullscan rather than replace it, or ignore it (no recompute)
• 20:40 CacheTellMeTheTruth: How do you find which COLUMN stats sql server used to create an execution plan? I’m trying to find a list la col stats “potentially” useless (double quotes are mandatory here )
• 23:11 Daniel Moll: Hi Brent. We are monitoring sys.dm_os_sys_memory and did not have any warnings since I activated “lock pages in memory”. now we have several events (high=0) a day. (Why) is this a problem?
• 25:38 Juan Falcon: Is there and automatic way to update OpenSSL which SQL Server installs with Python Services?
• 25:54 Igor: Hi Brent, what is the best way to reduce table size, after drop column?
• 27:00 __normally_weird (Sean C): I have an interest in contributing to the community to “pay it forward” as well as selfishly gain experience to meet a long term goal of becoming a performance tuning consultant. How can I, as a mere mortal, positively impact the community beyond answering stack questions?
• 29:04 Aaron: I have several queries that generate plans that have an Index Seek + Lookup for their respective tables. What are some key things to take into consideration?
• 30:16 marcus-the-german: Hi Brent, a friend of mine would like to know how she can determine that a parallel executed query is executed on one NUMA node? Let’s assume we have a 2 x 8 core server and MAXDOP is set to 8
• 31:24 Artur: I’m doing the D.E.A.T.H. method. Is it a good idea to test any index changes in non prod environment before I apply them in prod environment? 32:28 Artur: Have you ever used Distributed Replay?
• 34:11 Mike B: I’m testing a solution using triggers to compress LOB data on insert with the COMPRESS function with a view containing the DECOMPRESS function. Thinking to trick the app by replacing the table with a view and avoid changing code. Seems to work, but any gotcha’s to look out for?
• 35:37 Andrew from Canada: Synching a recent stack users table to an older stack DB to do some tests. It takes a long time and the system appears underutilized. why? by design?
• 37:30 qTechnik: How to monitor cpu load by each database on sql server? Don’t like statistics from plan cache because different lifetime for each plan in cache.
• 39:24 marcus-the-german: Hi Brent, do you have any experience/recommendation on receive side scaling (RSS) on SQL Servers?
• 40:01 David Nelson: What are the options for using a source control system for versioning of stored procedures (like git, svn, etc)
• 40:33 Steven: Hi Brent, thank you for the anwser on “why does a join spill to tempdb”. The parallalism is nearly evenly distrubted, so the operator was not allocated enough memory. Is it possible to allocate more memory to a specific operator in a query or do I have to use min_grant_percent?
• 41:16 AlwaysLearningDBA: what do you suggest to transition existing career as production DBA into Architect or Data Science role?
• 42:20 Kyaw Than: The app admin wants to set system DBs to compat mode 2016. I could test it out but would appreciate your opinion.

Before Fjöruborðið opens for lunch, I answer a few of your questions from https://pollgab.com/room/brento including:

• 00:00 Introductions
• 00:29 Peter: What is your opinion about the SQL generated by Entity Framework?
• 02:00 Peter: When would you switch from EF to stored procedures?
• 02:45 Always Learning DBA: What are free resources to help me transition into an architect or data scientist role?
• 04:43 How is the auditing and monitoring different for Azure SQL DB?
• 06:33 Recap

I sit on the porch and discuss your highly voted questions from https://pollgab.com/room/brento.

Today’s discussion includes:

• 00:00 Introductions
• 00:27 DK: What was your Gordian Knot problem?
• 03:25 George: How exactly does locking work on INSERT INTO…SELECT FROM?
• 05:48 Michael: Can I tell if hyperthreading is turned on or off?
• 07:25 Party People: What are the most common anti-patterns you see?
• 10:20 Recap

You upvoted questions at https://pollgab.com/room/brento, and today I’m at the harbor in Heimaey to cover ’em.

Questions we talked through:

• 00:00 Introductions
• 01:00 Zach: What happens when I change a VARCHAR(MAX) to VARCHAR(2500)?
• 04:26 Scott: What tools do you recommend for tracking down locks involved in blocking?
• 07:23 Pointing out the beluga whale sanctuary
• 07:58 Ice Horse Rider: Why aren’t you a Microsoft MVP?
• 10:20 Kirby: What does changing MAXDOP do?
• 12:44 Mr SQL Seeks: Should I upgrade to 2017 or 2019? What compat level do I use?
• 14:19 Farooq: How do I get notified when SQL Server needs additional CPUs?
• 15:45 Recap

,

You posted and upvoted questions at https://pollgab.com/room/brento, and I ascended – well, drove into – the Eldfell Volcano in the Westman Islands to answer ’em:

Questions we covered in the video:

• 00:00 Introductions
• 01:55 Do you notice big improvements for physical servers over virtual servers?
• 05:14 Why aren’t my SQL Servers using all of the available RAM?
• 07:16 Do you recommend periodic SQL Server restarts?
• 09:36 How many times have you encountered a SQL Server in perfect shape that needs no work?
• 11:55 Should I use Extended Events or Profiler? I find the XE interface dreadful.
• 14:11 Why do so many people keep trying to ask questions in YouTube chat rather than PollGab?
• 15:30 Is there a way to influence which keys SQL Server uses as the range high key in statistics?
• 17:56 Why does a hash match join spill to TempDB when the estimates are accurate and the used memory is lower than granted?
• 20:20 What SQL Server feature do you miss the most when working in Postgres?
• 20:32 What Posgres feature would you love to see in SQL Server?
• 22:30 My query execution plan takes 3-5 minutes, but transferring millions of rows to SSMS on my laptop takes more time. How do I make it go faster?
• 24:09 When I’m using log shipping, can I still do separate full and log backups?
• 25:05 Can an index with the same name as a column cause problems?
• 25:21 Should I use backup devices rather than files?
• 26:24 Do you have kids?
• 27:17 How do I deal with a manager who insists on choosing technologies that IT should use?
• 30:55 How much training time should I let my direct reports do?
• 32:24 How should we pass client information to SQL for the session?
• 33:40 Have you ever told someone to turn on trace flag 4199?
• 35:00 Recap

I sat down at the EVE Online Monument in Reykjavik to answer the highly-voted questions you posted here.

Questions covered in the video:

• 00:00 The EVE Online Monument
• 00:48 Why is SQL Server using so much memory?
• 02:30 Will table compression make a 2GB database faster?
• 03:28 Will you be doing Office Hours long term?
• 04:39 Should I upgrade SQL Server?
• 06:00 Should I store JSON in SQL Server for a new application?
• 07:40 What’s the best way to speed up page loads?
• 09:33 What’s your favorite ETL tool?
• 11:20 What are your thoughts on Oracle, DB2, MySQL, and Postgres?
• 12:12 How big of a server should I use for a 100GB database?
• 14:30 Will In-Memory OLTP help me on a 1GB database?
• 15:30 What’s a good alternative to NOLOCK?
• 16:35 How do I explain the legacy CE and backwards compat to a non-DBA?
• 17:20 Should I change my inherited CHAR columns to VARCHAR?
• 18:43 Alberto, Office Hours probably isn’t a good fit – you need consulting or training.
• 19:20 Should I run antivirus on my SQL Servers?
• 20:59 Why should I move from SQL Server 2016 to 2017?
• 22:16 Since a backup file is bigger during busier times, should I back up when the database is idle?
• 23:19 Who are your references in your field?
• 25:06 Where you start when analyzing someone else’s queries if you don’t know their tables?
• 26:15 Is there a module to write T-SQL in PowerShell? Do you teach your Mastering classes with PowerShell?
• 28:38 Wrap-up

In this episode, I’ve got my own question: what’s this animal running around at my feet? Commenters on the YouTube video explained that it’s a mink. They were imported into Iceland in the 1930s for their fur, and they escaped out into the wild. The country’s been trying to reduce their population, and they’ve even been part of the COVID19 news.

Anyhoo, back to databases. The way these videos work is that you post your questions here, upvote the ones you’d like to see me talk through, and I do your bidding:

Questions we discussed in this episode:

• 00:00 How can I fix a slow query with 30 left outer joins?
• 03:20 How do I track down unparameterized queries?
• 04:10 Can I force parameterization without using Optimize for Ad-Hoc?
• 04:54 How can I measure latency of sync Always On Availability Groups?
• 07:17 Should my monitoring software point at my AG listener or the replicas?
• 08:50 What index reorganize/rebuild thresholds do you recommend?
• 11:36 Why does Query Store show 5-6 plans for the same query?
• 13:10 My SQL Server goes down on its own. How can I find out why?
• 14:58 (I’m visited by a cruise ship and an unidentified animal)
• 16:37 My databases are 1-3TB. What should I warn managers about?
• 18:15 When I’m doing the DEATH Method, how should I treat unique indexes?
• 19:46 How do I save sp_BlitzCache’s results to a table?
• 20:22 sp_Blitz warns me that I have a high number of plans created recently.
• 21:55 Is there a simple way to do inserts faster?
• 23:27 Why should we stay on-premises instead of move to the cloud?
• 25:30 If queries always use ORDER BY, can changing indexes change the query results?
• 26:39 (Moving the camera to show the animal)
• 27:40 Should we interview candidates by making them write queries in Notepad?
• 31:12 Wrapping it up

You’ve got questions, I’ve got answers. Welcome to another edition of Office Hours, where you post your questions here, upvote the ones you’d like to see me talk through, and I do your bidding:

Questions we covered this week:

• 00:00 Introductions
• 01:30 Are DBAs transitioning to other roles, and why?
• 04:48 Why is it hard for DBAs with 35 years of experience to get clients?
• 06:40 How can I track down slowness in the app if it isn’t SQL Server?
• 10:17 I’m a backend/frontend/mobile developer. Am I spreading myself too thin?
• 12:00 Are users with multiple languages a problem for the plan cache?
• 13:54 My query errors out in batch mode, but works in row mode. Why?
• 14:48 What’s the oldest SSMS bug that causes you the most frustration?
• 16:14 When would you recommend not to use SQL Server?
• 18:29 What was your favorite thing in Iceland?
• 21:00 Does lock escalation work the same way in Azure SQL DB?
• 24:04 Why isn’t my query going parallel?
• 26:43 We revisit fragmentation for the 1,043rd time
• 28:12 Why is my database stuck in restoring state?
• 30:05 What certifications will grow my career?
• 33:49 Do you like SQL Server 2019’s automatic index tuning?
• 34:51 My new coworkers love query hints. What should I do?
• 36:50 What should DBAs document?
• 37:24 What is your favorite data auditing method?
• 41:00 When should the primary keys not be the clustered index?
• 42:00 How much C#/Java does a DBA need?
• 43:06 What’s the best way to delete 90% of my data?
• 44:05 Can I turn an execution plan back into T-SQL?
• 45:45 How should I do real time updates to keep two servers in sync?
• 46:46 Are you going to teach Power BI?
• 48:35 Will Azure SQL DB replace SQL Server?
• 51:17 Is the SQL job market good, and what will it look like in 5 years?
• 53:10 Is it safe to use Accelerated Database Recovery?
• 56:09 Am I crazy to drop foreign keys to make my database faster?
• 57:43 Can I automate scripting out a database schema without tools?
• 58:52 Recap

Got questions that aren’t a good fit for DBA.StackExchange.com, and you want my opinion? Post them here and upvote the questions you’d like to see me talk through. This weekend, I hopped into my home studio and did a half-hour session because so many good questions had piled up:

Here are the questions I answered in this session:

• 00:00 Introductions
• 01:39 Why is connecting to SQL Server sometimes slow?
• 03:52 How do I know when my indexes are hurting me?
• 05:54 Should I enable Optimize for Ad-Hoc Workloads?
• 10:15 What do you think about running SQL Server in Kubernetes?
• 13:50 Can I restore everything except one table?
• 18:15 How can I monitor 1,000 SQL Server instances?
• 24:04 How can I explain that performance tuning never ends?
• 25:58 (I rant about how much I hate tech support)
• 27:15 How do I write a DBA resume when I have 8 years experience?
• 30:28 Any alternatives for SSMS on Mac OS?
• 32:52 What if I want to jump from a DBA role to project management?
• 34:32 If users query nested views, can I see the resulting T-SQL?
• 35:39 Should I dynamically craft update statements per column?
• 38:04 Wrap-up

Wanna join a live session? Subscribe to my free YouTube channel and you’ll get notifications whenever I go live. See you there!

We took a break from roaming around Iceland long enough to check in at our home studio. While we were here, I did a live Office Hours session, taking your questions about Microsoft SQL Server.

Questions I answered in the video:

• 00:00 Introductions
• 02:35 How can I tell if my SQL Server has too much memory?
• 05:20 Does normalization have a big performance impact?
• 08:08 What performance metrics should we show to the business?
• 11:17 Are SQL Server’s index recommendations any good?
• 12:37 Thank you for all you do for the community.
• 13:24 Are GUIDs a good clustering key for high concurrency?
• 15:54 Should I stack multiple SQL instances on the same physical server?
• 17:41 Do foreign keys help with performance?
• 21:05 Is there a benefit to having an identity column?
• 22:25 Why is my query blocking itself?
• 25:29 Should I separate my database into multiple databases?
• 27:46 What’s the best data type for primary keys?
• 28:13 Should I enable RCSI locally since I use Azure SQL DB for production?
• 29:32 Is my SQL Server slow because of my network?
• 32:40 How should we move documents out of the database?
• 33:30 Which query should I tune first?
• 35:50 Is SQL Server 2019’s UTF8 support a game changer?
• 38:13 Which query filled up my TempDB?
• 40:35 Will there be a SQL Server 2021?
• 43:35 What’s the best way to implement version control?
• 44:23 What performance change yields the best bang for the money?
• 45:49 Is 1,000 logical reads a big deal?
• 46:58 What security role lets people administer SQL Agent jobs?
• 48:35 How can I determine what permissions an application needs?
• 50:35 How should I configure quorum for my cluster?

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.

Today’s session comes to you from the harbor in Húsavík, the whale watching capitol of Europe.

Questions I answered in the video:

• 00:00 Introductions
• 00:29 Is SQL Server 2019 ready for prime time now?
• 02:47 Does WITH NOLOCK cause high CPU usage?
• 04:53 Is MERGE a good practice or bad?
• 06:36 OPENROWSET is so easy – why shouldn’t I use it?
• 07:52 How can you tell if a server has enough CPUs?
• 11:47 Discussing cargo cult programming
• 15:25 Wrap-up

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.

Erika and I are on the road in Iceland, roaming around the countryside, and I’m taking you with us. We visited Grímsey, a tiny island on the Arctic Circle, home to gazillions of sea birds like puffins and arctic terns. I took about half an hour to answer your most highly-voted questions from here:

Questions covered in the video:

• 00:00 Introductions
• 01:43 Should developers create indexes for their queries?
• 03:28 Do you recommend monitoring for resource-intensive queries?
• 04:40 How should I set max server memory?
• 07:30 How much free space in buffer pages is acceptable?
• 09:42 How can I fix implicit conversion in a 3rd party app?
• 11:55 How do you organize tasks for productivity?
• 14:00 How do you manage your mental state for productivity?
• 16:02 Why might too many indexes slow down selects?
• 18:00 What’s better – queries in the app or in stored procedures?
• 22:06 How do I prioritize DBA tasks?
• 23:38 How do I find which queries are winning the deadlocks?
• 24:35 How do I find out what my memory is being used for?
• 26:10 Wrap-up
• 27:40 Walking you over to the puffins

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.

Erika and I are on the road in Iceland, roaming around the countryside, and I’m taking you with us. This session was shot in Siglufjörður, a beautiful little oceanfront fishing village in the north.

Yes, I’m on the airport runway. No, seriously. Here’s what it looks like from the air in the winter.

Questions covered in the video:

• 00:00 Introductions
• 00:45 What are the best practices for linked server security?
• 04:20 Have you ever told a customer to change their collation?
• 06:28 What compatibility level do you recommend?
• 09:06 Should parallelism be 70% of my waits?
• 11:36 Should I append GUIDs to my temp table names?
• 13:30 Why is there a car hood in the video?
• 14:25 Why are my TempDB write stalls slower than my user databases?
• 18:34 Should I send an email for each failed login?
• 19:43 Has your opinion of table-valued user-defined functions changed?
• 20:36 How do I measure the overhead of Transparent Database Encryption?
• 21:46 Can highly fragmented indexes cause PAGEIOLATCH waits?
• 22:30 How do I track down which query is using a specific index?
• 24:45 Wrap-up

If you’ve got something you’d like to see me cover, ask your questions here, and while you’re at it, upvote other questions that you’d like to see covered. I’ll take the highest-upvoted questions and talk through ’em in an upcoming webcast.