Product has been added to your cart.

The PASS Summit Lineup Is Out and Registration is Open!

Brent Ozar
#SQLPass
5 Comments

This year’s PASS Data Community Summit is November 13-17 in Seattle. (No online version is available this year – you gotta be there in person.)

If you’ve attended in person before, you already know the value: it’s like a family reunion for the Microsoft data community. It’s your chance to see the authors you’ve read so many times, hear their latest lessons, and network with people who can help you the next time your company has hard times. Go register.

If you’ve never attended before, here’s my sales pitch:

  • The pre-conference workshops on Mon/Tues are $595 each, and that’s a steal for a full day of in-depth training on topics that you just won’t find locally.
  • The full 3-day pass is $1,995, and the sessions are organized into learning pathways so you can quickly figure out if there are topics that are a good fit for you
  • Pick your pre-cons and your learning pathway, write them down, and then show your manager, “Here’s what I’m going to learn for $3,185.”

Send that to your manager and see what comes back. Then, the negotiation starts. Here are techniques I’ve heard folks use in order to get their companies to pay for some, if not all, of the conference, hotel, travel, and meal expenses:

  • “I understand that we don’t have a travel budget, so how about the company buys the conference ticket out of the training budget, and I’ll pay for my own travel?”
  • “I understand we only have a limited training & travel budget for the whole team. How about I go, and when I return, every Friday for the next 2 months, I’ll teach a lunch & learn for the rest of the staff, covering one of the sessions I attended?”
  • “I’m going to attend whether the company pays for it or not, and I’m going to take vacation to attend. If it’s important to you that I’m reachable during that time, you can pay for the conference ticket and travel, and I won’t take vacation, and I’ll be reachable via email while I attend the sessions.”
  • “I understand we don’t have any budget, so how about I pay for it, but you don’t make me take vacation time while I attend, and I won’t be reachable?”

I know you might be reading that and going, “There’s no way my company would go for that.”

But they do – it’s just a matter of finding the right combination that works for you and them. Go make it happen, because you need to start making connections and gaining knowledge that will help your career.

(Unfortunately, I won’t be there – Summit is the same week as the Las Vegas F1 night race. I’m super excited to see F1 cars racing down the Strip!)

[Video] Office Hours: Stump Me On SQL Server

Brent Ozar
Videos
0

I went through the top-voted questions from https://pollgab.com/room/brento and discussed ’em live on my Twitch channel.

 

Here’s what we covered:

  • 00:00 Start
  • 00:39 tanchenglai: Hi Brent! We bought your Level 2 Bundle last year. Recently, we went into frequent thread pool issues; we suspect it is due to high CPU utilization. Our IT manager advised us to delete old data (keep only 3 – 6 months). Is deleting data the best solution for performance issues?
  • 01:32 Kulstad: I have been given the “glorious” task of upgrading a production SQL server from 2005 Standard Edition to 2019 Standard Edition. Can you recommend and tips, tricks, and “gotchas” for such an upgrade?
  • 03:24 chandwich: What are the most indexes you have ever seen on a single table? Was it too many? What kind of hardware requirements would be necessary to ensure that tables indexes are all usable?
  • 05:00 MS Excel: Hi Brent, recently we got request to migrate on-premise database to AWS RDS instance. Database is about 300GB in size. What would be your approach to do it with minimal downtime (except CDC data replication)?
  • 06:09 Maksim Bondarenko: Hi Brent. Is it possible to get CDC database/database table changes on some other server instance?
  • 06:46 Xavier R: Hi Brent, what is in your opinion a good average query execution time for rapports or application performance in small/mid/large/very large database?
  • 08:00 Xavier R: Hi brent, i have 4 years of experience with MSSQL. How easy or hard is it to transition to a job that uses DB2?
  • 09:28 Clippy: What are your thoughts on copilot integration with office 365 for 30 dollars per user?
  • 13:09 Alex M: Considering that you visit some nice, high-end restaurants, do you also have an interest in cooking at home? If so, Any suggestions for favorite recipe sites?
  • 15:41 SelectStar: A qry that joins 2 tables, intermittently (once a wk more/less) fails w/ error converting varchar to int. It gets fixed after dropping/re-adding any index from one of the 2 tables. When it fails, it has a diff exec plan. It’s a vendor qry, that uses deprec join. What would u do?
  • 16:26 Balraj Sahni: Is high batch requests per second ever a concern on it’s own or does it have to be simultaneously high along with another wait stat to be of concern?
  • 17:59 Luca: What is the most efficient way to capture SQL Execution timeouts to help troubleshooting?
  • 20:19 Miles: Hi Brent, We see tempdb size growing up to 5TB. Version store size is 4TB..RCSI is turned on few dbs. What does version store that big means to you and how can we reduce the size of version store? How to capture those offending queries take up space in tempdb and tune them?
  • 21:10 Ben: I am struggling to find best way to query Nested JSON – when there are 4 cross apply the estimated rows are 312milion, and the actual is only 5 rows. If I place SQL Function to manipulate the data SQL give 3G ram for the query .Would you recommend using CLR instead of SQL?
  • 21:43 Jessica: Hey Brent, the last time you discussed hosting for pollgab was March 2022. Have you considered an update to that post in 2023 to show how hosting costs have changed month to month vs usage since then?
  • 22:27 Chat GPL: Hi Brent, a friend of mine struggles with tuning many small queries, about 200 quite simple stored procedures, called sequentially for a total of 1M times. Trivial plans, no blocking, no missing indexes, same execution time. How would you start when there is no low hanging fruit?
  • 23:24 Augustus: What spoken language translation tools do you like to use for work and vacation?
  • 24:23 Sultan: Any recommendations for how to keep logins and SQL agent jobs in sync across AG replicas?
  • 25:31 Dimitris Horn: Do you ever see issues related to SQL Connection Pooling with your clients?
  • 26:43 Mike in Dublin, IRL: Hi Brent. If you query a non-clustered index on a heap table and data from a column not contained in the index is required, does the query have to scan the heap table for the row ID or can it perform a seek to the row ID?
  • 27:22 Doesnt Buy Anything: Hi Brent a friend is looking for advice. Would you advice an ‘in place’ upgrade of the Windows Server OS from 2012 to 2019 for a SQL Server 2017?
  • 28:50 Benji: Will we ever see a mastering tempdb class?
  • 29:09 SQLChess: Hi Brent. A friend of mine started a SQL blog but is struggling to find new interesting topics to post. What is your methodology for new blog posts? Thanks!
  • 31:10 Jessica: Hey Brent, What sort of tests can I use outside of complicated load replay systems to test how much overhead a new indexed view would cause? Is the put it in production and see if anyone screams viable?

[Video] Office Hours: Extended Answers Edition

