Blog

Office Hours in the Valley of Fire

Last Updated November 6, 2024

I took the Graffiti Gulf 356 out to the Valley of Fire State Park to exercise it, and took your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:39 mailbox: Hey Brent! I’m really enjoying your prerecorded training classes. Quick question,I’m running sp_BltizCache @SortOrder = ‘reads’ on our DW server and receiving a priority 1 warning of Plan Cache Instability. How meaningful is this warning on a DW server?
02:49 Briggers: Hi Brent. I have inherited an intense AG with 1 database, utilising an async read-only secondary. Is there a way to help reduce the size of a redo queue? For example reducing the number of checkpoints (Automatic) on the primary?
04:00 DoesTimeReallyExist: Hi Brent! I prefer to learn SQL Server in depth instead of learning no-SQL or PostgreSQL and TimescaleDB. What do you think?
04:31 Aksel: Duplicate Index How to explain a situation where there are indexes A (col1, col2) and B (col1, col2, col3, col4), and there is a query that checks the values of col1 and col2. If the query uses index A, no memory grant occurs. If the query uses index B, a memory grant occurs.
05:31 Cameo: What are your pros / cons of using local time vs UTC time for OS clock running SQL Server? Which do you see more of out in the field?
06:51 mailbox: My friend’s company pays only to license 4 cores enterprise(SA) on a reporting server. Best I can tell, we don’t need enterprise as of now. However, we might need it in the future. Is it a good idea to switch to Std edition on same budget, thus increasing core count?
08:00 adba: I am seeing high CPU on a SQL server VM,I have added more cpu and tuned the query, but we still see the issue. What parameters do I need to monitor to show it is a problem on the host side.
08:59 GenXerTiredOfTheBabyBoomers: You have probably got this question often, but getting up there in age/end-of-corporate-life, how does one become a consultant? I have decades of experience with MS SQL, Windows OS, and networks, yet have trouble crossing the great divide of corp to cnslt.
10:18 SportsFan101: What are your thoughts on the new JSON data type in Azure SQL? Will we see this feature in the new on-prem version, SQL Server 2025?
11:28 Bandhu: When doing row vs page compression for canned SQL, do most of your clients do all row compression or all page compression or some combination?
11:48 mailbox: In your experience, when is it time to upgrade server hardware? Maybe I should ask, how often should we try to upgrade the server hardware of our SQL Servers? My friend says that they have VMs on hosts that are 8 years old.
12:53 Juan Pablo Gallardo: In pure ERP environments, with no user queries or tasks outside the ERP, is it safe to say that any deadlock is responsibility of the ERP vendor to solve?
14:06 Dopinder: What criteria do you use when evaluating standup desks? What is your favorite brand and model?
14:44 Vasilis Hadjiloucas: Should I exclude my antivirus software from scanning my FILESTREAM container in SQL Server? What are the potential risks or benefits of doing so?
15:17 CryingInTheCorner: Hi Brent, you still didn’t update most of your Mastering classes since Sql Server 2022 came out… :'(
16:41 Dumb dude: If you had a server that was having a lot of resource contention and you were only allowed to add CPUs or Memory, but not both, without cost being factored into it, which would you pick.
17:21 About the 356’s Graffiti Gulf paint job
18:40 Dopinder: Can a single DBA manage a new SQL AG or is a team of DBAs recommended for AG administration? What do you see in the field?
19:31 mailbox: What is it like to work as a DBA for a consulting firm? Is it just non-stop performance tuning fun? Or are their lots of nights and weekend work?
20:50 Eddy Grant: When upgrading Azure SQL VM from 2019 to 2022, is it ok to do in place migration since we have snapshot backups or should we logship to new VM hardware?

Office Hours at the PASS Summit in Seattle

Last Updated November 6, 2024

Brent Ozar

SQL Server

