Product has been added to your cart.

Blog

Updated First Responder Kit and Consultant Toolkit for August 2023

Brent Ozar
First Responder Kit Updates
2 Comments

Turns out y’all actually work over the summer – there are actually cool new features this month! I think I’m actually going to have to record updated sp_BlitzIndex, sp_BlitzLock, and sp_BlitzQueryStore modules for my “How I Use the First Responder Kit” class because these features are pretty awesome.

How I Use the First Responder Kit
Wanna watch me use it? Take the class.

To get the new version:

Consultant Toolkit Changes

I updated it to this month’s First Responder Kit, but no changes to querymanifest.json or the spreadsheet. If you’ve customized those, no changes are necessary this month: just copy your spreadsheet and querymanifest.json into the new release’s folder.

sp_Blitz Changes

  • Improvement: better chance of running to completion when you’re not SA. sp_Blitz has always required sysadmin permissions in the past, and this is our first pass at it, so if you have limited permissions and you run into errors, feel free to file ’em as issues and we’ll see if we can work around it. (#3292, thanks Erik Darling.)
  • Fix: reduce possibility of missing replication in use. (#3316, thanks DavidSchanzer.)

sp_BlitzCache Changes

  • Fix: more accurate estimation of memory grant used overall across multiple executions of a query. (#3313, thanks mdpenguin.)
  • Fix: more accurate percentage of duplicate query plans. We’ve been battling this issue for yeeeears, and I still can’t reproduce the issue in the lab, so I don’t think this is the last chapter on this issue. (#3314, thanks WaldenL.)
  • Fix: skip warnings of ResourceDB queries. (#3315, thanks WaldenL.)
  • Fix: better compatibility with AG secondary replicas. (#3291, thanks mbambion.)

sp_BlitzIndex Changes

  • Enhancements in #3294: in the columnstore visualization section, each rowgroup’s state, trim reason, etc are now shown:

And in that result set, the size of the secondary dictionary (if required) for a column is now included in its size. Here’s the Users.DisplayName column before – note the sizes in the far right:

Now, with the secondary dictionary size included, the DisplayName column’s true size shows up as being dramatically larger because the strings are relatively unique and don’t compress well:

sp_BlitzLock Changes

  • Improvement: if the queries involved in deadlocks are still in the plan cache, sp_BlitzLock now shows an additional result set with their plans. (#3293 and #3311, thanks Erik Darling.)
  • Improvement: when targeting one session, make sure it exists first. (#3306, thanks Erik Darling.)
  • Improvement: faster XML queries. (#3305, thanks Erik Darling.)
  • Fix: time searches are now UTC compatible. (#3307, thanks Erik Darling and Mike Hodgson.)

sp_BlitzQueryStore Changes

  • Improvement: Parameter Sensitive Plan Optimization (PSPO) compatibility! If you search for a single @StoredProcName, we now show all of the plan variants for it. (#3312, thanks sqljared.)

sp_DatabaseRestore Changes

  • Improvement: new @KeepCdc parameter restores that attribute too. (#3289, thanks SQLSlinger.)
  • Fix: compatibility with SQL Server 2022’s new undocumented columns in RESTORE HEADERONLY. (#3190, thanks Razvan Socol.)
  • Fix: incorrect error message in debugging output. (#3320, thanks Greg Dodds.)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

[Video] Office Hours in Telluride, Colorado

Brent Ozar
Videos
0

We headed up to the mountains to get away from the Vegas heat. Before the sun rose, I took your top-voted questions from https://pollgab.com/room/brento.

 

Here’s what we covered:

  • 00:00 Start
  • 00:36 Ozan: Hi Brent, how should Volume Snapshot Backups (VSS) be configured correctly that it will not freeze the database’s IO for a couple of seconds or minutes? Best regards from Berlin
  • 02:00 Miles: Hi Brent, As a DBA (5+ years), how do you track your work? Any tool or Excel? for ex: manager asks send me the list of items worked over last 10 months. things like that. Any piece of advice for visibility and credibility, dealing with a biased manager?
  • 03:01 Yullka Mujo: What are the top issues you see with using FileStream in SQL Server?
  • 03:24 Logar The Barbarian: What kind of problems have you dealt with and resolved for clients utilizing Dynamics GP for their ERP systems?
  • 04:43 Pavle Vuisic: Is it ok to update to latest SQL CU when you are four updates behind or should you install one CU then the next, etc?
  • 05:13 Rotnei: Do you have any suggestions for dealing with Azure Alert fatigue? Email rules / folders seems potentially heavy handed.
  • 05:55 Mahlagha Jaberi: What is your opinion of performing Azure SQL VM backups using crash-consistent snapshots?
  • 06:51 Miles: Hi Brent,Dev team is try to DELETE a log table based on co-related sub-query. It read 4TB data and It caused heavy blocking for more than an hour. Eventually had to kill the session. Even Rollback it took almost 30 mins. Db is part of AG. Any advice to make the DELETE’s faster?
  • 07:41 Hjordis: When should you put new tables in a file group other than PRIMARY?
  • 09:01 Ozan: Hi Brent, regarding MAX DOP I get in a 16 CPU VM environment with MAX DOP = 0 (16) better, faster results than with 8 which is the max value recommended by Microsoft. What problems could I face when I stay with 16 or 0? Thanks
  • 09:31 BobbyTables: You mention that while you haven’t done them recently, you’ve heard of potential issues when doing an In Place Upgrade for MS SQL OS. What issues have you seen reported?
  • 10:53 Shahab Hosseini: What is the top batch request per second (sustained) you have seen at your clients? How many DBAs did they have?
  • 11:38 Ingvar Vittfarne: Do you have a recommended naming convention for SQL Agent Job Schedule names?
  • 12:13 Nomzamo M: How do you know if the SQL engine has inlined your user function or not?

Office Hours: Before Heading Out on Vacation

Brent Ozar
Videos
1 Comment

I took a break from packing my bags long enough to answer your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Start
  • 02:42 T-man: Hi BO. A friend has multiple Distributed AGs (on separate business division servers) but needs to share ODS data with each of the DAG DBs. Any near-real time architecture ideas so they can stop batch ETL? Adding replication on top of AGs can work, but yuk. Thanks, you da best.
  • 04:11 BigData: Adding a server login during peak times is causing deadlocks with random user sprocs. It appears related to SCH-M on security cache. Server is 1.5tb and security cache is 13+ gb. Can’t recreate issue on small dev server. Are there any gotchas when clearing security cache?
  • 04:59 reluctantly_tolerant : We are using AWS FSxN (managed NetApp ONTAP) provisioned for 512MBs throughput with SQL 2019. The DB files are so fragmented that 2k iSCSI IOPS causes 50k-60k disk IOPS and AWS is throttling to 20k IOPS and ~130MBs throughput. How can I fix this and prevent recurrence?
  • 06:59 Eli: Hi Brent, observed a hash match operator performing 1000x slower when Table B is first in a join, but no spill! Join is many-to-many, with one duplicate value in Table A showing up 40M times, 200M times in Table B. From an internals perspective, what makes B-first so much slower?
  • 07:43 Tim Gitchel: Is there a way to capture any connection to the DAC? I am seeing an error from a nightly Tenable scan that there is another connection preventing access, but I can never seem to catch it. Could something else be causing this same error?
  • 09:01 pete: Why would a bit-wise operation in a table join cause a query to run long?
  • 10:37 Sonakshi Sinha: What did DBA Brent do for physical fitness?
  • 13:47 Golshifteh Farahani: I addition to Ozar training, what are the top things SQL DBA’s should be doing to future proof their careers?
  • 15:32 OliTheDBA: At what point do you think the cons outweigh the pros of automation ? For example, fancy multi-thousand line powershell scripts to snap restore db’s are great and save time, but the up keep and maintenance isn’t enjoyable and can often take hours a week. (Rant)
  • 16:50 Bilal Lashari: What are your top memories from your visits to the Microsoft Redmond campus?
  • 19:28 MooneyFlyer: Hey Brent, kind of a silly question, so roasted if needed. Why STATISTICS IO ON doesn’t give any information about written pages? Is there a way to get this information? I’m trying to give hard evidence to indexing naysayers on the real impact of adding an index to a table.
  • 21:08 Bjorn: Is it ok to use PAGLOCK hint in conjunction with TSQL – DELETE TOP (@SomeVar) FROM dbo.Foo to prevent lock escalation?
  • 21:57 Grimlock : What is the optimal nap time and duration? Have you always followed this?
  • 23:13 Alex M: Do you have any experience/thoughts on using non-MS SQL replication technology with SQL Server, such as Zerto for DR?
  • 25:08 Vangelis Protopappas: What are the top SQL full text issues you see with your clients?
  • 26:00 Demet Evgar: Does the DBA role for non-relational DB’s differ much from that of relational DB’s?
  • 27:57 Pareesh Patel: When doing one DB per customer for multi-tenant, what is the max number of DB’s you would not exceed when using SQL A.G.?
  • 29:18 Stu: At what point do you think a software developer should look for a new job, what are the tell tale signs?
  • 30:53 west: What are your thoughts about Microsoft Fabric and its dwh ability?

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.

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?