Brent Ozar
Videos
0

Y’all posted so many good questions at https://pollgab.com/room/brento that I went longer than usual this time:

Here’s what we covered in this episode:

  • 00:00 Start
  • 02:17 Tanchenglai: Hi Brent, recently we always ran into thread pool issue. I checked Performance Dashboard and found that CPU spiked over 80% occasionally. I suspect apps can’t connect after CPU over 80%.
  • 03:55 Janis : I’m facing a puzzling issue with a simple join query, warns excessive 1/2 GB memory grant when joining the final table. I wonder where to dig deeper for a solution.
  • 05:02 Novice Consultant: Hey Brent! How do you handle those clients who said they don’t know the how-to when you suggest some non-SQL Server solution
  • 08:03 Eli: Hi Brent, hope you’re well. In your training, you make use of CTEs, but not correlated subqueries. Reusability aside, are there other reasons to prefer CTEs over correlated subqueries? Also, does subquery optimization behave differently if nested deeply?
  • 08:53 Sonakshi Sinha: Is it beneficial to periodically delete the auto generated stats and let SQL re-generate them in hopes of culling the stats that are no longer used?
  • 09:44 FroztDbz: Should an sql server service account password have expiry or can it be added on exemption list. Also how should I justify to them that this should be added to exemption list since it will create unnecessary downtime when not tracked properly
  • 10:28 Dimez: In DBD do you play killer more or survivor more and who do you main as killer/survivor?
  • 11:45 i_write_tsql_in_lower_case: Hi Brent, with this writing SQL Server 2022 has 6 monthly CUs. What’s wrong with SQL Server 2022?
  • 12:23 Chris: Have you ever used any tools that can assist in comparing execution plans that is better than the built in one for SSMS?
  • 13:29 DBA with Anxiety: Hi Brent!. Thanks For Response My Last Question, Appreciate it very much. How to overcome FOMO in the world of databases
  • 15:19 Dansa: We have a newly created table with a daily-partition aligned clustered column-store index. The partition function has 4800 partitions. Attempting to drop the empty table did not succeed after 5 hours.
  • 16:36 Aamir Khan: What is your opinion of Microsoft SQL Server slow performance troubleshooting documentation?
  • 19:01 Vangelis Protopappas: What are your pros/cons of MSTeams vs Slack?
  • 21:06 Vishnu: What tools (external to source control) do you like to use when comparing two TSQL scripts for differences?
  • 21:29 Dimitris Horn: We have many power users that can setup new / modify existing SQL agent jobs whenever they feel like it. Do you have any tips for how to track all the SQL Agent Job changes?
  • 24:50 Sonakshi Sinha: What are your thoughts about using schemas to segregate multi tenant tables within a single database (MyDB.Co1.Sales, MyDB.Co2.Sales, etc)? Is this hard on plan cache / query store?
  • 25:35 Eli: Hi Brent – heard you got a new gaming PC! If you ever get tired of Dead by Daylight, have you ever considered Deep Rock Galactic?
  • 26:50 Double Bad Ass: What is your favorite cloud data warehouse and why?
  • 27:37 gserdijn: Hello Brent, I noticed something I’ve never seen before. Some databases had OLDEST_PAGE as Log Reuse Wait Description. What does that mean?
  • 28:33 Eduardo: Prior to SQL 2022, which SQL version was the previous poorly adoption version?
  • 29:56 Kerem Bürsin: Does SQL page/row compression reduce the need for db normalization?
  • 30:32 Bernard Tai: What was your favorite DBA class / subject as a student?
  • 33:11 pete: An SP with a 3 table join takes 30 seconds with the results into a
  • #temp, the second part has a 3 table join using the result set and 2 of the original tables, but it takes 2+ minutes. The only thing that is way out of kilter is the estimates of rows to be operated on. help pls
  • 34:52 Miles: Hi Brent, What metrics to measure and what threshold values to consider to determine how busy is my SQL Server? I want to check the load on the SQL Server. Thank you.
  • 36:48 PretendsToKnowSQL: We have a few maintenance plans we would like to “copy” to another instance. Are there any easy ways to do this or is it better to just take the time to rebuild them on the other instance?
  • 37:30 Jessica: I am currently crying in standard edition for indexed views. Have you ever seen issues wrapping an indexed view in a second view to add the noexpand hint on the first view? (create view View2 as Select * from ix_View1 with (NOEXPAND))
  • 38:08 Ozan: Hi Brent, at least two times i heard you saying that you get up at 3 AM. But so far i didnt hear you telling us when you go to bed!? I’m curious because 3 AM is damn early. Best regards from Berlin
  • 39:02 Miles: Hi Brent, We have 4TB vendor specific DB. We see a lot of open spids idle for over 8hrs. By killing long running head blockers, what kind of impact will it have on other AG replicas?
  • 40:02 Eduardo: Do you know what the tipping point is in SSMS where it will crash from showing too many query plans from a stored proc that has cursors?

Analyzing Prices of SQL Server Books, New & Used

Brent Ozar
SQL Server
11 Comments

While on a road trip, Richie stopped by a used bookstore (or would that be used book store?) and made an amusing observation about the relative value of different SQL Server books. I just had to go look at Amazon for some research.

I’m listing prices below, but keep in mind that when you click, the prices and inventory may have changed.

Pro SQL Server 2022 Administration by Peter A. Carter: new $54, used $50 – there’s not much of a discount here because few 2022 books have been bought so far, and the topic is still fresh.

Pro SQL Server 2019 Administration by Peter A. Carter: new $52, used $38 – the book is older, so it’s in more hands, and more people are ‘done’ with it and willing to sell theirs on. I’m not sure the demand is lower for a 2019 book given the high market adoption rates for 2019, but that might be the case. Some people might just always want the most recent version even if they’re not using it.

Pro SQL Server Administration by Peter A. Carter (released 2015): new $70, used $28 – why might an older book be more expensive? Well, there just aren’t many left that are brand new, so the new price is high. Amazon’s clingin’ on in the hopes someone’s going to collect old books, I guess.

My gut tells me that makes sense: new book prices go up as they age, and used book prices go down. Let’s try another series – Grant Fritchey’s Query Performance Tuning series.

If you think Grant peaked in 2014, dear reader, I would beg to disagree.

How about Itzik Ben-Gan’s excellent T-SQL Fundamentals series:

I’mma be real with you: I bet the vast majority of us could learn a lot from a 15-year-old T-SQL book by Itzik Ben-Gan that just cost us $1. In fact, I bet by the time you read it, the used ones will have sold out to other people who want an incredible bang for their buck, so I’d better include a screenshot for proof:

Truly old-school readers of this blog will doubtlessly ask, “Well, Brent, isn’t there a specific used book you’re not mentioning?” Okay, yes:

But, uh, I think you’re still better off with Itzik’s for $1. That is a steal.

[Video] Office Hours While the Pool Refills

Brent Ozar
Videos
6 Comments

I step away from the backyard long enough to take y’all’s questions from https://pollgab.com/room/brento.

Here’s what we covered in this episode:

  • 00:00 Start
  • 02:23 Stockburn: Hi Brent, we are using distributed always on AGs, with a node in AWS for DR. We are thinking of using this to quickly migrate the work load to the cloud by failing over to the DR node, building a second server in AWS. Have you done this, recommend it or run away screaming?
  • 04:38 Developer who cosplays as a DBA: Is parameter sniffing inevitable in a database that scales large enough? Or are there SQL Server rockstars out there who can write all their queries to be immune to parameter sniffing?
  • 06:35 dirty-dba: I am tuning a SUM query. Removing all non pk indexes from the table makes the query run faster than it does with just the specific indexes required for the query, I am subscribed to your training package, but can’t get my head around why is slower with indexes
  • 07:46 Vishnu: How frequently should we be updating 1. First responder kit, 2. sp_WhoisActive, 3. Ola Hallengren’s SQL Server Maintenance Solution?
  • 09:17 Boutaga: Hi Brent, as a production dba I use SSMS templates scripts folder as a repository for my scripts, this is synced through onedrive. What tool would you recommend as a script repository which would sit between that and a local gitlab ? Is there any ?
  • 10:24 Bleona: What should someone do if they witness scripts / source the IT manager brought from their previous company?
  • 11:41 Enca : Can a SQL query be expensive without being slow or is slow + expensive mutually inclusive?
  • 12:28 Dilip Kumar: Are there any gotcha’s with using SELECT TOP 0 C1, C2 to initialize temp staging tables?
  • 13:15 Fernando Soler: Several of our SQL Data files have file stats showing 99% reads vs 1% writes. Are there any optimizations to be on the lookout for in this scenario?
  • 14:11 Haluk Bilginer: What is the top Kerberos issue you run into with your clients running SQL Server? How do you like to troubleshoot?
  • 15:24 Janis: We are on SQL Server 2016 and plan to migrate to 2019, as for now I have a couple queries where memory grants are going out of roof (1,5 gb for one query). Where I can find more info or deep dive in memory grants as all YouTube videos and google doesn’t help? I know 2022 adaptive
  • 16:29 Kareena Kapoor: What are the recommended online training courses for running SQL Server in an Azure VM? What are the recommended online training courses for running SQL Server in an AWS VM?
  • 17:05 Can Yaman: What is your opinion of using Azure Backup to back up Azure SQL VM?
  • 18:53 Sean: Hi Brent! Did you have a chance to visit the volcano when you were in Iceland? I’m debating booking tickets to see the latest and greatest.
  • 20:38 Pythor: Where is a good place to learn about source control for SQL Server stored procedures and schema changes?
  • 21:22 Rajesh Khanna: What are the top issues you see for shops that choose to use SQL tables as queues?
  • 22:16 Satnam Singh: Brent, Can you please help if there is any T-SQL to find DTU Utilization for each session on a Azure PaaS Database.
  • 23:01 GiddyUp (409): Is there any forensic evidence you like to capture prior to killing a long running SQL agent job?
  • 24:15 Dafina: Can a given table simultaneously support both fast OLTP and OLAP queries or is this not recommended?
  • 25:30 DadJokerDetroit: If an SQL Developer recursively bangs his/her head, will they end up with a CTE injury?
  • 25:55 Dumitru: Can I enable Accelerated Database Recovery, Always On Basic Availability Group and Log Shipping on primary replica of SQL 2019 Standard Edition?
  • 26:25 KG: What’s the best way to migrate/upgrade an existing availability group to a new server and new sql server version? I’m primarily looking at log shipping versus leveraging the existing availability group.
  • 27:12 Eduardo: How are the advertisers for your twitch stream chosen? How are the advertisers for your youtube stream chosen?
  • 27:56 Sql100: In one of your q&a sessions, you had mentioned that creating index as well on the foreign column would also have efficiency of retrieving the data – with the include having multiple columns or just that particular foreign key index? Thank you in advance, as always.
  • 28:39 Mike: Can’t download SqlServer Azure data to Excel with MFA login, only SqlServer Authentication. Is there a way using MFA login to download Azure data into Excel?

[Video] Office Hours: Stump Me With Your Data Questions

Brent Ozar
Videos
4 Comments

There are only a couple weeks left in this summer’s marathon of Office Hours sessions, which means there’s limited time left to ask your toughest database problems (not trivia questions) at https://pollgab.com/room/brento.

Here’s what we covered in this episode:

  • 00:00 Start
  • 03:22 tanchenglai: Hi Brent, I have implemented a system that records data in another factory. When changing shift, the data cannot be inserted into DB. I suspected due to too many machines causing the storage problem (hardware issue). How could convince the hardware guys to upgrade server storage?
  • 05:44 MooneyFlyer: Hi Brent! Do you have any recommendations on how to manage self-referencing tables? Say an Org Chart and queries like “Does this employee have this manager in its hierarchy?”. I usually solved this with recursive CTE, but I’m concerned about performance. Sorry for the WofT!
  • 06:28 Juan Falcon: We are heavily invested in SSRS 2019. Should we be looking at other options/products for when we jump to SQL Server 2022? Please advise.
  • 08:01 BeanZW: Using Merge statement for Update, Insert and Delete from source to target with the selected date time in target table. Is that the correct way using Merge?
  • 10:07 Benbo Baggins: DBA’s believe indexes are a function of data not schema, will change over time and vary between clients depending on their data usage. Dev’s believe that they are a function of schema and should be defined to suit the data and application. What are your thoughts?
  • 10:54 RoJo: We have 12 remote Devs that use our Dev SQL DB to develop on. Is there a way to safely allow this on the Internet without giving VPNs to every Dev? or other options for sane development remotely ?
  • 12:58 Era Istrefi: What criteria do you use to decide if a given foreign key should have a supporting index or not?
  • 13:52 StatisticallyUncertain: If there are any, what are come cases should automatic statistics creation should be disabled?
  • 16:25 Jiun: Hi Brent, I’ve a SQL procedure that my web site will call. The procedure executes a Java script. My problem is that it’s taking too long to execute (about 6 seconds), and over half of the run time is spent importing libraries and loading objects for the script to use.
  • 17:38 Azul: What are the pros / cons of all devs sharing a single SQL Server instance vs hosting their own instance which they keep in sync / up to date? Which do you recommend?
  • 19:32 Nora Istrefi: When should a computed column be persisted?
  • 22:55 johnkurt: Non-cluster index has pointers to the clustering index in a rowstore table or rowid to a heap-tabel. How is the connection between Non-cluster and cluster on a cluster-column store index ?
  • 23:56 Rani Mukerji: What is your opinion of PowerBI vs Tableau?
  • 25:50 T: Best DR solution for SQL Server 2019 Standard Edition
  • 26:30 Dua: What is your opinion of using TSQL merge statement to update DW dim target table via source staging table?
  • 26:47 Dhurata Dora: What is the best resource for learning about modeling DIM and FACT tables in SQL Server?
  • 28:02 DBA with MBA: Have you run into issues with Nutanix? Our 1TB DB (Mfg production OLTP, not super busy), after restore to more $ Nutanix (3-4 nodes), runs ~30% SLOWER on default settings due to Nutanix’s difference with HyperV/SAN. Vendor suggests like splitting MDF into many vDisks and more.TIA
  • 28:53 DKKimbo: We keep getting query store going read only after an internal filestream error message is logged. File stream isn’t enabled, have you seen filestream cleanup errors on systems that don’t have it enabled?
  • 29:55 lucky12345: Does the closed source nature of MSSQLServer make it less vulnerable to hacking attempts when compared to PostgreSQL?
  • 30:19 Neithan: This may be irrelevant but can you explain what’s the point of the isolation level? And why aren’t everything Serializable?