I’m in Seattle this week for the PASS Data Community Summit. Before the pre-conference sessions started, I sat down to take your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
02:29 MyTeaGotCold: How are you finding DbBeaver for Microsoft SQL Server? Like you, I find ADS buggy.
03:22 DBA_Mufassa: We have a SQL 2019 std VM that was already provisioned with 4 cores. We noticed antivirus is using CPU a lot. We want to add 4 more cores just for OS processes to use. VM total now 8 cores. Can CPU affinity in SQL be set to 4 cores so we stay compliant with Microsoft licensing?
04:33 SportsFan101: Do you have a list of DBCC commands that you still use? At a recent job interview, I was asked to name all the DBCC comamnds that I know or use. What do you think of this question?
06:34 VegasDBA: Hi Brent! I have 3 AGs. I’d like to put a copy of each on another SSRS instance. I inherited hundreds of SPs using linked servers for reporting and it’s been an issue with failovers and upgrades. When hosts become unavailable or change names, reports all fail. Recommendations?
07:53 Vishnu: Is AWS storage for SQL Server glacial slow like Azure storage for SQL Server?
08:52 Banhdu: What is your opinion of Elastic Query in Azure SQL DB for cross server searching? Is it as bad as linked server queries?
09:09 Jökull: SQL best practices assessment for Azure SQL VM, shows fragmentation warnings like so Medium – Remove fragmentation of dbo.Acme.IDX_Foo_DefID index. Current fragmentation level is 25.93% What is your opinion of these best practice suggestions? Is medium too high?
09:51 Rockwell: What is your opinion of Azure PostgreSQL? Does it compare favorably with Amazon’s offering?
10:45 Bandhu: What are your pros / cons of chat gpt vs copilot? Which commercial version has more bang for the buck?
11:32 Philippe Addelia: Hello Brent, I am wondering what the best platform would be to automate tests, such as unit testing, regression testing, post deployment testing and functionality testing. Would tSQLt be a good choice?
13:04 Oli the dba: Is AWS as frustrating as Azure ? It constantly feels like one step forward two steps back ! I find this non stop relating to performance and architectural designs. Need more perf? Sure turn the dial. Btw now your HA is gone. Sigh.
14:17 Håkan: Do you have, or do you know of a course that could work as an in-house certification for basic querying skills for DW purposes? Looking for something like the old Exam 70-761 (Querying Data with Transact-SQL), but could be a bit simpler.
14:56 ChompingBits: This is an entirely academic question but I was curious if the order of the ON clause in an INNER JOIN matters. I.E. If FromTable.Column = JoinedTable.Column performs better than JoinedTable.Column = FromTable.Column. I wouldn’t think it makes any difference, but was curious.
16:13 Nom: Have you seen an Azure SQL Database/Managed instance that was about to exceed the maximum storage space allowed by Azure? How would you go about resolving issues where the cloud storage limits are going to be exceeded sooner or later?
17:52 Crazy Harry: What’s your recommended way to periodically export data out of Red Shift into Azure SQL VM?
18:12 J.T.: I’ve been a SQL DBA for 9 years now and have not used A.I. tools at all professionally or personally. In a previous episode, you’ve mentioned that the money is well spent. What is one example of how you use the tools and/or do you have any suggestions on where to begin?
20:00 Ricardo: I need to improve a function that is already fast as executions/minute = 7350. What is the best approach to go from a blink to half-a-blink please?
20:56 SQLFumbler: A developer asked if I can help extend the message column in error logs. Deadlock entries only show 300 characters, truncating details needed to identify the SP causing the locks. E.g., Backup logs show over 400 characters. Is it possible?
22:05 Renzi: Do you have any helpful scripts / tools to find permissions granted in SQL for AD users that no longer exist?
22:40 Ricardo: Hi Brent. I was going to ask about my nervousness around changing hundreds of untrusted foreign keys and constraints (as detailed my sp_blitz). But as I was typing “Brent-in-my-head” piped up with “test in a test system” and “discus with the team first”. Thanks again Brent 🙂
23:36 SteveE: Hi Brent, What are the reasons that would cause long plan compilation times on a particular query
24:14 Q-Ent: Hi Brent. I was wondering how you create maintain and monitor all the links of type brentozar.com\go\***
24:34 Garry B: Are you going to PASS Summit?