Who’s Hiring in the Microsoft Data Platform Community? August 2023 Edition

Brent Ozar
Who's Hiring
7 Comments

Is your company hiring for a database position as of August 2023? Do you wanna work with the kinds of people who read this blog? Let’s set up some rapid networking here.

If your company is hiring, leave a comment. The rules:

  • Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
  • An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
  • Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
  • Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
  • Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
  • Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.

[Video] Office Hours: Overheating Cadillac Edition

Brent Ozar
Videos
2 Comments

I took your questions from https://pollgab.com/room/brento before heading out to pick up my Cadillac from the mechanic.

Here’s what we covered:

  • 00:00 Start
  • 04:50 JudgeDredd: Hi Brent, have you ever been asked for opinion/expertise (as a database specialist) in a court trial? If yes, could you share some details?
  • 07:10 Aart Bluestoke: A maximum of 25% of ram for a query seems large; queries either seem to be tiny, or need more than 25% (but grab 25% and spill). Is it reasonable to set it to 10% or some smaller values, to avoid blocking by ram allocation if a couple reporting queries land at the same time.
  • 08:35 Satish: How does OLTP SQL DBA differ in required skills / duties from OLAP SQL DBA?
  • 10:21 StanTheMan: Hi,New DWH Server,Dell R750,SQL S2022,RAM 1.8TB,TEMPDB 4TB on local NVMe disks,stored procedure goes every 5minutes, error log every third day shows Incorrect checksum on TEMDB.mdf/ndf, CHECKDB IS OK, CHDSK IS OK, should be writte back on controller disabled?No one cant tell us
  • 12:25 Chris: Hey Brent, I have a large CCI which rarely requires updates to a tiny portion of it (~100 rows out of 100m). Even with NCI’s to support the seeks the key lookups for the update just plain suck and take tens of minutes. Are there quick wins or just need to bring the pain?
  • 13:51 DBA With Anxiety: Hi Brent. Do You have worksheet to decide, when we use sqlserver, or when we use another open source solutions ?
  • 15:12 Sigmund: How important (if at all) is emotional intelligence to the DBA role?
  • 16:10 Divya: What are your thoughts on surrogate vs natural key usage in SQL Server?
  • 16:54 Inspector Gadget: What are your thoughts on disabling / renaming SA account for security purposes?
  • 17:56 Dilip Kumar: How hard would it be to specify desired columns / column order in sp_blitzindex similar to how sp_whoisactive does it?
  • 19:00 Q-Ent: Hi Brent, Have you ever used Distributed Replay tool to assess performance workload on a new server?
  • 19:45 Ricardo: What is Data worth? Would you measure it by the amount of money it cost to get, look after, and store? (-say- 2000% once analysed and used), Or could you say its total-worth is 100% of the companies value (like company shares). As a full log of a company’s activity.
  • 21:06 Yitzchak: What are your thoughts on ChatGPT including training from commercial books without permission / compensation? Is this fair use?
  • 22:05 Cthulhu: Should you ever use sp_executesql directly in C# code? If so, when?
  • 22:40 Pablo: Should we configure alerts for SQL Error 833 Disk Delay? SQL Server 2019
  • 23:12 Rogge (like Stogie): Hello Brent: please help us understand why collation is different: SELECT COALESCE([i].[name], [i].[type_desc]) FROM sys.indexes [i]; Forcing either column to either collation “fixes the ‘glitch”‘. Why? (I’ve searched your site and other less reputable ones)
  • 24:29 Kris: Hi, Is there an easy way to dynamically merge XML data (data type XML) into fixed tables.
  • 25:34 pete: simple – I want to pipe the message tab, not the results tab to a file. Reality so far is that my research has this as almost, if not, impossible. point me in the right direction . .
  • 26:21 END TRY BEGIN CRY: Hi Brent. Suddenly one of my servers is taking 30min to backup a 4GB database. Wait types are ASYNC_IO, BACKUPIO, BACKUPBUFFER. Backups go directly to blob storage in Azure. All other servers are fine but this one is really slow. Also nothing else is currently using the server.
  • 27:51 Imran Abbas: What is your opinion of SQL Activity Monitor?
  • 28:00 Alexis Georgoulis: For Azure SQL VM, what are the pros / cons of managing disk encryption at the VM level vs through SQL TDE? Is it wise to run both?
  • 29:22 Oldie: Hi Brent, our developers use SSMS to create database backup files in case then need to restore but developers cannot delete the backup files from disk without getting access to the disk through an file share or if an administrator creates an job to delete the files, any advice ?.

[Video] Office Hours: Azure SQL DB & SQL Server Questions

Brent Ozar
Videos
4 Comments

Post your SQL Server and Azure SQL DB questions at https://pollgab.com/room/brento and upvote the ones you’d like to hear me discuss.

Here’s what we discussed in this episode:

  • 00:00 Start
  • 02:47 therealprodDBA: In the last webcast, I heard you cite that majority of your clients are on AWS. I got curious now that my shop is considering migrating onprem SQL to azure. Is there any known issue that the said clients were avoiding on azure SQL DB, managed instance and hyperscale?
  • 04:08 Festus: What naming convention do you prefer to use for unique constraint indexes vs non-unique indexes?
  • 05:21 EnglishmanDBAInIsrael: Following Garðarshólmur question on backups, is snapshot backup suitable for a highly active environment? I thought of having a AG secondary dedicated to snapshot backups, and then IO freeze does not affect users, but still gives the super fast disaster recovery.
  • 06:03 Seeker of Parallelism: Hi Brent, I added indexed views to tune my query. Unfortunately only my dev system uses the indexed views, the prod system gets the old query plan (already tested with option recompile). What could be the reason for different execution plans on both systems?
  • 07:21 END TRY BEGIN CRY: Hi Brent. I need to shrink a log after it grew to about 3 times the size of the DB @ 150GB (bad dev coding). The DB is in Simple recovery mode and had a backup done, and the log file says its 99% free space, but shrinkfile doesn’t make it any smaller. Can you think why? Cheers
  • 09:24 Doug E: Should we be concerned when “Reason for early termination of statement optimization” equals “Timeout” for a complex query in a stored proc?
  • 10:16 boutaga: Hi Brent ! Do all application using queries with parameter have parameter sniffing in SQL Server ? In my experience it is the case, but if not, how can I code an application in that manner ?
  • 12:42 Peter: How do you go about learning new technologies? You help a lot of us in your articles and teaching, but who teaches the teacher – and what methods work best for you?
  • 17:20 fajitapete: So I got a customer who handles the database server. The server is serving my application. 5 filegroups (3 for data, 2 for indexing) with 17-23 files per filegroup. Files are all different sizes, am I wrong in trying to show there is a performance issue with this many files?
  • 19:23 Todd C: Hi Brent: Do you ever do escape rooms with friends or family? They are popping up all over the place now. Even cruise ships have them (Royal Caribbean at least). I bet you would be pretty good, given your problem solving skills and analytical mind.
  • 20:51 Yuvati: What is your opinion of SQL Trace Flag 4199 (Query Optimizer fixes released in SQL Server Cumulative Updates and Service Packs) and when is it appropriate to use?
  • 23:15 Dave: Should indirect checkpoints be used with the master database?
  • 24:14 Eduardo: Is Azure ultra disk storage performance specs closer to onprem SAN specs or usb thumb flash drive?
  • 25:57 Zoom Towny: Do you have a quick/easy way to determine if a given query historically used the stats density vector vs histogram when estimating cardinality for a given index?
  • 27:44 SleepyDBA: Hi, I have trouble with a vendor application that includes a table with three columns and just one row. Application actively updates that row. So, table has large number of ghost rows, querying it results in 32k logical reads. is the existence of ghost rows connected to RCSI?
  • 28:43 Eduardo: For running SQL Server VM in the cloud, does one cloud vendor provide better storage price to performance ratios than the others?
  • 30:37 Ezra: Should SQL DBA’s know docker containers?

Finishing Up Your Free Azure Networking Training

Brent Ozar
Microsoft Azure
0

To celebrate this month’s launch of our new class, Fundamentals of Azure Networking for the Data Professional, we’re making one video a day completely free. Here’s what’s on tap this week:

Block out some time per weekday on your calendar now because each weekday, a different video will be live – but just for one day only! You gotta keep up if you wanna learn for free. (I’ll be making the videos public manually, so it won’t be at an exact time – just rest assured that if you log in at the same time every day, you’ll always have a fresh video to watch. If you log in at different times each day, well … you might not. Sorry about that.)

Like the class? Wanna catch up or re-watch at your own pace? Use coupon code CheckTheDNS for 50% off the $89 price. Start learning now!

[Video] Office Hours: SO MANY Good Questions

Brent Ozar
Videos
0

Seriously, how do y’all keep coming up with so many great questions? I went through your top-voted questions from https://pollgab.com/room/brento/ on a live stream on my Twitch channel, like I do on most Wednesdays & Thursdays, and really enjoyed these:

Here’s what we covered:

  • 00:00 Start
  • 04:14 Groove_timer: Hey Brent, Trying to convince my employer to not do In-Place upgrade across our SQL estate that is mostly 2012/14 instances. Rather they did side-by-side. But due to costs constraints they are not willing to do this. Are SQL Server in-place upgrades still ill advised?
  • 07:15 StuckInAQuagmire: Have you ever had a situation where a stored procedure took too long, so application reran it without stopping the original, and it took long, and it cascaded into zero resources available and the only way to stop it was to stop SQL Service?
  • 09:39 Bomi: Hello! I watch your office hours constantly. Love your teaching pragmatic database usage. But I’ve never used MS SQL server. Is there a Brent for SQlite or MariaDB or Postgres or something? Or should I start using MS SQL server just because it has the best learning material?
  • 11:32 Peter: I’ve JUST learned about Query Notifications. Normally I’d use event hooks in an app to raise notifications about data changes, but this would be useful for situations where the data can be changed outside of the app. What are the drawbacks / gotchas to using Query Notifications?
  • 14:00 .Net Dev: Hi Brent, it seems like azure sql DB doesn’t allow me to update the setting for cost threshold for parallelism. Do you know it’s not configurable? Do they automatically modify the setting depending on the needs my DB?
  • 16:50 Garðarshólmur: What is your opinion of the new snapshot backup support in SQL 2022? Is this the answer for multi tb database backup?
  • 18:11 Kevin: Morning Brent, This may seem trivial. I’m a little leery about moving from SSMS 18.12 to 19.1. Any gotchas there that you know of, please?
  • 19:29 Kris: Hi, In SQL Server 2019 is there a way of running dynamic SQL with EXEC where the SQL statement is over 8000 characters?
  • 20:18 Slonik: What are your favorite PostgreSQL blogs?
  • 21:55 Psycho (SPID) Killer: What is your opinion of the hybrid buffer pool / PMEM support in SQL Server?
  • 24:34 CJ: Since SQL Agent alerts aren’t available on Azure SQL Managed Instance, is there a list of equivalent items we can/should monitor thru the Azure Portal (Azure Monitor). Any resources you can provide would be great.
  • 25:40 Dom: What would be the most important “features” that you would see as “requirement” for a monitoring tool ? We are looking at something that monitor everything so it won’t be “excellent” with SQL but I wonder what is the “must” that it should be monitoring (OS excluded)
  • 27:32 Eduardo: Do you know of any good tools that parse SQL query plan XML to extract the params / param values for use in SSMS query testing?
  • 28:06 Konya: What is your opinion of Azure Synapse?
  • 30:57 Brandon: I’ve seen your article on “3 connection strings” for scaling transactional systems. What do you think are the best options now for providing read only replicas for the near-current and older connection strings?
  • 31:27 Aditya: When should you use async auto update stats vs sync auto update stats?
  • 34:40 Erum: What is your opinion of the Microsoft announcement for new support of .NET 5 C# Language Extension for SQL Server?

[Video] Office Hours: While Stack Overflow is Down

Brent Ozar
Videos
5 Comments

Stack Overflow was down, so y’all posted questions at https://pollgab.com/room/brento and I gave ’em my best shot.