Who’s Hiring in the Microsoft Data Platform Community? November 2024 Edition

Last Updated October 28, 2024

Brent Ozar

Who’s Hiring

Is your company hiring for a database position as of November 2024? 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.

Watch Me Stream at the PASS Summit for Free!

Last Updated October 1, 2024

Brent Ozar

#SQLPass

This week at the PASS Data Community Summit in Seattle, 15 sessions are getting streamed online for free! I’m proud to be one of ’em – my session’s on Friday. Here’s the lineup, all times Pacific:

Wednesday:

8:15 AM – 9:30 AM – MICROSOFT KEYNOTE: Fuel AI innovation with Azure Databases
9:45 AM – 10:45 AM – GENERAL SESSION: What’s new in SQL Server and Azure SQL | Data Exposed Live
11:15 AM – 12:15 PM – GENERAL SESSION: Understanding SQL Server Backup and Restore
2:00 PM – 3:15 PM – GENERAL SESSION: Inside the SQL Server Transaction Log
3:30 PM – 4:45 PM – GENERAL SESSION: Fabric Architecture: Data Warehouse or Lakehouse, Which is the Right Choice?
5:00 PM – 5:45 PM – Query Store: The Simplest Way to Troubleshooting Performance

Thursday:

8:15 AM – 9:30 AM – REDGATE KEYNOTE: Simplifying Complexity: Making Database DevOps Work in the Real World
9:45 AM – 11:00 AM – GENERAL SESSION: AI Impacting Ethics: Harnessing Toolkits for AI and Data Science
11:15 AM – 12:30 PM – GENERAL SESSION: Leveraging Copilot in Fabric: Maximizing the Power of Your Data Enterprise
2:00 PM – 3:15 PM – GENERAL SESSION: A Practical Deep Dive into I/O for T-SQL Performance Tuners
3:45 PM – 5:00 PM – GENERAL SESSION: Everything You Know About Isolation Levels Is Wrong

Friday:

8:30 AM – 9:45 AM – COMMUNITY KEYNOTE: Generating Innovation: Change Is Not Artificial, It Is Intelligent
10:30 AM – 11:45 AM – GENERAL SESSION: Watch Brent Tune a Query in SQL Server 2022
1:00 PM – 2:15 PM – GENERAL SESSION: One Year in Fabric: Lessons Learned from Implementing Real-World Projects
2:30 PM – 3:45 PM – GENERAL SESSION: Perf Tuning in Prod: Plan Forcing, Plan Guides, and Plan Correction

To join in, just go to the PASS Data Community Summit home page, put in your email, and watch the videos. See you there!

[Video] Office Hours: Let’s Have a Drink

Last Updated October 28, 2024

Brent Ozar

Videos

I was making myself a drink to film Office Hours and I realized I should just film it in my home bar. I mixed myself a gin & tonic and took your top-voted questions from https://pollgab.com/room/brento:

Here’s what we discussed:

00:00 Start
00:50 MyTeaGotCold: Can you name anyone who radically disagrees with you? After nearly 50 questions, I trust you too much and want to hear from the dark side.
02:02 DataGuy: What are your thoughts on the Azure offerings of PostgreSQL vs AWS? Obviously you prefer AWS, but is Microsoft really that far behind? I ask because we are locked into Azure and are looking for opportunities to save on licensing for very specific apps.
03:13 DoesTimeReallyExist: Hi Brent! Do you have experience working with TimescaleDB? If so, how would you rate its performance?
03:21 Yusuf: Do you see foresee any issues granting the SHOW PLAN permission to a dev user so they can tune their queries for production SQL VM?
04:11 Dopinder: Will DBAs and software developers be replaced by AI in the next 5 years? How should they upskill to avoid obsolescence?
06:06 SportsFan101: Hey Brent, You have previously mentioned that the issue with open source is that when something breaks there is no one to call (no official support). I’ve never worked with open source. What do IT teams do when they encounter an issue with Open Source RDMS?
07:27 Mola Ram: How should we handle sending of emails from SQL Server so that recipients know why they are receiving an email and can opt out?
08:10 Mr. SqlSeeks: Perf issue caused by a new plan being generated. No idea why a different plan was built,. Days of research. Restructured the code. Now performs in all scenarios. Is it ever OK to stop looking for the root cause because you were able to fix it and spent days on research already?
09:43 Buttercup: Azure marketplace image installed SQL Server with master and msdb on C drive. Do you have recommended way to relocate or do we just need to suck it up?
10:17 nick: What should you be aware of when creating a database with enterprise level amount of tables
11:01 Rob DBA: Hi Brent! Working as more of an “Application DBA” wondering any points/methods you apply to determine a query is too far gone? Eg, slow with 88 joins to some very large tables where its doing heavy logic in the db that cant change.How to word rewrite to the business / devs?
13:49 Midlife DBA: Hi Brent, Have you had any experience with customers that have backup/recovery solutions like Rubrik or Cohesity? Any pros/cons you would be willing to share?
15:09 mailbox: Is SQL Server a good place to build a data lake? Any recommendations on DBMS that are designed for data lakes and offer good product support?
17:11 Vishnu: How good is your Internet when on the ship? Good enough to do day to day work?
18:04 Garthe Knight: What are your favorite 80’s TV shows?
18:41 Thomas Dolby: What’s your opinion of the azure feedback site used for SQL Server enhancements? Microsoft seems to advocate it on linked-in.

Launch Day Bonus: Free Stuff on Friday

Last Updated October 28, 2024

Brent Ozar

Company News

Our annual Black Friday sale launches this Friday and runs all month.

For those of you who are early birds, the folks who move quickly when the sales open on Friday, I’ve got a special bonus for you.

The first 100 buyers can use coupon code EarlyBird11 to save an additional 11% off the already-discounted Black Friday prices. When you go to put the coupon code in during checkout, it’ll show you how many are left:

Don’t use the coupon today! You wouldn’t wanna pay full price, not when the Black Friday deals are only a couple days away. See you on Friday!

Who Actually Buys BrentOzar.com Training?

Last Updated November 24, 2024

Brent Ozar

Company News

So you’re thinking about picking up some of my training & online services, and you’re wondering – do people actually buy this stuff?

Here are just a small sampling of some of the companies who’ve bought recently from me – forgive the sloppy logo arrangement:

If your manager isn’t springing for our training, maybe you should consider working for one of these companies instead. (Did I say that out loud? I might have said that out loud.)

[Video] Office Hours: Salem Witchcraft Edition

Last Updated October 27, 2024

Brent Ozar

Videos

It’s spooky time again! I visited Salem, Massachusetts again this year, this time for a friend’s wedding. I ended up in the same spot next to the Charter Street Cemetery that I filmed at last year! I went through your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

00:00 Start
01:28 Dumped_on_DBA: How would you recommend storing data like zip codes that have spaces (like in Canada/UK), with or without the spaces? The issue happens when you want to search for a customer by zip code and the search input may or may not have a space and thus not match what’s stored in the DB.
02:40 DoesTimeReallyExists: Hi Brent! Should I use Cassandra or SQL Server for storing 500 million records daily? Each record contains 10 columns, with data types being datetime and numeric.
03:52 Frozt: What can you say about GMSA. I am using NTLogin again since they rollout cyberark and require us to change password every month which needs restart because other jobs are failing if you don’t restart the service. Do you have any opinions or gatcha on GMSA is it better than NTLogi
04:10 Otter 841: What was your opinion of Otter tune for PostgreSQL? Why do you think it failed while Constant Care succeeded?
05:43 KondzioSSJ4: Why MSSQL sometimes wants a index that have only changed order of fields in INCLUDE index creation? The index columns are the same. I have index like (X) INCLUDE (Y,Z) and query processor want to add (X) INCLUDE (Z, Y). What is the difference?
06:25 Tarzan Boy: What are your thoughts on the reserved instance trap for Azure SQL VM where your VM soon becomes antiquated but you are stuck with it due to reserved instance?
07:52 Az: Archiving needed for 10TB table with frequent updates, image column & 30M+ records. Seeking expert advice on optimized strategies for performance, storage efficiency & data consistency. Help with tools, technologies & step-by-step approach.”
10:20 Zoltan: What is your opinion of Idera SQL defrag manager for managing index fragmentation? Worth the money?
10:51 chandwich: Hey Brent! In the last Office Hours, you mentioned one skill many DBAs lack is their ability to communicate effectively. Have you considered offering a class on this specific topic? If not, are there any courses you recommend?
11:58 FutureBlogger: Hi Brent, I’m looking at starting a blog on databases and SQL Server to document my day to day learning. Do you have any tips or advice and what pitfalls should I try and avoid?
12:50 Hal: Do you foresee a day in the near future where certain ChatGPT requests for technical support will require a credit card?