Here’s what we covered:

  • 00:00 Start
  • 04:33 Amir: Does Az-900 worth the effort and money? What about other microsoft certifications?
  • 06:16 Mr. M: Hi Brent. Which feature in SQL Server you like the most?
  • 08:43 Q-Ent: Hi Brent,I took mastering classes bundle and nowI am on Mastering index part.While you do your magic on the queries, you never consider statistics.Do you think statistics update is an obsolete maintenance method?I am really excited and I totally recommend everyone to join a class
  • 09:28 Yvette: What’s the recommended max ceiling for a single AG group in terms of number of DBs and max size per DB?
  • 11:37 Ricardo: What should I do when I say to a new customer “no, you should not be partitioning that fact table. It’s not helping with the overnight load”. But they ignore me and continue to have problems (My default reaction is to silently fume).
  • 13:33 Netanel: What is your official jingle / song?
  • 14:11 Benji: Does table row / page compression affect SQL backup performance?
  • 15:28 Ralph N.: Do you know of any slick ChatGpt fact checking tools?
  • 16:26 Freyja: What is your opinion of using column store index on a temp table in a stored proc?
  • 17:25 Yitzhar: What is the best way to capture locks for a problematic stored procedure when you are asleep?
  • 19:05 Rufus: Does Data Warehouse vendor typically drive reporting software choice or vice versa?
  • 20:23 Karthik K: Hello Brent, My friend is trying to create new NCI on CCI(Clustered Columnstore) table having other NCIs, sometimes it generated sub-optimal plan for the index creation part taking too much time. Is there way to influence the optimizer like providing trace flags? Pls suggest
  • 21:49 Tonio: HI Brent, Is there a good way to estimate how much memory a SQL Server instance running OLTP workloads needs? Any easy calculation based on database sizes, biggest tables, etc.? I have customers who insist that 16 GB memory is good for their 500 GB DB and have performance issues.
  • 24:09 Replicant: Been years since I’ve used replication, avoided as much as possible, how is it in new versions of SQL. Would you use it?
  • 25:08 Denish Patel: Power BI freeze the SQL whenever it fetch the full data, is there any effective way to solve such large data grabbing operations on database which also have lots of INSERT operations.
  • 25:44 Don’t Bother Asking (DBA): Hi Brent, Have any clients approached you regarding the implementation of CICD with a database in the pipeline? I mean automating integration/deployment of database changes alike application changes (bugfixes/updates) using CICD tools. What is your catch with such requirement?
  • 27:25 Hany : Hi Brent, Trying to look for a Lifetime Access payment option for Level 2 Bundle (All your Fundamentals & Mastering classes plus SQL ConstantCare and the Consultant Toolkit), couldn`t find it, only One Year Access is available, is it possible to add it? Thanks, Hany
  • 28:16 Bocephus: Can you show us how you like to use dual monitors with SSMS?
  • 29:01 Tonio: Is there any feature of Oracle Database that you wish SQL Server had? Also Thanks for the previous question’s answer 🙂
  • 30:02 Piotr: What is your opinion on naming and nesting of CTEs?

SQL ConstantCare® Population Report: Summer 2023

Brent Ozar
SQL Server Population Report
15 Comments

The short story for this quarter: SQL Server 2022 adoption rates have stalled, even backtracked, and it doesn’t appear to be due to the cloud, either.

For the long story: ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates? Let’s find out in the summer 2023 version of our SQL ConstantCare® population report.

Out of 3,124 monitored servers (up from 3,002), here’s the version adoption rate:

The big 3 versions are all within 1% of the last quarter’s numbers:

  • SQL Server 2019: 38% (up 5% since last quarter)
  • SQL Server 2017: 19% (steady)
  • SQL Server 2016: 27% (down 1%)

On the other extreme:

  • SQL Server 2022: 1% – actually down, from 52 servers last time to 46 this time
  • Azure SQL DB: 2%
  • Azure SQL DB Managed Instances: <1%

Just 13% of the population are running unsupported major versions (2014 & prior), and that’s steady from the last report.

Here’s how adoption is trending over time, with most recent data at the right:

It looks like companies are standardizing on SQL Server 2019 for new installs rather than SQL Server 2022. In the past, we’ve seen development environments going live on new versions first, ahead of production pushes, but we’re not even seeing that anymore.

I’ve written about how SQL Server 2022 still isn’t ready yet, and how even the updates are breaking. This feels like the moment where we call it: companies are skipping SQL Server 2022. It’s a shame, and I know Microsoft employees have to be frustrated about this, and I know I’m not going to make any friends at Microsoft by saying the quiet part out loud, but here we are.

Free Azure Networking Training This Week

Brent Ozar
Microsoft Azure
2 Comments

To celebrate this month’s launch of our new class, Fundamentals of Azure Networking for the Data Professional, we’re making one video a day completely free. Here’s what’s on tap this week:

Block out some time per weekday on your calendar now because each weekday, a different video will be live – but just for one day only! You gotta keep up if you wanna learn for free. (I’ll be making the videos public manually, so it won’t be at an exact time – just rest assured that if you log in at the same time every day, you’ll always have a fresh video to watch. If you log in at different times each day, well … you might not. Sorry about that.)

Like the class? Wanna catch up or re-watch at your own pace? Use coupon code CheckTheDNS for 50% off the $89 price. Start learning now!

[Video] Office Hours: 30 Minutes of Rapid-Fire (And a Little Burnination)

Brent Ozar
Videos
1 Comment

Almost all of today’s questions from https://pollgab.com/room/brento were great! Well, except two.

Here’s what we covered:

  • 00:00 Start
  • 02:13 Andrei: What’s the appropriate response/punishment for developers who insist on storing json and xml in nvarchar(max) fields?
  • 03:43 JustGoogleIt: What are your thoughts on Jeff Moden’s “Black Arts” Index Maintenance — GUIDs v.s. Fragmentation? How would you configure index maintenance on databases with mostly GUIDs for the clustered indexes but some ints sprinkled in?
  • 04:31 Eli: Any thoughts on whether the availability of NVMe storage interfaces on Azure Ebsv5 and Ebdsv5 VMs will finally help close the gap on cloud IOPS and throughput?
  • 05:19 Praveen Kumar: Hello Brent, I want to change the datatype from ‘int’ to ‘bigint’ on a partitioned table with 2 billion+ rows. What is the best way to do this with with minimal downtime? I don’t want to create triggers to capture DML query changes as I’m afraid that it might impact performance.
  • 06:23 MrGPT: The SQL CPU reached 99 and I saw the ‘704 Workers Created 300 Idle’ log and I couldn’t find any wait type on SQL except Hadr_Lease_Timeout, but this is about cpu reaching 99. How are so many workers created? Only solution set Max Workers 1000 ? 2 servers with AlwaysON
  • 08:28 Venkat: Is the Azure SQL DB auto indexing similar in index suggestion quality to Clippy in canned SQL Server (i.e. disregards key order, joins, etc)?
  • 09:29 Fritz: Where do you shop for your cool meme shirts?
  • 10:15 MooneyFlyer: Sometimes I create schema-bound/indexed views to improve select performance. How can I see the detailed execution plan SQL uses for inserts/updates/deletes on the tables used by the view? SQL is only giving an “Index Update” operator in the execution plan.
  • 11:25 Benji: Do you ever do the exotic car racing in Vegas?
  • 12:41 NetworkGuy1262: We use SQL Standard with multiple databases ~128G on NVMe disk. Combined, 700G. Servers have 384G RAM. Can you suggest a test method or tool to measure the performance benefit upgrading to Enterprise rather than sticking with 128 buffer pool limit of Standard with fast NVMe?
  • 15:37 Sergi : Is it possible, using SQL, to raise the CPU usage % as privileged time? I understand that this metric is only used for system processes but I’m not sure.
  • 16:13 Jason from Michigan: I occasionally see an error of “Time-out occurred while waiting for buffer latch type 2 for page”. Rebuilding the index “fixes it” but I am curious what the mechanics are of what’s causing this error to occur.
  • 18:57 Dwayne: Which twitch gamers do you enjoy watching?
  • 20:20 Pytzamarama: Hi Brent! We have 8 hotel databases on one SQL Server Enterprise, on premise. The managment have opted for moving them to Azure SQL. Since hotel business is a 24/7 one, what is the quickest way (the least downtime) to migrate all 8 SQL Server databases to Azure?Thanx!
  • 21:40 Jesse: How would someone get into SQL Server consulting? What do you like the most and hate the most about being a consultant?
  • 24:08 Rufus: How do you track down data corruption when the corruption source lies in networking hardware?
  • 25:11 Iskenderun: Should we expect much of a server performance hit when we simultaneously enable SQL transport encryption and SQL encryption at rest (TDE)?
  • 26:03 Tony: How many of your customers run SQL Server on Kubernetes? Is this a skill worth learning?
  • 27:11 Tim: Any plans to purchase the Apple Vision Pro?
  • 28:51 Vishnu: What are the tell tale signs that the network bandwidth for your SQL Server is not sufficient?
  • 30:01 Eduardo: Are there any risks to our SQL Server for running long periods of time without a reboot?
  • 30:48 StuckInTheCloudDBA: Hey, Brent how do you approach index creation for applications such as Informatica MDM where include columns are not supported? Will it be possible to have optimized enough performance only with indexes with key columns?
  • 32:27 Izmir: Does commercial SQL monitoring software negate the need for Query Store?

Free Azure Networking Training This Month!

Brent Ozar
Microsoft Azure
3 Comments

Raise your hand if you’ve ever blamed the network.

You’re responsible for the health, security, and uptime of your company’s data services in Azure. You’ve provisioned a few services, but every now and then, you run into problems making your services reachable and reliable from different users and app servers.

You want to understand:

  • Your company’s cloud network topology
  • How your services live in that topology
  • What a private link is, and why you probably need it
  • If your services are available publicly or privately (and how to tell the difference)
  • Where to look if someone can’t connect

You’re ready for our latest class, Fundamentals of Azure Networking for the Data Professional. It’s taught by Drew Furgiuele, former Senior Cloud Solution Architect at Microsoft, and now a Senior Solutions Architect at Databricks. You might recognize him from his popular Fundamentals of PowerShell class, too!

To celebrate the launch this month, use coupon code CheckTheDNS for 50% off the $89 price. If you can block out time on your calendar, one module of the class will be free each weekday:

Block out some time per weekday on your calendar now because on each weekday in July, a different video will be live – but just for one day only! You gotta keep up if you wanna learn for free. (I’ll be making the videos public manually, so it won’t be at an exact time – just rest assured that if you log in at the same time every day, you’ll always have a fresh video to watch. If you log in at different times each day, well … you might not. Sorry about that.)

It’s also part of my Recorded Class Season Pass: Fundamentals for $395. Folks who already have the RCSP or one of my big bundles can hop into their account and start learning immediately.

FOR XML PATH Changed The Way I Think About T-SQL #TSQL2sday

Brent Ozar
T-SQL
4 Comments

The first time I saw FOR XML PATH being used to generate a comma-delimited list, I think I stared at it, shook my head to clear the cobwebs, stared at it some more, and then closed the code editor thinking it was complete witchcraft.

And that same thing probably happened the next several times, too.

But eventually, I took a deep breath and read the code more closely to understand what it was doing. I don’t know whose example I originally found, but I’m going to point you to Dave Valentine’s post on how to do it because he’s got a clear example that even has a database diagram! Way to go, Dave.

The basic idea behind FOR XML is that you can take a regular query’s results, and export them to XML format.

Normally, XML looks kinda like this:

With FOR XML PATH, you can define how SQL Server formats the output. For example, if you wanted a list of just the names, and you wanted them in a comma-delimited format, you might want:

Nothing about that is even remotely close to valid XML, but the FOR XML PATH technique lets you abuse the intended usage of the feature and get something completely different out of it.

To learn how to do it, check out Dave’s post, which includes this example:

The a-ha moment for me was realizing that instead of building a result set, FOR XML PATH is building a result string, and it just so happens that in this case, the string doesn’t have line endings. You could build a multi-line result set by appending carriage returns and line feeds at the appropriate grouping points.

For more a-ha moments, check out Erik Darling’s call to action for today’s T-SQL Tuesday, and there will be fun comments in that post.

[Video] Office Hours: DBA & Developer Relationships

Brent Ozar
Videos
1 Comment

Y’all posted & upvoted questions at https://pollgab.com/room/brento, and we finished up the session talking about the relationships between developers and database administrators.

Here’s what we covered:

  • 00:00 Start
  • 02:35 Nortzi: Hi Brent. Is there a way to optimize sorting a result using a column from a different table? Using POC index pattern (partition,order,covering) works great to eliminate sort operations on columns from the same table but what if I have a column from another table in the order by?
  • 04:25 Gustav: Who is winning the serverless cloud SQL race? In addition to price, what are the big differentiators?
  • 05:38 Pool_Party: You wrote on your blog that SHRINKDATABASE is not good because creates fragmentation and therefore the problem is not solved. But I’m in an Azure SQL Elastic Pool scenario and I need to claim more space otherwise databases will not fit. What to do?
  • 07:29 SD: Hi Brent, I have Non-Prod AGs that I want to upgrade from SQL 2016\2017 to SQL 2022 (And downgrade the Edition from Enterprise to Developer). Can I do this by adding SQL 2022 Developer Edition Nodes to the AG and doing a failover (is this supported \ allowed \ known issues)?
  • 08:56 Deepak: Do you think generative AI would be a good addition to Redgate SQL Prompt (i.e. help write queries)?
  • 11:13 Jahid Ajmeri: Hi Brent, I am DBA. I want to use transactional replication with updatable subscription. There are multiple services insert/update data every 5 second into database. I want to make this write operation on both pub & sub database without conflicts. What you suggest.
  • 14:01 Adam: Hi Brent, I’m finding on occasional that my msdb is going into Suspect mode around the same time in the evening. I have run DBCC and VM disk checks and nothing has been found. Have you ever come across this before and if so, how did you solve it?
  • 15:27 Dwayne: Which games do you enjoy on the PC? Which games do you enjoy on the PS5?
  • 16:56 TomInYorks: I’m exploring solutions to locking/blocking seen in an application (multi-tenant, DB per customer but customisable reporting on live data) – was looking at RCSI or Snapshot Isolation. Can you recommend a good place to start when analysing the suitability of either of those?
  • 18:31 Vishnu: Is Apple Worldwide Developers Conference or Microsoft Bing the more influential tech conference?
  • 18:57 Vineeth: What is your opinion of the new Json native data type in Azure SQL DB?
  • 20:06 Deepak: Automated weekly Qualys cyber scans attempts to penetrate the SQL Server and reports findings back to corporate. One of the test threads attempts to connect to the DAC. Do you see any risks in this?
  • 20:57 Edwardo: Is there such thing as a “memory deadlock”? I recently observed multiple parent queries snagging 25% memory apiece and then calling a linked server loopback connection to spawn some child queries under a different SPID, and the child queries were waiting on RESOURCE_SEMAPHORE.
  • 22:21 Dom: Hi Brent, You mentionned a couple of time about “san backup” for SQL large databases. I google it and didn’t find much information about how that “work”. I’m guessing it’s backing up the mdf and ldf (it then cannot take log backup right?) Does it mean DB needs to be in simple ?
  • 23:04 Halldora: For sp_BlitzCache @MinutesBack argument, is there good way to tell how many minutes back we can go before data is no longer present?
  • 23:58 Yitzhar: Do you have any good use cases for the SSMS option : “Discard results after execution”?
  • 24:41 toepoke: Hey, I use lookup tables in my db, typically with “Id, Code, …” columns, with a FK onto “Id” from the parent table. “Code” should be unique. “StatusCode” has a unique nonclustered index to avoid dupes. Is this more icky than dropping “Id” & using “Code” as the identity col?
  • 25:31 Dwayne: What MSExcel skills (if any) do you recommend having for the SQL DBA?
  • 27:14 dave: Some developers say that DBA stands for “Don’t Be Asking” – How do you feel about Dev-DBA relationships?

[Video] Office Hours: Lots of Good SQL Server Questions

Brent Ozar
Videos
5 Comments

At https://pollgab.com/room/brento, post the questions you’d like to get my opinion on, and I’ll take a break every now and then at the office and go through ’em.

Here’s what we covered in this episode:

  • 00:00 Start
  • 01:08
  • #ARRRRRGH: Hi Brent. Have you seen anybody put good visualisations on top of sp_Blitz? Im trying to build something in Power BI to look at our entire estate and looking for inspiration
  • 02:33 Pradeep: Hi Brent, I am a Dev DBA. I use of SET STATISTICS IO ON to identify heavy logical reads and tune indexes to minimize it. I got better results also. Production DBA says reducing physical reads only will add value and logical reads not. Your thoughts please.
  • 04:01 MancDBA: Hi Brent, how dedicated were/are you when it came to getting better knowledge about SQL Server. Did you ever study at the weekends/evenings or did you get enough knowledge throughout the working week? Cheers.
  • 06:03 The Net Demoter: When should you not enable Query Store for SQL Server?
  • 06:44 Venkat: What is your opinion of the new query_antipattern extended event in SQL 2022?
  • 10:57 Lakshmi: Do you have any good use cases for temporary SQL stored procs?
  • 12:23 Deepak: Do you think ChatGPT will result in the re-birth of Microsoft English Query for SQL Server?
  • 14:17 CKI: What tool do you recommend to check database for sql injection vulnerability?
  • 14:54 Bruce Un-Mighty: Does SSMS execution time in the lower right include or exclude the time required to render the results grid?
  • 16:20 Dipesh: How do you know when SSMS plan operator times are individual or cumulative?
  • 17:28 Maksim Bondarenko: How do You deal with “Transaction Log Larger than Data File” situation when a database in AlwaysON Cluster with Syncronous replica?
  • 18:42 Parminder: Is there value in learning PowerBI for the SQL DBA?
  • 20:24 Deepak: What Azure SQL DB features do you think we can expect to flow down next to traditional SQL Server?
  • 23:48 Dr. Zaius: Hi Brent, have you ever used Adam Machanic’s TOP (max bigint) technique as an alternative to the X-acto method of using temp tables to phase query executions? Any rules of thumb on which technique to use in a given situation?
  • 24:35 Yousef: What is your opinion of OPTIMIZE_FOR_SEQUENTIAL_KEY in SQL 2019?
  • 26:11 Eli: Hi Brent, do you have a rule of thumb or training that teaches about the smallest table size you’ll index? I’ve got some ~8k tables that are slowing down a query with millions of scans, but my hunch is that I should be focusing on reducing the number of scans instead of indexes.
  • 27:32 Mars: Hi Brent, would you reccomend using firstresponderkit for benchmarking?
  • 29:04 neil: Is there a way to MAXDOP someone else’s troublesome query (third party application dashboard hogs all the cpu cores). Like MAXDOP a specific sproc or something like that?
  • 31:09 GuaroSQL: Hey Brent! how are you? Is First Responder Kit working fine in SQL server 2022? we are thinking of migrating, but we are using a lot First Responder Kit and we want to keep using it.
  • 32:22 Eduardo: When running sp_blitzfirst, should it be run on the DAC connection?

[Video] How to Use ChatGPT to Write WordPress Blog Posts

Brent Ozar
Blogging, Videos
5 Comments

Today’s live stream was a little different: I demonstrated using the Aiomatic WordPress plugin, ChatGPT, and Azure Open AI to write blog posts. I showed the kinds of content it writes, the kinds of blog post content it doesn’t include, and taught you how to identify meaningless word salad blog posts written by people who don’t actually know what they’re doing.

I finish up by talking about the kinds of places where it actually is useful, and how I use it to wireframe out new work.

Brent Ozar

Hi! I’m Brent Ozar.

I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I’m based out of Las Vegas. He/him. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.

Want to advertise here and reach my savvy readers?