[Video] Office Hours: Open Q&A About Databases

Last Updated October 28, 2024

Brent Ozar

Videos

I’m back in the home office to take your database questions from https://pollgab.com/room/brento.

Here’s what we covered in this episode:

00:00 Start
03:15 Mattia Nocerino: I’ve always ignored database t-sql snapshots. What are good use cases, if any?
05:12 All.. Night.. Log..: Any recommendations for what to do when we hit high Tlog VLF count on our SQL AG cluster?
06:06 ProochingMan: Do you think learning R and/or Python can be useful for DBAs who function as production DBAs and who have to occasionally help with data validation for new systems?
06:54 TiredDBA: How do you feel about Multi-Factor Authentication connecting to SQL Server databases? Does this become a nuisance if a user wants to connect to multiple servers to run a query against?
07:57 InTheNameOfGod: Can you explain how to use SET XACT_ABORT ON; and should we use it with BEGIN TRY and also BEGIN TRANSACTION; ? Am I the only one confused here?
09:17 Help Help Me BrentO: We have an issue where plan age in the plan cache is less than 30 seconds. When this exists, performance is poor. When the cache cleared, the plan cache age continually grows and performance is good. This lasts for 10 min before the issue starts again. What should be looked at?
10:45 Pseudo Echo: Now that ChatGPT can answer many coding questions, will this impact the quality / difficulty of the fewer questions that actually make it to Stack overflow and DBA stack exchange?
11:44 Rebel Girl: What are your thoughts on putting the SQL system DBs on their own dedicated non C drive? Good, bad, overkill?
12:45 Mattia Nocerino: I’ve inherited a server where previous full backups are restored once a day. BI asked me to improve restore times, so I’ve left dbs in standby and restore diff + logs (basic tlog-shipping I guess). I’m noticing high variance in restore time, from 90 to 30 min. Is this expected?
14:50 MyTeaGotCold: Have you recommended DBCC CLEANTABLE at any point in the last ten years? Even if you truly need it, why not rebuild the indexes instead?
15:12 Coffee Badger: What are your thoughts on Amazon’s 5 day in office work week policy?
16:11 Queen B: Does anything bad happen if you don’t prune your backup history for boxed SQL? What job normally does this?
16:59 boersp: With Veeam and all the other SQL backup agents, do you still recommend SQL backups via maintenance plans? Or are the 3rd party backups good?
17:58 Maximus: What are your recommended resources for learning stoicism?
19:02 Hal: Will we see AI mental health counseling in the near future?
20:01 StatsDontLie: My friend is getting an error on updating statistics on a read-only copy of the database on a user-defined table. Updating stats on the read/write database doesn’t fix the issue, neither does more coffee, help!
21:06 Tommy Tutone: What are the top issues you see when migrating SQL FCI VM from single subnet to multi subnet?
23:07 DBA-Team: Are you frequently using the Vision Pro? Or was it nice at first but not so useful now?

What’s In Your Development Database? The Answer: Production Data.

Last Updated October 14, 2024

Brent Ozar

Processes and Practices

Are your developers working with live production data, completely made-up synthetic data, or something in between? I posted a poll here on the blog and on a few of my social media feeds, and here were the results:

Note that the numbers can add up to more than 100% because folks could mark multiple choices if they had multiple teams with different approaches.

I also posted it on Reddit, which has its own separate (smaller) results, but I like having samples from more audiences:

No matter which way you slice it, about half are letting developers work with data straight outta production. We’re not masking personally identifiable data before the developers get access to it.

It was the same story about 5 years ago when I asked the same question, and back then, about 2/3 of the time, developers were using production data as-is:

Someone’s gonna ask, “So Brent, what do you recommend to fix this problem?” And I just don’t have easy answers. Over a decade ago, I explained why masking data is so hard, and the commenters chimed in about the difficulties they were having.

So when vendors claim to have solved this problem, what’s the catch?

One group of products focuses on building fake data from scratch, which is fantastic in the sense that you can pre-build testing data without waiting around for customers, and you never have to worry about getting the sanitization right. However, you have to define your tables & relationships in those tools, and you’re relying on those tools to generate the same kinds of data distribution and outlier users that you end up with in production. My clients who use these tools tend to struggle with reproducing parameter sniffing issues, for example, because the created data is too evenly distributed and doesn’t account for the crazy things users do in production.

Another set of products focuses on sanitizing prod data, which is cool because you can maintain the same data distribution, but less cool because you have to define every column that needs to be sanitized, keep those definitions up to date as you add/change tables, and then when you need to refresh development, wait for the data to be sanitized. These tools make it easier to troubleshoot parameter sniffing issues, for example.

Both kinds of tools require time & attention from data modelers. You have to lay out your entire data model and define what parts of it are private. Honestly, I love this, and everyone should do it.

When Microsoft announced Purview, I thought, “This is another really good idea that very few people will ever use.” It’d be amazing if we had a central dictionary that defined every column in every table in every database. Row-level security tools could plug into that to determine who’s allowed to see PII. Data cleansing tools could plug into that whenever they needed to sanitize production data. It’d be an amazing single source of truth for all kinds of utilities.

However, back here in reality, we can’t even agree on what utilities to use, let alone the central dictionary for them all to rely on.

Back here in the reality of 2024, we’re still developing with raw production data. <sigh> We’ve got so much work to do.

[Video] SQL Server Always On Availability Groups 101

Last Updated August 21, 2024

Brent Ozar

Always On Availability Groups, Videos

I got a few closely related Availability Groups questions at https://pollgab.com/room/brento and decided to do a half-hour introduction to AGs:

Here’s what we covered:

00:00 Start
00:24 Paul Hunter: My company decided to move to AZURE. The person in charge of moving the SQL Server decided to put up an AG (I voiced my concerns over management of an AG). Now I am going to be in charge of managing an AG. Do you have any advice on how to quickly learn to manage an AG?
30:14 Bandhu: What’s the max number of DB’s you would not exceed for a single AG?
33:59 Dopinder: What is the top issue you see with clients running SQL Server AG?
35:29 Bandhu: Is there a good way to determine which databases a given app connects to (for the purpose of designing AG’s)?

Updated First Responder Kit and Consultant Toolkit for October 2024

Last Updated October 19, 2024

Brent Ozar

First Responder Kit Updates

The wording of all of sp_BlitzIndex’s warnings is now much more clear and inclusive thanks to hard work by W. Eli Hayes and VladDBA.

When Kendra Little first wrote sp_BlitzIndex about a decade ago to help you analyze your index designs, we brainstormed about how to surface warnings. Index design is not black and white: for example, for some tables and workloads, 10 nonclustered indexes isn’t a big deal, but for other tables and workloads, it’s way too many and causes blocking issues. So we needed a way to talk about warnings in a fun way that indicated some professional analysis was required, and that the situation may or may not be a big deal. We settled on psychological-sounding phrases like “Index Hoarder”, “Aggressive Indexes”, “Abnormal Psychology”, etc.

The psychological terms worked, but we had to explain ’em every time. They were more of a barrier to understanding than an enhancement. So now, your sp_BlitzIndex advice looks more like this:

Simpler, clearer, and more inclusive. Although now I gotta go re-record all my index training, hahaha! I’ll work on that after the Black Friday sales finish.

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

To get the new version:

Download the updated FirstResponderKit.zip
Azure Data Studio users with the First Responder Kit extension:
ctrl/command+shift+p, First Responder Kit: Import.
PowerShell users: run Install-DbaFirstResponderKit from dbatools
Get The Consultant Toolkit to quickly export the First Responder Kit results into an easy-to-share spreadsheet

Consultant Toolkit Changes

Updated to this month’s First Responder Kit, but no changes to the spreadsheet template. In case you haven’t upgraded in a few months, you’re probably upgrading now because your old version has expired, so here’s what we did in May because the changes were pretty big:

Supports Microsoft Entra multi-factor authentication (MFA)
Automatically retries failed connections, allowing you to more easily gather data from Azure SQL DB Serverless databases that auto-paused
No longer requires create-table permissions, so it works better in environments where you can pull diagnostic data but not see database contents
Requires .NET Desktop Runtime 7 or higher on the machine where you run the Consultant Toolkit, typically your jump box or laptop (not the SQL Server itself)

sp_Blitz Changes

Enhancement: added checks for unusual Query Store configurations and missing trace flags. (#3553, thanks Reece Goding.)
Fix: when it was run without SA permission, it would throw a false error about the number of TempDB files not matching. (#3578, thanks Tisit.)
Fix: when it was run with SA permission, the “No Failsafe Operator Configured” wasn’t being reported. (#3577, thanks Tisit.)
Fix: added some wait types to the ignorable wait type list. (#3562, thanks Reece Goding.)
Fix: when it was run with SA permission, the invalid logins check wasn’t being reported. (#3551, thanks PowerDBAKlaas.)

sp_BlitzIndex Changes

Enhancement: change index warnings from psychological terms, to more inclusive ones. (#3571, thanks W. Eli Hayes and VladDBA.)
Enhancement: faster processing of missing indexes. (#3556, thanks Matt Maddocks and Dean MacFadyen for reporting.)

sp_BlitzLock Changes

Fix: @Debug = 1 would throw an error if your plan cache was empty. (#3567, thanks VladDBA.)
Fix: the @OutputTableName parameter would sometimes throw errors depending on your database context. (#3566, thanks BilliousBT.)

If you wanna watch me troubleshooting the sp_BlitzLock errors, this is a fun one-hour live stream of me working on that last bug:

sp_DatabaseRestore Changes

Enhancement: fix backup file selection for non-split full backups used with the @StopAt command. (#3560, thanks Rob Hague.)

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: Back at Home (Briefly)

Last Updated October 1, 2024

Brent Ozar

Videos

I took a break from unpacking my SQL Saturday San Diego bags and re-packing for a Boston wedding long enough to take your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

01:55 Deadlockinator: Hi Brent, Why does sp_BlitzWho return NULL in the query_text for some SPID, even when I’m running it as sysadmin? It also seems like it’s not capturing all the sessions, but SQL Profiler gets all the statements, especially the ones running with sp_executesql. Thanks!
03:46 Bruce: How prevalent is SQL Server on Linux in production environments?
04:32 Bandu: How many companies have you seen 1. Attempt a transition from MSSQL to PostgreSQL. 2. Complete a transition from MSSQL to PostgreSQL?
06:37 MyTeaGotCold: Is there a shorter way to say “Microsoft SQL Server on Amazon RDS”? None of those words seem optional! There are many SQL servers on RDS that aren’t Microsoft’s and many clouds that aren’t Amazon’s!
07:03 Ray: When Installing Cumulative Updates for SQL Server all the previous updates for the Service Pack get installed. If there are 10 CUs and CU 5 has a bug, will that bug be an issue when I install CU10?
07:52 Sean: Heyo! Inspired by your consulting video. How would you get Ops/Dev buy in on performance or optimization requests? Our infrastructure DBA team has raised tickets, standups, pushed at exec prioritization meetings, handed them all-in-one fixit scripts to test, with no luck.
09:33 Dru: Do you ever watch any of the free Carnegie Mellon database college courses on YouTube? What is your favorite?
11:04 Erzsebet: Does SQL CLR compare favorably with PostgreSQL extensions? What is your favorite PostgreSQL extension?

How Many Indexes Is Too Many?

Last Updated October 14, 2024

Brent Ozar

Indexing

Let’s start with the Stack Overflow database (any size will work), drop all the indexes on the Users table, and run a delete:

SET STATISTICS IO ON;
GO
BEGIN TRAN
DELETE dbo.Users WHERE DisplayName = N'Brent Ozar';

SET STATISTICS IO ON;

BEGIN TRAN

DELETE dbo.Users WHERE DisplayName = N'Brent Ozar';

I’m using SET STATISTICS IO ON like we talk about in How to Think Like the Engine to illustrate how much data we read, and I’m doing it in a transaction so I can repeatedly roll it back, showing the effects each time. Here’s the actual execution plan:

We read that from right to left. The first thing SQL Server has to do is scan the entire Users table to find the row(s) with DisplayName = ‘Brent Ozar’ because we don’t have an index on DisplayName. After it’s found them, then it’ll delete them from the clustered index.

On the 2018-06 copy of Stack Overflow that I’m using, SQL Server has to read 143,670 8KB pages to find the rows it’s looking for:

We need an index on DisplayName.

We want our delete to run faster, and we wanna quickly find the rows where DisplayName = ‘Brent Ozar’. To do that, let’s roll back our delete and create an index:

ROLLBACK
CREATE INDEX DisplayName ON dbo.Users(DisplayName);

1 2	ROLLBACK CREATE INDEX DisplayName ON dbo.Users(DisplayName);

Then try our delete again:

BEGIN TRAN
DELETE dbo.Users WHERE DisplayName = N'Brent Ozar';

1 2	BEGIN TRAN DELETE dbo.Users WHERE DisplayName = N'Brent Ozar';

Now, the actual execution plan is simpler:

From right to left, SQL Server can open the index on DisplayName, seek into just the row it’s looking for, then delete the row from the clustered index. Be aware that the plan is simplifying things a little: if you hover your mouse over the Clustered Index Delete operator, you’ll see that at the very bottom of the tooltip, SQL Server shows two objects, not one:

At the bottom, it says “Object: PK_Users_Id” (that’s the clustered index) and “DisplayName” (that’s our new nonclustered index.) Still, the work involved is way less now, as evidenced by query runtime and logical reads:

We cut logical reads from 143670 down to just 12 pages! I’m no data scientist, but I think the term we’re looking for here is “good.”

That index helped. We should add more!

When clients come to me for performance problems, it’s usually one of two extremes: either they’ve never discovered the magic of good nonclustered indexes, or they’ve gone wildly overboard with ’em, adding tons of ’em. Let’s throw on a few more indexes, and then run our delete again:

CREATE INDEX Location ON dbo.Users(Location);
CREATE INDEX LastAccessDate ON dbo.Users(LastAccessDate);
CREATE INDEX Reputation ON dbo.Users(Reputation);
CREATE INDEX WebsiteUrl ON dbo.Users(WebsiteUrl);

CREATE INDEX Location ON dbo.Users(Location);

CREATE INDEX LastAccessDate ON dbo.Users(LastAccessDate);

CREATE INDEX Reputation ON dbo.Users(Reputation);

CREATE INDEX WebsiteUrl ON dbo.Users(WebsiteUrl);

Now, our delete’s actual execution plan looks the same, but…

The graphical plan hides a secret, and to see that secret, we’ll need to hover our mouse over the “Clustered Index Delete” operator, which is a vicious lie:

See that Object at the bottom? The so-called “Clustered Index Delete” is actually deleting the row in every nonclustered index too. That also means we’re doing more logical reads to find the rows:

We’ve gone from 12 logical reads up to 24. Now, is 24 8KB reads a big deal? Not at all! You should feel totally comfortable adding a handful of indexes to most tables when your query workloads need those indexes.